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. 🙂

SQLReportModelDP.zip

Comments (5)

  1. TheMilkMan says:

    Hi,

    Is the zip file correct? It seems to contain a SQLTimeoutDP assembly and not SQLReportModelDP as specified in the NewConfigEntries.txt file?

    Thanks

  2. KathyDavis says:

    I implemented this custom data processing extension, but it only works sometimes!  Any suggestions?

    Thanks,

    Kathy Davis

  3. kelvinaston says:

    Hi Bob,

    I’m trying to implement your dropdown sorting code but haven’t met with much success. I initally received Semantic query execution errors but managed to get around these by adding the IdbConnectionWrapper code from the updates to the original post. However, I’m not finding that anything is actually sorted. Are you aware of any other changes I may need to implement to get this working?

    Thanks for any advice that you can offer, and for supplying the code.

    Regards

    Kelvin

  4. Has there been an update for this to work with RB 3.0? says:

    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?

  5. Derek Price says:

    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