Sorting the values in parameter dropdowns in Report Builder
In SQL 2005 the semantic queries generated by Report Builder do not support sorting. This is not a problem in the report itself, because all sorting is defined and implemented in the report definition instead of the query. However, for parameter dropdowns there is currently no solution: if the query doesn't sort the data, there's no way in RDL to define the sort you want. As a result, the values in your parameter dropdowns will sometimes be scrambled.
If you are interested, the attached C# project contains an updated version of the custom data processing extension I shared in a previous post for enforcing query timeouts, This version adds the ability to recognize lookup queries generated by Report Builder (e.g. those generated for parameter dropdowns), and append an appropriate ORDER BY clause to get the data back in the right order. Since the order of the result data is not defined in this release, it should not break anything, and since it uses a very strict Regex to recognize the lookup queries, it should not affect performance of any other queries. This implementation will sort dropdown items in exactly the same order as they appear in the Filter dialog in Report Builder, whether they are attribute values or entity instances (including the use of an entity's SortAttributes if defined).
To try out the sample, download the attached ZIP file and follow the same instructions as before, Please note that, as I mentioned before, this approach exercises an unsupported feature, so if you run into problems and ask MS Support for help, they will tell you to go jump in a lake. :)
Comments
- Anonymous
January 25, 2008
Hi,Is the zip file correct? It seems to contain a SQLTimeoutDP assembly and not SQLReportModelDP as specified in the NewConfigEntries.txt file?Thanks - Anonymous
October 10, 2008
I implemented this custom data processing extension, but it only works sometimes! Any suggestions?Thanks,Kathy Davis - Anonymous
April 22, 2010
The comment has been removed - Anonymous
October 11, 2011
I'm trying to build it, but I can freely admit that these days my skills with Visual Studio are pretty limited to nothing much more than developing SSAS and SSIS packages. Is there a means to make this work simply with RB 3.0? - Anonymous
March 29, 2012
To fix this all you need to do is unhide the parameter that is not sorting properly, the only column should be a VALUE column. Add the SAME field to the data set, not touching anythign else, save the data set.As long as you have the field Sorted in the reporting model properties the field will auto sort it self in the parameters drop down.if you have questions feel free to email at: dp978@msn.com