asked on

Custom Sorting in SSRS

I have a dimension with these attribuyes:
Age Age Range
12 1-12
18 13-19
21 20-30
34 >30

I need to sort sort the Age Range field in a in SSRS Tablix in Descending order of >30, 20-30, 13-19, 1-12. The default sorting of z-A in SSRS was not doing so I wrote this expression below is not sorting the age range properly as well:
IIF(SortField.Age Range = >30, SortDirection.Value = 4
ISortField.Age Range = 20-30, SortDirection.Value = 3,
ISortField.Age Range = >13-19 SortDirection.Value = 2,
SortField.Age Range = 1-12, SortDirection.Value = 1)

Does anyone have the idea of expression that could sort the age in order of >30, 20-30, 12-19 and 1-12.

devlab2012

Instead of sorting on AgeRange field, add a new calculated field in the dataset say CalAgeRange with following expression:

=Switch(
Fields!AgeRange.Value = ">30", "1",
Fields!AgeRange.Value = "20-30", "2",
Fields!AgeRange.Value = "12-19", "3",
Fields!AgeRange.Value = "1-12", "4"
)

Now do sorting on CalAgeRange field instead of AgeRange.

lcohan

I would use a CASE statement instead in the SELECT statement providing data for your report and order by that column(number) DESC like:

SELECT id,
      CASE
            WHEN SortField_Age >=30                                    THEN 4
            WHEN SortField_Age >20 AND SortField_Age <30      THEN 3
            WHEN SortField_Age >=13 AND SortField_Age <19      THEN 2
            WHEN SortField_Age >=1 AND SortField_Age <12      THEN 1 ELSE 0 END as SortOrder,
            name
FROM
      clients
ORDER BY 2 DESC-- SortOrder

Projack

ASKER

devlab2012:
It throw invalid field exppression for age_sort calculated field.

Projack

ASKER

lcohan:
1 the age-range is in format >30, 20-30,12-19, 1-12
2. How can I implement this solution in SSRS expression

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial