Ssrs 2008 r2 sort on a groupinng

SSAS and SSRS
1

In an existing SSRS 2008 r2 report, I am trying to determine how to change the sort order of a grouping called termName in a tablix. The school terms are sorted in descending order. The termName column is defined as varchar(10), not null .
The values for termName are the following:
T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11, and T12.

The problem is the term names end up not have T12 sorted first followed by T11 then followed by T10 then followed by T9.

The sort ends displaying the terms incorrectly in the following order:
T9,T8,T7,T6,T5,T4,T3,T2,T1,T12,T11,and then T10.

Could I possibly use IIF expressions on the sort and/or on the location in the SSRS 2008 r2 report where the value is dispalyed? If so, can you show me what I can do?

Thus can you tell me what I can do sort on the tablix?.

2

One way:

ORDER BY CASE TermName
   WHEN 'T1' then 1
   WHEN 'T2' then 2
   ...
   WHEN 'T12' then 12
   END

Another (riskier, if the value is invalid)

ORDER BY CAST(SUBSTRING(termName, 2, len(TermName) AS int)

1 Like
3

Try setting the sort to the expression: =Val(Fields!ColumnName.Value)

If that doesn't work, then you can use CInt and Mid in an expression to get the numeric values - something like: =CInt(Mid(Fields!ColumnName.Value, 1, 2))

1 Like