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 5:12pm
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 7:32pm
Did you ever find a resolution to this issue? I am having the exact same problem. Regards, Joe
March 19th, 2008 1:48am
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 9:27am
Anupama, You should read before you answer. Jayant is not using T-SQL! -Kushal
April 8th, 2008 7: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 9: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 9: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 11: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.
March 4th, 2010 6:38pm
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:52pm