Order a report builder report by the parameters entered

Question

So I have an SSRS report with one parameter and got a request to order the report by the exact order that the parameter/order numbers entered. Is there any way to sort the report this way?

For example, the parameters/order numbers entered in order: 5, 10, 1, 3, 2 I need the report to come out in that exact order

Table:

Order Number	Customer
5	A
10	B
1	C
3	D
2	E

Below is the query too, but I don't think this is anything I could do in sql server anyway.

SELECT c.customer_name AS [Customer]
, oh.order_no AS [Pick Ticket Number]
FROM orders oh
    INNER JOIN customer c ON oh.customer_id = c.customer_id
WHERE  oh.order_no IN (@orderno)
GROUP BY c.customer_name, oh.order_no

Answer 1

If you are using a drop down list of order numbers for the user to choose from, then I don't you can do this easily as there is no way to know which order they were clicked in.

If the users are just typing into a multivalue parameter and pressing enter between each entry then you can do it like this...

Add new new parameter to your report (this can be hidden) call it something like 'pOrderSort` and set the Default Value expression to be

=JOIN(Parameters!orderno.Value, ",")

This will create a string, something like "5,10,1,3,2".

Now change your dataset query to this..

declare @Seq table(orderNum int, SortBy int IDENTITY(1,1))

INSERT INTO @Seq (orderNum)
    SELECT value from string_split(@pOrderSort, ',') 


SELECT c.customer_name AS [Customer]
        , oh.order_no AS [Pick Ticket Number]
        , s.SortBy
    FROM orders oh
    INNER JOIN customer c ON oh.customer_id = c.customer_id
    INNER JOIN @Seq s ON oh.order_no = s.orderNum
GROUP BY c.customer_name, oh.order_no
ORDER BY s.SortBy

All we are doing here is splitting the passed in parameter into rows and assigning a sequence number in the SortBy column.

Now all we do is join to this table and order by the SortBy column. There is no need for the WHERE clause as we are joining only to the order number we need.

You can use the SortBy column in the report design to order the rows as required.