Grouping a row of months by year SSRS

3
votes

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

2
sqltsqlreporting-servicesssrs-2008
Are you sure that you sort by this expression by [Month] ? – Bishoy Frank
Yeah, it's sorted by month. – Anthony Sims
You can do what you want much more easily with a Matrix rather than a Tablix. Then you could just a parent group to the column group that comes with the Matrix by default. – R. Richards
Sort your year ascending. – EastOfJupiter

2 Answers

1
votes

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
0
votes

=Switch(Fields!NameOfMonth.Value="January",1,Fields!NameOfMonth.Value="February",2,Fields!NameOfMonth.Value="March",3,Fields!NameOfMonth.Value="April",4,Fields!NameOfMonth.Value="May",5,Fields!NameOfMonth.Value="June",6,Fields!NameOfMonth.Value="July",7,Fields!NameOfMonth.Value="August",8,Fields!NameOfMonth.Value="September",9,Fields!NameOfMonth.Value="October",10,Fields!NameOfMonth.Value="November",11,Fields!NameOfMonth.Value="December",12)

you can do datepart and sort by using above expression Month Sorting