September 11, 2013 at 2:46 pm
I've got a report I recently did in SSRS 2005. The users want to be able to sort the returned data by one of 2 columns (either service type of date of service). I've just Binged this and am getting the feeling that making it possible for users to specify which column to sort by wasn't added until SQL Server 2008, at the earliest. Is that true?
If that isn't true, if I can make it possible for users to specify which column to sort by in SSRS 2005, how is it done?
Kindest Regards, Rod Connect with me on LinkedIn.
September 12, 2013 at 7:08 am
While it isn't ideal because it requires the report to be re-run if they change the parameter, I usually implement this with a Sort By parameter on the report itself. Then you set the sort property in the table/list on the report based on the selection.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 12, 2013 at 8:10 am
Jack Corbett (9/12/2013)
While it isn't ideal because it requires the report to be re-run if they change the parameter, I usually implement this with a Sort By parameter on the report itself. Then you set the sort property in the table/list on the report based on the selection.
That's an interesting idea, which I didn't know anything about. I came across an "Interactive sort" which could be done by right clicking on the column header (not at all obvious to me that could be done). Your idea sounds like a different approach. How would a user be able to interact with that?
Kindest Regards, Rod Connect with me on LinkedIn.
September 12, 2013 at 8:34 am
Rod at work (9/12/2013)
Jack Corbett (9/12/2013)
While it isn't ideal because it requires the report to be re-run if they change the parameter, I usually implement this with a Sort By parameter on the report itself. Then you set the sort property in the table/list on the report based on the selection.That's an interesting idea, which I didn't know anything about. I came across an "Interactive sort" which could be done by right clicking on the column header (not at all obvious to me that could be done). Your idea sounds like a different approach. How would a user be able to interact with that?
It isn't really an interactive sort that you get with 2008+. It is just another report parameter that you use to sort the data when displayed. When I do this I don't use the Report Parameter as query parameter, some do and then do dynamic sorting in the query, I just use it to determine sort order for the display. For example if your report displays customers by salesperson and sales region (contrived) and some users like to see the report ordered by salesperson and others like to see it sorted by sales region. In 2008+ you could enable interactive sorting and the users can do what they like. With 2005 you can't. So assuming that the data is in a table and has no grouping, I add a sort by parameter to the report that has Salesperson, Sales Region as options. Then in the table properties I set the sort order with an expression like:
IIF(Parameters!SortBy.Value = "Salesperson", Fields!salesperson.Value, Fields!salesRegion.Value)
Now when the report is run the user will see the data the order selected. AS I said earlier, the downside is that if the user decides to change the order, the report has to be totally re-run, all the way back to the SQL Server, even though the data hasn't changed. But, I'm not aware of another way to do this.
The same idea applies if you have some level of grouping, you just have to apply the sort at a different level than the table level.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 7, 2013 at 5:42 pm
Jack Corbett (9/12/2013)
Rod at work (9/12/2013)
Jack Corbett (9/12/2013)
While it isn't ideal because it requires the report to be re-run if they change the parameter, I usually implement this with a Sort By parameter on the report itself. Then you set the sort property in the table/list on the report based on the selection.That's an interesting idea, which I didn't know anything about. I came across an "Interactive sort" which could be done by right clicking on the column header (not at all obvious to me that could be done). Your idea sounds like a different approach. How would a user be able to interact with that?
It isn't really an interactive sort that you get with 2008+. It is just another report parameter that you use to sort the data when displayed. When I do this I don't use the Report Parameter as query parameter, some do and then do dynamic sorting in the query, I just use it to determine sort order for the display. For example if your report displays customers by salesperson and sales region (contrived) and some users like to see the report ordered by salesperson and others like to see it sorted by sales region. In 2008+ you could enable interactive sorting and the users can do what they like. With 2005 you can't. So assuming that the data is in a table and has no grouping, I add a sort by parameter to the report that has Salesperson, Sales Region as options. Then in the table properties I set the sort order with an expression like:
IIF(Parameters!SortBy.Value = "Salesperson", Fields!salesperson.Value, Fields!salesRegion.Value)Now when the report is run the user will see the data the order selected. AS I said earlier, the downside is that if the user decides to change the order, the report has to be totally re-run, all the way back to the SQL Server, even though the data hasn't changed. But, I'm not aware of another way to do this.
The same idea applies if you have some level of grouping, you just have to apply the sort at a different level than the table level.
Interactive sorting is available in 2005. It works great with table with no grouping and have used in reports with one level of grouping.
If you need multilevel sorting, viz., sort by col1 then col2 then col3, then I would use report parameter.
October 9, 2013 at 6:06 pm
Rod at work (10/8/2013)
We have now migrated to SQL Server 2012, with SSRS 2012. Does SSRS 2012 allow for multiple sorts, or do you still suggest that we use a report parameter?
Unfortunately I haven't worked on 2012. But wish such feature is introduced 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply