Sorting Parameter values in SSRS Model based report
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.
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.
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.
SELECT DISTINCT WCSR.CUSTOMER_NAME
ORDER BY CUSTOMER_NAME
Please let me know if it works.
March 19th, 2008 9:27am
You should read before you answer. Jayant is not using T-SQL!
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...!
April 15th, 2008 9:36pm
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.
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 7: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 9: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?
December 1st, 2011 1:49am
This work! Thank you very much for providing the workaround! Now report builder and report model can somehow sort parameter list!
April 26th, 2012 11: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 27th, 2012 12:23am
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 27th, 2012 6:57am
Indeed, it wiil only affect on sort direction for fields on the filter dialog box of the query designer window.
April 27th, 2012 2:20pm
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 8: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 21st, 2012 10:28pm
This is by far the best answer (provided by JohnMKelly)
I could find - it worked perfectly to sort my parameter drop down list.
I tried setting the SortDirection in my Report Model (as suggested by Zeilo), but
that had no effect.
But if I can suggest one other option (for Report Builder 3.0): Create a Shared DataSet. For example, I created a Share DataSet for my clients. The DataSet returns a Client ID and a Client Name. I even provide it with a parameter: isActive
(1=Active, 0=Inactive, 2=Both Active/Inactive). To produce the results, I developed a query and ORDER BY Client Name. With the Shared DataSet, I can easily reuse and ensure all ad-hoc reports show the exact same parameters.
October 22nd, 2012 3:33pm
I've also just used JohnMKelly's technique successfully in SSRS 2008. This is a confusing limitation of the report model.
Looking at the T-SQL that the report model is emitting, with a single field from a single entity - there is not SORT BY. When I add a second field (in my case, I only want one, so I just add it twice) it adds SORT BY [Field1], [Field2].
This gives me the behaviour I want.
When I add a third field, that just gets included in the SORT BY clause in the oder it occurs, e.g. SORT BY [Field1], [Field2], [Field3].
By the looks of it, you can control the sort order by ensuring you have two or more fields in your query, and they appear in the preferred order of sorting precedence.
December 6th, 2012 12:17am
hey Ferry !!
Thank you !! it actually worked, the first field in the dataset is used for sorting !!
July 5th, 2013 5:59am