Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS 2005: Allowing the user to specify sort order Expand / Collapse
Author
Message
Posted Wednesday, September 11, 2013 2:46 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:49 AM
Points: 738, Visits: 1,307
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.
Post #1493905
Posted Thursday, September 12, 2013 7:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 10:57 AM
Points: 10,387, Visits: 13,454
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1494155
Posted Thursday, September 12, 2013 8:10 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:49 AM
Points: 738, Visits: 1,307
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.
Post #1494194
Posted Thursday, September 12, 2013 8:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 10:57 AM
Points: 10,387, Visits: 13,454
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1494216
Posted Monday, October 7, 2013 5:42 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 6:12 PM
Points: 827, Visits: 342
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.
Post #1502426
Posted Tuesday, October 8, 2013 7:53 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:49 AM
Points: 738, Visits: 1,307
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?

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1502619
Posted Wednesday, October 9, 2013 6:06 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 6:12 PM
Points: 827, Visits: 342
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 :)
Post #1503334
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse