SQL Server Performance Forum – Threads Archive
Dynamic sorting of table dataI have a dataset returned to my report from a stored procedure that takes two parameters, Start Date and End Date. The data is sorted by date asc in the stored procedure but now my users want to be able to toggle whether it is sorted asc or desc. Do I remove the ORDER BY clause and use the data table properties instead? Is there an easy way to do this? TIA, FM.
There is no direct solution available in RP. You can achieve using work around logic. Here is the logic. Add 2 parameters to Report. One for to pass Sort Column Number and other for Sort Order. Pass these two parameters to Store Procedure, based on 2 parameters you can sort the data dynamically in store procedure. You can provide a hyper link (jump to the Same Report.) on each column. For example if you have 2 column in report. For first column add hyper link (jump to same report) and pass extra 2 parameters SortColumn No and SortOrder.
Thanks for the feedback but it’s still not clear how do this. I don’t want to build the query dynamically as it’s too heavy on our resources. How do change a column from sorting ascending to descending either by a parameter or expression? This is really getting to me… FM
I have been told how to do this by one of the MSFT guys. The report has four parameters, a start and an end date, a column to sort by and directional column. The table now has two sort expressions on it: =iif(Parameters!Direction.Value= "Ascending",Fields(Parameters!SortBy.Value).Value,0)
=iif(Parameters!Direction.Value= "Descending",Fields(Parameters!SortBy.Value).Value,0) With each expression having the relevant sort value attached to it. And it works rather well too. Just thought i’d share with the group. FM.
Oh, you’re all welcome. <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />
Thanks for the valuable info on dynamic sorting. I was able to implement it fine under studio. However, after deploying and testing under the manager it has issues. I added the ability to click on the table headers to jump back to the report to sort by that column. Worked fine under studio but under Report Maneger the sorting worked, but the filter dropdowns were then gone.
I too have had that problem. The report’s column headings are underlined and work as hyperlinks to re-sort the report but for some reason it steals the parameters. I’m waiting on an answer from Microsoft to see if this is a bug. I have noticed that this is put in the query string returned to the report server: Parameters%3dCollapsed%26rc%253a If you remove it, hey presto the parameters re-appear! I’m unsure as to how we can choose to not send this when jumping from report to report.