Search code examples
reporting-services

Grouping dates by months

I'm having an issue with grouping multiple dates by months. I found this post but somehow I couldn't figure it out.

What I'm trying to do is to count the amount of dates by their total amount in a given month, by program. My SQL request goes like this :

Select SubmissionDate, Program
From [Table]
Where Year = 2023

Basically, this request pulls out all the dates starting from january first 2023. The output looks like this (AAAA-MM-DD) :

SubmissionDate  Program
2023-01-05      A
2023-01-24      B
2023-01-26      A
2023-02-02      A
2023-02-11      C
2023-03-18      B
2023-05-27      A
2023-05-28      A    
2023-06-05      A
2023-06-24      B
2023-06-26      A
2023-06-30      A

From this point, I'm trying to sort a table that will look like this in my report :

    | January | February | March | April | May | June
 A  |    2    |     1    |       |       |  2  |  3
 B  |    1    |          |   1   |       |     |  1
 C  |         |     1    |       |       |     |

The best I could achieve so far is getting the total amount of submissions within a given month, but have been unable to split it by program. To achieve this, I have used the Month() and Count() fonctions on the SubmissionDate variable (see below). However I'm hard stuck on adding the program to the table.

January  | 3
February | 2
March    | 1
April    | 
May      | 2
June     | 4

Any help would be greatly appreciated.

Solution

  • As hannover suggested, you need to use a matrix, not a table.

    Here's a quick GIF on how to do it. (Right-click and open in new windows if it's not large enough to see)

    enter image description here

    To start I recreated your dataset, then added a Matrix, dropped the program onto the rows placeholder, then submission date onto the column placeholder and set the data to CountRows().

    I run the report and you can see we have to change our grouping, so I change both the column group and sort to =Month(Fields!SubmissionDate.Value). I then change the textbox expression to =MonthName(Month(Fields!SubmissionDate.Value)).

    That's it.