The other day, someone requested that a report in SSRS be sorted differently by default. While that might make sense if everyone wants it that way, more than likely you might have people that want a report sorted differently by default. How to do it?
There are probably a few ways, but this is what I did.
First, I added two parameters. “SortByDefault” and “SortOrder”
The “SortByDefault” will be a drop down of your columns you want to sort by for your dataset (or group, or table/tablix)
The “SortOrder” is simply Asc (1 to N, A to Z) and Desc (N to 1, Z to A)
Now, here is how mine look:
SortByDefault (I have two columns I want to allow sorting by, PointsLeft and StackRank):
Now comes the fun stuff: Making it work.
Make sure you remove any “ORDER BY” in your dataset (you don’t have to but this makes it easier).
I also have every column in the report set up for interactive sorting based on the column header/column it shows, but not sure that is necessary here, I just wanted to put that out there just in case
You want to get to your sorting options. So in my case, I have a tablix, so get to your tablix property window and the sorting option:
Now you can see, my “Sort By” and “Then By” are expressions. It is kind of weird here. Also you can’t set expressions for “Asc” or “Desc” so what I had to do was trick it somewhat.
the first is to handle the asc option:
the second is to handle the desc option
You can see, some magic. If the order by is XYZ then use the field, otherwise 0. If you notice from the screenshot, first one is A to Z (Asc) and the second one is Z to A (Desc). So we are basically telling SSRS to sort by the param or not based on the order by option and it chooses the right order by (ASC/DESC). I think this was easier in SQL 2000 SSRS 🙂
Well now you should be able to test your report and try to sort orders. What I did next is make my params hidden. The defaults are what I wanted for the existing report (Order By PointsLeft DESC), and what I did next is create a linked report and set the hidden parameters int he report options in Report Manager to (Order By Stack Rack, ASC)
Now I have one report, with hidden sorting params and I can create linked reports with different sort options without having to create a new report. I could add all columns to the choices, or even let users choose as parameters (but they already have interactive sorting in this case).
Happy Report Buildin’!
13 replies on “Dynamic Sorting Using Parameters in SSRS”
Brilliant! Why didn’t I think of that? Thanks for the tip.
Reblogged this on Glacier's Blog and commented:
Steve Novoselac has done an outstanding job of explaining how to set up your SSRS reports so your users can dynamically sort their reports. This reference document was very easy to follow and within minutes I was able to achieve the results as described.
Great article! Thanks for sharing your documentation!
Thanks, Steve Novoselac. A very clear and put me on the right track immediately.
I tried this great thing in my report. I think I did the exactly the same thing. But I got the following error when try to run the report ” A Value expression used for the report parameter ‘SortByDefault’ refers to a field. Fields cannot be used in report parameter expressions”.
What could be wrong? The value I used are just field from my dataset – columns from the result table.
Could you please shed some light here?
Huzzah! Worked like a charm.
Great tip – many thanks.
Perfect solution, easy to adopt and implement.
Thank you! Very helpful!
Everithing is running perfectly except for the descending order. When I choose Desc option the first sort expression still interfere and the sorting is not applied. Help me please
Can I sort report using more that 1 column? Example : “Name” then “Age” then “Class”
Can I sort using multiple parameters? column 1 then column 2.