Search code examples
sqlt-sqlreporting-servicesssrs-2008

Grouping a row of months by year SSRS

My report displays price_totals per month with each month populating the row header: enter image description here As you can see, the months aren't in order (and they dont necessarily need to be. This is designed to be ran for our fiscal year). Notice that after May, it skips to October. October - December are months in 2015, and then the 2016 months are ran from Jan - May.

To "fix" this, I simply added a 'Year' row above the row of months:enter image description here Im not a huge fan of how this looks. Can I group the 2015 months together, and the 2016 months together to only display each year once instead of showing the year for each month?

Here is my tablix design in case you were curious:

enter image description here

Solution

  • Using SSRS, you can sort by fields that are not displayed. On your column group that generates the months column, set the sort by to the following:

    =Fields!Year.Value & "-" & RIGHT("0" & Fields!Month_Number.Value,2)

    Note that this function will generate a year-month string that will look as follows and will facilitate proper sorting.

    2015-01
2015-02
2015-03
2015-04
2015-05
2015-06
2015-07
2015-08
2015-09
2015-10
2015-11
2015-12