Solved

Custom Sorting in SSRS

Posted on 2011-02-13
5
2,042 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:Projack
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:devlab2012
Comment Utility
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.
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
0
 

Author Comment

by:Projack
Comment Utility


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

Author Comment

by:Projack
Comment Utility
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
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
if the age-range is a column in your query that provides data for your report then you could replace that query data with the CASE statement like below:
SELECT
....
      CASE
            WHEN [age-range] = '>30'    THEN 4
            WHEN [age-range] = '20-30'  THEN 3
            WHEN [age-range] = '13-19'  THEN 2  
            WHEN [age-range] = '1-12'   THEN 1 ELSE 0 END as SortOrder,
....
FROM your_table_name
ORDER BY 2 DESC-- 2 is the number of SortOrder column in the SELECT list

Hope this helps...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now