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
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

Roberto Roberto · Accepted Answer · 2016-04-22T20:29:49

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 Answers