Home Dashboard Directory Help

Sorting parameters for reports developed with report builder by Roger Rombooth



Sign in
to vote
Type: Suggestion
ID: 305893
Opened: 10/20/2007 2:33:59 AM
Access Restriction: Public


Using report bulider in reporting services does not allow to sort parameters. This is no issue if there are only a few values the user can select. But if there are many (>10), the values of the parameters look scrambled and confuses the user.
Sign in to post a comment.
Posted by JD Houle on 3/9/2011 at 11:30 AM
We have SQL Server 2008 R2 and using Report Builder 3.0. We tried the proposed workaround, and it does not support Report Models.

Users are refusing to use the Report Builder tool. Our project of implementing SSRS 2008 with Report Builder 3.0 is now in jeopardy.

I cannot imagine how such a basic and essential functionality is not available, even after 4 years that this issue is known to Microsoft.
Posted by Mark Tassin on 12/20/2010 at 8:50 AM
This is a pretty hiddeous oversight on Microsoft's part. Unsorted parameters = unuserfriendly interface to report consumers.

And while this can be fixed if we write SQL queries for all the pieces of a report, Report Models are broken by this, and they are far more versitile as they don't require users to know SQL.

*sigh* I was really liking Microsoft's BI tool, but this might break it for user acceptance and force me to recommend SAP or Cognos for their greater maturity and userfriendliness.
Posted by Nepal Sushil on 10/30/2010 at 10:08 AM
This feature is not supported even in Report Builder 3.0!!!!!!!!!!!!!!!!!!!!!!
Posted by Shashidhara Krishnamurthy on 5/18/2010 at 2:23 AM
Even the workaround mentioned doesnot seem to be working, if we are using a Report Model to create reports using Report Builder 2. 0.
Posted by Wayne Snyder on 3/8/2010 at 11:22 AM
It is difficult to imagine a parameter which should not be sorted.
Posted by james_jones on 2/12/2010 at 1:52 PM
People hate picking things out of unsorted lists. This is the #1 complaint I hear from users regarding RB. If this issue isn't fixed in Report Builder 3.0 then we will abandon this technology. Sorry, but this is a huge oversight on Microsoft's behalf.
Posted by BillVo on 10/17/2009 at 2:13 PM
Sorting *should* occur automatically based on the "SortAttributes" setting of the entity in the Report Model. Beyond this, the business user should be able to specify a sort order directly for the parameter, defaulting to the SortAttributes setting.

Posted by Eric Strom on 10/7/2009 at 9:00 AM
It seems this problem is alive in well in RB 2.0. Any status on this?
Posted by jefobr on 1/6/2009 at 10:07 AM
In Report Builder 2.0 I don't see a way of Sorting Parameters while using a Model. Semantic Queries are generated automatically when adding a Parameter. Most report building products allow you to sort Alphabetically. If this were T SQL I could easily do this. Does anyone know of a workaround?
Posted by Microsoft on 10/23/2007 at 4:34 PM
Thank you for reporting this issue. We will consider fixing this in a future release.
Sign in to post a workaround.
Posted by Melissa Field on 10/26/2011 at 12:10 PM
I have found if the dataset is only a few fields (such as for a parameter value) the you can use the Group By as a workaround to sort the list.
Posted by Seth Hildebrand on 1/27/2011 at 1:58 PM
It appears to me that sorting is done ascending based on the columns placed from left to right. For example, I added the TOP (100) PERCENT/ORDER BY to my queries and it had no affect on the final reports built from my Report Model. When I moved the column I wanted to sort on to the left most column, it began sorting in ascending order.
Posted by Dayne on 8/24/2010 at 1:01 PM
We have also found that adding the field to your query twice will cause it to sort on the field. For example, if my query is a list of last names, I can add the last name field to the query twice instead of just once, and it will sort on last name.
Posted by doug.sublette on 7/9/2010 at 11:09 AM
Instead of a TOP(x), you can use the "TOP (100) PERCENT" instead.

Select TOP (100) PERCENT [ID], [User]
From [User]
Order By [User]
Posted by james_jones on 2/12/2010 at 1:41 PM
I filed a support issue with Microsoft and this is the official workaround that we concluded:

1. At the Data Source View level, convert the parameter-in-question's table to a named query. (Right-click table, "Replace Table", "With New Named Query")
2. Perform an ORDER BY on the parameter column.
3. Insert "TOP(x)" after the SELECT keyword, where x is equal to some arbitrarily high number that you hope the number of table elements will not exceed.

Clearly a hack, but the only workaround AFAIK.