Can't tell how report sorting parameter list (subreport routed through tablix)

Question

I have a subreport placed inside of a tablix. The report the tablix is in takes a parameter that is a list of order numbers (A4, B7, etc.). The dataset splits the parameter, separating the order #s and routing them to the subreport.

When I enter in the parameter on the main report and run, it spits out the report copies as desired, but in no discernable order.

E.G., If Order #s are (A400008, A400005, LSA3277, FLA3654), it returns them in this order: (LSA3277, A400005, FLA3654, A400008)

So it doesn't seem to be alphanumeric. I am super confused and need to force this report to return the subreport in the order entered. Let me know if you need more details.

@Roberto nope. It is designed to only take one parameter though, hence why I split it in the subreport and really the only reason I have a separate report. — Dom Vito, Apr 20 '16 at 17:05
It sounds to me like all you need to do is just Sort the Sub-Report. But it seems like you can also do this in one report with some Grouping — Roberto, Apr 20 '16 at 17:49
@Roberto the subreport is linked to a report that returns a tablix, yes. The tablix is part of a page with some static text boxes, etc. — Dom Vito, Apr 20 '16 at 18:56
In the Sub Report, Click on the tablix, then right click the top left square to go to Tablix Properties. Go to the Sorting Tab and add a Sort by your `OrderID` — Roberto, Apr 20 '16 at 18:59
@Roberto won't that sort it alphanumerically? as opposed to how things are entered? — Dom Vito, Apr 20 '16 at 19:24
Yes it will sort it alphanumerically. If you don't do this then by default it displays how it is sorted in your dataset. It might be easier to sort your Order#s when you pass them in as well. — Roberto, Apr 20 '16 at 20:14
@Roberto my problem is that I have no groupings in place, but it is not sorting the items either alphanumerically or the order I enter them in. — Dom Vito, Apr 20 '16 at 20:14
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/109823/discussion-between-dominic-g-and-roberto). — Dom Vito, Apr 21 '16 at 13:56

score 0 · Answer 1 · answered Apr 22 '16 at 20:29

I recommend you revisit your Datasets/Queries and both reports to look for more efficient ways. From my understanding you should be able to get it in one report without a sub-report. You should be able to do it with tablix row groups.

But if you want to keep as is. In your Sub-Report go to tablix properties. Go to the Sorting Tab and add a Sorting Option. Click on expressions.

If the Number of Orders passed is always the same (4). You can try something like this:

=IIF((Split(Parameters!OrderID.Value, ",")).GetValue(0) = Fields!OrderID.Value, 1,
IIF((Split(Parameters!OrderID.Value, ",")).GetValue(1) = Fields!OrderID.Value, 2,
IIF((Split(Parameters!OrderID.Value, ",")).GetValue(2) = Fields!OrderID.Value, 3,
IIF((Split(Parameters!OrderID.Value, ",")).GetValue(3) = Fields!OrderID.Value, 4,
0))))

If the Number of Orders passed can change (I assume it is) then you can modify the above to check that when SPLIT the string will have a value at nth position every time for X amount of times:

=IIF((Split(Parameters!OrderID.Value, ",")).GetValue(0) = Fields!OrderID.Value, 1,
    IIF(IIF((Split(Parameters!OrderID.Value, ",")).Length > 1, 
        SPLIT(IIF(
        SPLIT(Parameters!OrderID.Value, ",").Length > 1, 
        Parameters!OrderID.Value, "1,2")
, ",").GetValue(1), "0") = Fields!OrderID.Value, 2,
    IIF(IIF((Split(Parameters!OrderID.Value, ",")).Length > X, 
        SPLIT(IIF(
        SPLIT(Parameters!OrderID.Value, ",").Length > X, 
        Parameters!OrderID.Value, "1,2,...,X")
, ",").GetValue(X), "0") = Fields!OrderID.Value, X+1,
0)))

Again.. I DON'T recommend this, but this will return the sub report in the order they were passed in the Comma-Delimited string.

Can't tell how report sorting parameter list (subreport routed through tablix)

1 Answers1