CODEWITHSUNDEEP
reporting-servicesreportbuilder
jrdev12345
jrdev12345

Reputation: 25

Order a report builder report by the parameters entered

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

Upvotes: 0

Views: 143

Answers (2)

jrdev12345
jrdev12345

Reputation: 25

I had to do it without string_split() so below is the code to get around that, other than that I followed Alan's answer with creating a hidden parameter named @orderlist that is joined with the @orderno parameter

DECLARE @str varchar(max)
SET @str = @orderlist

DECLARE @separator varchar(max)
SET @separator = ','

DECLARE @Splited TABLE(id int IDENTITY(1,1), item decimal(19,0))

SET @str = REPLACE(@str, @separator, '''),(''')
SET @str = 'SELECT * FROM (VALUES(''' + @str + ''')) AS V(A)' 

INSERT INTO @Splited
EXEC(@str)

SELECT c.customer_name AS [Customer]
, oh.order_no AS [Pick Ticket Number]

FROM oe_hdr oh
    INNER JOIN customer c ON oh.customer_id = c.customer_id
    INNER JOIN @Splited ot ON ot.item = oh.order_no

GROUP BY c.customer_name, op.pick_ticket_no, ot.id
ORDER BY ot.id ASC

Upvotes: 0

Alan Schofield
Alan Schofield

Reputation: 21703

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.

Upvotes: 1

Related Questions