Sorting Parameter values in SSRS Model based report
Hi, I have created a report using Report designer (Visual Studio, using Data Model as a data source), in the report I had created few datasets (with single filed) to populate the report parameters,lets say I have created a multi valued Parameter CustomerName and assigned field from a dataset, result are coming correctly and combo box is getting populated but the customers are not in alphabetical order! I want to sort it and need to specify it in report (please note that I am using Report Model as a data source and I cant sort the source table in the data base to get the result sorted) Please let me know if anybody has done that or forward me if know some link which talks about it. Thanks in advance. Regards, Jayant Jape
September 21st, 2007 10:12am

We have some customers that I think are trying to do a similar thing -- in the Report Builder, when they are designing the report, the values come in alphabetized. However when they actually run the report, the parameters are not sorted. I don't know of anything we can do to at this time to assist, other than to make a custom report.If you ever hear of anything, please let me know.
Free Windows Admin Tool Kit Click here and download it now
March 11th, 2008 12:32pm

Did you ever find a resolution to this issue? I am having the exact same problem. Regards, Joe
March 18th, 2008 6:48pm

Hi Jayant Jape, As you have assigned thevalue for parameter from the field from dataset, you can useORDER BY clause in order to display the customer name in ascending order in query. for eg: SELECT DISTINCT WCSR.CUSTOMER_NAME FROM CUSTOMER ORDER BY CUSTOMER_NAME Please let me know if it works. Anupama
Free Windows Admin Tool Kit Click here and download it now
March 19th, 2008 2:27am

I am having the same issue and found this link that is related, but not helpful to me. It states that the semantic query for paramters is not sorted, so you could try indexing the source by the field. I think that this is a needed enhancement by Microsoft - to sort paramter drop-downs automatically (probably by the sort options that are already set for fields in the model - hmm - what a concept!) This may help you, but not me because I am using a view that resides in a separate db from the db that is being accessed and to index a view it apparently needs to be schema bound, which I cannot do because it is another db and because a vendor may change the table in patch updates that I don't want to mess up by binding. Any new thoughts would be nice.. Or if somebody can twist microsoft's arm to add this enhancement to sort parameters in report builder...! http://technet.microsoft.com/en-us/library/ms160347(SQL.100).aspx
April 15th, 2008 2:36pm

You are correct, this is currently a limitation of RB queries. This blog post may help in the meantime: http://blogs.msdn.com/bobmeyers/archive/2007/10/11/sorting-the-values-in-parameter-dropdowns-in-report-builder.aspx
Free Windows Admin Tool Kit Click here and download it now
April 16th, 2008 2:35am

I believe I saw the same question from 2006. I can't believe we still can't sort the contents of a dropdown 2 years later. The blog suggests a complex workaround then says MS will tell you to jump in a lake if you have problems using it.This is an important business need, and needs to be resolved by MS asap.thank you
December 5th, 2008 3:54pm

This works for SSRS 2008, i have no clue if this would work in 2005.I have some info that explains the behaviour of the Report Model query designer a bit, and how to sort your values.If you want to sort on your customer name, make sure the custoner name field comes BEFORE the customer code, or any of the other fields from that same entity. SSRS will sort the fields in an entity by the first field it finds. so if you have the code first, it will sort by the code.Sometime you want the field in the same Entity group, an entity group is when you see like an umbrella like title over the different fields that belong to the same entity. Sometimes you dont want this. When excactly you do want this or not i am not sure of. When I tried it just now with EmployeeID and EmpolyeeName i didnt get it to work with the in the same entity group. When I had the seperated and the EmployeeName first it sorted nicely by EmployeeName. Try it out and use the execute button and see what you get. If the results are in Name order, the parameter should also give you the values in that order.I'm not quite sure if the Entitygrouping, I don't this it does. but the enrity grouping is something you probably want to use in your datasets, it saves performance as it will only look at that entity once instead of 5 times if you ahve the fields spread across the query.When you create your dataset for your parameters you can manipulate the sorting of the values a bit. If you have for example a Profession and a Profession code, you want to make sure they are in the same entity group, easiest way to do this is to do select the 2 fields in your fields list and then drag them at the same time to your dataset area. They will then stick together and form a entity group. The first field in that group is the field that group will sort on. so if you want to sort on the profession name, make sure the Profession field is the leftmost field. If you want to sort on the code, make sure the code is the leftmost field. There is very little info known about the Report Model query designer and we figured some things out after trying a few things. The way that screen works is also very weird are not very consistent. The way i described got us to sort the parameter values just the way we wanted.This isn't a perfect answer but i found it to work for us, and since there is hardly anything known about this i thought i'd share it. I have searched about this a lot as well and couldn't find anything. So I hope it helps.
Free Windows Admin Tool Kit Click here and download it now
March 4th, 2010 10:38am

This may help. I created a view in the underlying database containing just the ID/Code and Description fields. I then created a clustered index on that view with the Description being the first field (and the ID/Code being the second). Now I bring that view into the Report Model's Data Source View, but actually as a named query with the query being SELECT ID, Description from vLookupQuery WITH (NOEXPAND) - the WITH (NOEXPAND) is important since it means that even though RB/RS queries ID, Description, the order becomes left to the default for MSSQL, which in turn becomes the index order - which is Description in alphabetic order. So now I'm creating a series of these little views just for the purpose of creating parameters and cascading parameters.
June 21st, 2011 12:50pm

In Report Builder 3, if you filter on a field and then chose to make that field a prompt (and the ValueSelection is set to dropdown for the field in the report model definition) then a hidden dataset and parameter will automatically get created for you. The parameter's list of available values will point to the newly generated hidden dataset. The newly created hidden dataset will include the single field that you are filtering/prompting on. The results of this hidden dataset will return the data - not sorted. To get it to sort, edit the query for the hidden dataset and add the same field to the query - this will cause the semantic query engine behind the report model to include an order by clause on the field which will return the data sorted.
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2011 1:59pm

By any chance has this issue been resolved, or is it still not possible (without C# code) to sort the values available in a parameter's drop-down in Report Builder 2.0 when using a report model as a data source? Many thanks. Robin
November 30th, 2011 5:49pm

This work! Thank you very much for providing the workaround! Now report builder and report model can somehow sort parameter list!
Free Windows Admin Tool Kit Click here and download it now
April 26th, 2012 4:42am

The correct way to order the values for a parameter when using Report Model is to set the property SortDirection for the attribute. This will work for every report create from the report model and it will not be necessary to do any workarround on hidden dataset.
April 26th, 2012 5:23pm

I changed the "SortDirection" but it did not cause any change! After I re-publish the model, the sorting order still the smae! Anything I need to do more???
Free Windows Admin Tool Kit Click here and download it now
April 26th, 2012 11:57pm

I've been able to get a drop down parameter list to sort by adding a new datasource, and avoid the model altogether. I can then use T-SQL to pull my parameter dataset and specify a sort order in T-SQL. Of course I have to use the pre-existing parameters that are already configured within the Report Builder parameters collection. Also you have to remember that the mapping of the parameters needs to be pointed to my new dataset that is not using the model as its datasource.
May 14th, 2012 1:58pm

Zeilo, are you using Report Builder 2.0 or something else? I am using it and can't see where I can have any impact on 'SortDirection' as you've indicated.
Free Windows Admin Tool Kit Click here and download it now
May 26th, 2012 3:39pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics