Sorting in SSRS 2008 does not work

Question

I need to modify an existing report using SSRS 2008 named Region, and it can contain 10 different regions. There is only one column for a region, which can contain 10 different values. I believe this is a table. Currently, regions are sorted by code in alphabetical order, but I have to sort them differently, so I assigned a number in each of the CASE statements based on the desired order. Then I sorted the regions in the required order in the column itself (tablix), and the regions are sorted in the right order in the report, but, unfortunately, the number indicated in the report is instead of the name of the region. Instead of getting

BF CF CO CL etc ..... I got 1 2 3 4 etc. In the title 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 groupgroup.name as a region, and then used Region_sort as an alias for the CASE statement, but it did not work. In the Tablix properties, I used sort by: region_sort and an order from A to Z, but the regions were sorted alphabetically. Any help would be appreciated.

Answer 1

You can sort tablix differently with what is displayed.

• You should configure tablix according to the normal with the area as the displayed values, this is teamgroup.name .
• Using the sort option on the tablix tab will not work depending on the composition of your table. The best and most reliable way is to right-click the details bar in the grouping properties window and edit the sorting by parameters.
• Here you can edit the expression for sorting, and I suggest you use the switch function to change the names for numbers, or better use the region_Sort SQL column if you have one.
• Then set the order from a to z.

If you have groups in your tablix, you will need to do this for the region / group teamgroup.name , and not from the detailed one.