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
Free Windows Admin Tool Kit Click here and download it now
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
Free Windows Admin Tool Kit Click here and download it now
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
Free Windows Admin Tool Kit Click here and download it now
February 14th, 2011 5:12pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics