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.
March 11th, 2008 12:32pm
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
March 19th, 2008 2:27am
Anupama, You should read before you answer. Jayant is not using T-SQL! -Kushal
April 8th, 2008 12:58pm
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
April 16th, 2008 2:35am
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.
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.
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
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???
April 26th, 2012 11:57pm
Indeed, it wiil only affect on sort direction for fields on the filter dialog box of the query designer window.
April 27th, 2012 7:20am
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.
May 26th, 2012 3:39pm