SSRS 2008 - How keep the SAME order enter in the parameter to be reflected in the report

Question

I am working on a SSRS 2008 Report that when entering a list of ITEM numbers in a multiple data type parameter will return the Item Number, UPC, Description, and Item Status.

It works. But my problem is that it sorts my data in my report. Example. If I enter the following Item Numbers in the parameter: ZZZ DDD AAA HHH

My report shows the Data alphabetic as: AAA DDD HHH ZZZ

I want my data to be displayed in the order of the ITEM Number how I enter them.

I have One Data Set: SELECT ItemNo, UPC, Description, Status FROM Product WHERE ItemNo IN (@ITEM)

I have One Parameter: @ITEM Datatype: Text Allow multiple values

Please Help!

if the question was answered, you should mark it as such (or upvote if it provides help, but doesn't solve the problem). This will help others locate and determine the correct answers to problems — Trubs, Jun 13 '17 at 06:28

score 0 · Answer 1 · answered Jun 08 '17 at 05:07

You need to parse the string into a table and then set an order to the table you've created. Then you have something to sort by.

First - create a function that you can call in your sp. (the answer to this question gives you exactly what you need)

then, in your query you can use the function just like a table.

DECLARE @x VARCHAR(100)
SELECT @x = 'ZZZ DDD AAA HHH'

SELECT * FROM f_split(@x,' ') param
WHERE param.seq > 1
ORDER BY param.val

SSRS 2008 - How keep the SAME order enter in the parameter to be reflected in the report

1 Answers1