Custom Sorting in SSRS
Is it not possible to sort the data directly in Analysis Services or the MDX query ?
February 14th, 2011 4:06pm
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. BI Developer
February 14th, 2011 4:18pm
It possible but it was'nt in the order of >30, 20-30, 12-19, 1-12. So I need a custom sorting in SSRS to achieve that but if you have any other way I am open.
February 14th, 2011 4:26pm
Create Named calculaion field in SSAS as sortAge CASE WHEN invoice_Age >30 THEN 4 WHEN invoice_Age Between 20 And 30 THEN 3 WHEN invoice_Age Between 13 And 19 THEN 2 WHEN invoice_Age Between 1 And 12 THEN 1 ELSE 0 END And use sortAge to sort in SSRS Thanks
February 14th, 2011 4:31pm
It throw invalid field exppression for age_sort calculated field.
February 14th, 2011 4:37pm
Hi, Try adding a calculated field to the dataset. The steps to add a calculated field is * Open dataset, click on fields tab, click add-->calculated field, give a field name (ex:age_sort), in the expression enter below =switch(Fields!ageRange.Value=">=30",4,Fields!ageRange.Value="20-30",3,Fields!ageRange.Value="13-19",2,Fields!ageRange.Value="1-12",1) * Then in the tablix, matrix or chart that uses the dataset, go to properties, click sort tab and select the added field (age_sort) Hope this helpsPlease click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH
February 14th, 2011 5:12pm