Sort in SSRS 2008 not working
reporting-services, ssrs-2008
Solution
You can sort the tablix in a different manner to what is being displayed.
- You should setup the tablix as per normal with the region as the display values, this being the `teamgroup.name`.
- Using the sort option on the tablix will oftern not work depending on the makeup of your table. The best and most reliable way is in the grouping properties window right click the details row and edit the sort by options.
- Here you can edit the expression for the sort and I suggest you use a `switch` function to change the names to numbers, or better yet use the SQL `region_Sort` column if you have one.
- Then set the order to a to z.
If you have groups in your tablix you will need to do this for the `teamgroup.name` region/group instead of the detail one.
Problem
I need to modify an existing report using SSRS 2008 with a Region name and it may contain 10 different regions. There is only one column set up for the region which may contain 10 different values. I believe it is a tablix. Currently, the regions are sorted in the code alphabetically but I have to sort them differently, so I assigned a number to each in the CASE statement based on the desired order. I then sorted the regions by the required order in the column itself (tablix) and the regions are sorted in the desired order in the report but unfortunately, the number assigned is in the report instead of the region name. Instead of getting BF CF CO CL etc…. I get 1 2 3 4 etc in the heading of the report. ``` CASE , WHEN teamgroup.Name = 'BF' THEN 1 WHEN teamgroup.Name = 'CF' THEN 2 WHEN teamgroup.Name = 'CO' THEN 3 WHEN teamgroup.Name = 'CL' THEN 4 WHEN teamgroup.Name = 'CN' THEN 5 WHEN teamgroup.Name = 'GA' THEN 6 WHEN teamgroup.Name = 'IN' THEN 7 WHEN teamgroup.Name = 'KY' THEN 8 WHEN teamgroup.Name = 'MD' THEN 9 WHEN teamgroup.Name = 'NF' THEN 10 END AS Region ``` I tried to display teamgroup.name as region and then used Region_sort as the alias for the CASE statement, but it did not work. In the Tablix Properties, I used sort by: region_sort and order is A to Z but the regions were sorted alphabetically. any help will be greatly appreciated.