Reputation: 173
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.
Upvotes: 4
Views: 5593
Reputation: 4972
You can sort the tablix in a different manner to what is being displayed.
teamgroup.name
.switch
function to change the names to numbers, or better yet use the SQL region_Sort
column if you have one.If you have groups in your tablix you will need to do this for the teamgroup.name
region/group instead of the detail one.
Upvotes: 5