How to Total per Month of Year as a Tablix Column in SSRS 2008 R2

Question

I have a table with stats information per user, let's say

TABLE Stats
user varchar(50)
callsdate datetime
howmany INT

And I need to show a Tablix with the totals where the columns headings should be Jul-2012, Aug-2012, Sep-2012, etc., depending of what is there in the data.

Answer 1

Ultimately you need to set up a group at the report level that is a concatenation of the month and the year.

You could do this at the query level with something like:

select
  [user]
  , monthYear = left(datename(mm, callsdate), 3) + '-' + cast(datepart(yy, callsdate) as char(4))
  , howmany
from [Stats]

Or even do the aggregating at the query level if you can:

select
  [user]
  , monthYear = left(datename(mm, callsdate), 3) + '-' + cast(datepart(yy, callsdate) as char(4))
  , howmany = sum(howmany)
from [Stats]
group by [user]
  , monthYear = left(datename(mm, callsdate), 3) + '-' + cast(datepart(yy, callsdate) as char(4))

With this dataset at the report level you can easily group on the monthYear column as required for your Tablix.

You'll run into an issue with getting correct ordering; I would add another calculated column to the dataset:

select
  [user]
  , monthYear = left(datename(mm, callsdate), 3) + '-' + cast(datepart(yy, callsdate) as char(4))
  , monthYearOrder = cast(datepart(yy, callsdate) as char(4)) + right('0' + cast(datepart(mm, callsdate) as char(2)), 2)
  , howmany = sum(howmany)
from [Stats]
group by [user]
  , monthYear = left(datename(mm, callsdate), 3) + '-' + cast(datepart(yy, callsdate) as char(4))
  , monthYearOrder = cast(datepart(yy, callsdate) as char(4)) + right('0' + cast(datepart(mm, callsdate) as char(2)), 2)

This will allow you to group and order by monthYearOrder but still use the text from monthYear.

If you can't aggregate at the query level, you could do something similar with an expression in the report, e.g. as a calculated column in the dataset:

monthYear: =CDate(Fields!callsdate.Value).ToString("MMM-yyyy")

monthYearOrder: =CDate(Fields!callsdate.Value).ToString("yyyyMM")