Joe
Joe

Reputation: 173

Sort in SSRS 2008 not working

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

Answers (1)

glh
glh

Reputation: 4972

You can sort the tablix in a different manner to what is being displayed.

  1. You should setup the tablix as per normal with the region as the display values, this being the teamgroup.name.
  2. 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.
  3. 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.
  4. 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.

Upvotes: 5

Related Questions