SSRS custom sort with calculated column in a matrix

Question

I have a matrix in SSRS report as shown below where Month_end is the column group and I have added columns - Q1 Total,Q2,Q3,Q4 with expression where Q1 Total=Sum of values for Jan, Feb and Mar and so on for others. Q1,Q2,Q3,Q4 and Total 2021 are not query fields, they are calculated using expressions SUM and IIF.

Below is the expected output. I want the columns in this order. How do I sort them this way. If I sort the columns by the Month_end date, they will come in order - Jan, Feb, Mar, Apr, May etc. but I want to get Q1 Total column after Mar and Q2 column after Jun and so on. I have tried doing custom order but didn't know how to reference Q1 Total column in the sort order as it is not a query field. How do I sort them as shown in the expected output.

Answer 1

You'll need (if you don't already have) some form of ID for the month, quarter and year against each record.

So "May 2021" would be Month 5, Quarter 2, Year 2021.

• In your column groups, start with the months, order this group by month
• Next, add a parent group to the month group that groups by quarter, order this group by quarter
• Finally add a parent group to the quarter group that groups by year, order this by year.

The expressions for all the value columns (month, Quarter and Year totals) will all b ethe same. They can then be simple sums e.g.

=SUM(Fields!Sales_Value.Value)

There is no need to have IIF statements in you expression if the data is grouped correctly.