SSRS Monthly Grouping

Question

We have a single table that contains Order data for all our customers. From this, we would like to create a simple SSRS report that displays 12 months of data from a selected start month (controlled by parameter with 12 months in drop down) and separate the monthly data into one of two department columns.

Basically, we would like the data to display as follows on the report:

Month   Dept1   Dept2   MonthlyTotal
Sept    100     500     600
Oct     100     200     300
Nov     200     100     300
Dec     100     300     400
Jan     0       100     100
Feb     0       0       0
Mar     0       0       0
Apr     0       0       0
May     0       0       0
June    0       0       0
July    0       0       0
Aug     0       0       0
Total   500     1200    1700

What is the best way to achieve this? Should we use a Table or Matrix object? Should we hardcode 12 "month" rows in the report and dynamically populate the cells with expression like logic?

In case it impacts our view above we also would like the users to be able to drill down to the see the underlying detail records for each value.

Hannover Fist Hannover Fist · Accepted Answer · 2018-09-13T20:41:48

Your choice of whether to use a matrix should be based on how the data is in your dataset. If there are two separate columns for each Department then I would use a table with the two fields. If there is a DEPARMENT field that determines which department belongs a record to, then I would go with a matrix and use the DEPARTMENT as the column group.

Your problem with the months is that you probably don't have a table with the future months that are waiting to be populated with data so you'll need to generate them.

To populate the dates, I would start with a table of dates that is made dynamically. Here's something that I usually use to create a table of months. You could tie the start and end dates to your parameters.

DECLARE @START_DATE AS DATE = '2017-01-01' 
DECLARE @END_DATE AS DATE = '2018-08-31' 

;WITH DATE_RANGE AS (
                    SELECT CAST(@START_DATE AS DATE) AS MONTH_START
                    UNION ALL
                    SELECT CAST(DATEADD(MONTH, 1, MONTH_START) AS DATE)
                    FROM DATE_RANGE
                    WHERE YEAR(MONTH_START) < YEAR(@END_DATE)
                        OR
                        (
                            YEAR(MONTH_START) = YEAR(@END_DATE)
                            AND 
                            MONTH(MONTH_START) < MONTH(@END_DATE)
                        )
                    )


SELECT MONTH_START, MONTH_END 
INTO #DATE_RANGE
FROM DATE_RANGE

SSRS Monthly Grouping

1 Answers