SSRS 2008 Dataset Filtering - SUM / Break down by month

Question

I have a dataset that is going to get passed to an SSRS report that looks like this:

CODE TEXT COUNT  MONTH 
100  ABC  20     January 
100  ABC  10     February
200  DEF  15     January 
200  DEF  15     February
300  GHI  0      x 
400  JKL  10     January

This 'x' indicates there is no data for that code/text for the current year, however, I still need it to display. In SSRS I want to have a column to display for the current month (February) and a column to display for year to date:

    CODE TEXT MONTH_TO_DATE YEAR_TO_DATE
    100  ABC  10            30   [20 + 10]
    200  DEF  15            30   [15 + 15]
    300  GHI  0             0    [0] 
    400  JKL  0             10   [0 + 10]

Is this possible in SSRS 2008?

Chris Latta Chris Latta · Accepted Answer · 2012-02-24T00:18:05

This is going to have to be done in SQL so it would be easier to adjust the SQL if you posted what you have already, rather than the data resulting from that SQL.

Anyway, we have to find a way to know it is the current month and display it, which we can do with the SQL CASE statement. Maybe you have a date field you can query which might be cleaner than below, but I'll just run with the data as you have provided (that is, we just have a month name which we compare to the current month using the DATENAME and GETDATE SQL functions - obviously this doesn't work if you have multiple years of data as every February will be aggregated into the current month, but you get the idea for what to do):

SELECT [CODE], [TEXT], 
    SUM(CASE WHEN [MONTH] = DateName(month, GetDate()) THEN [COUNT] END) AS CurrentMonth,
    SUM([COUNT]) AS YearToDate
FROM MyTable
GROUP BY [CODE], [TEXT]
ORDER BY [CODE], [TEXT]

Now, in your comment on Vlad's answer you say you can't do a SUM(COUNT(fieldname)) which you can actually do if you use nested queries, like so:

SELECT [CODE], [TEXT], SUM([COUNT]) AS Total
FROM ( -- This is a nested query
    SELECT [CODE], [TEXT], COUNT(somefield) AS [COUNT]
    FROM MyTable
)
GROUP BY [CODE], [TEXT]
ORDER BY [CODE], [TEXT]

If you want to display x for the zero values then use the following Format for the YearToDate cell:

#,##0;-#,##0;x

SSRS 2008 Dataset Filtering - SUM / Break down by month

3 Answers