SSRS Chart – How to Include Categories with No Values

sql-server-2008-r2ssrs

I've got a query that gets some data points for the last 6 months (prior to current), akin to this:

SELECT  [DataPoint], [When]
FROM    Table
WHERE
        [When] >= DATEADD(MONTH, -7, @CurrentMonth)
        [When] < @CurrentMonth

And my ssrs chart has a category like this:

=Format(DateAdd(DateInterval.Month, DateDiff(DateInterval.Month, CDate("1900/01/01"), Fields!When.Value), CDate("1900/01/01")), "MMM")

The horizontal axis is configured thusly:

  • Number: Category = Date, Type = Jan 2000

and I've tried setting:

  • Axis Options: Interval = 1, Interval type = Months

… but it doesn't help.

It works great when there are data points within each month, but I have some data sets that only have data for some months (ie. Dec/Jan) and it will only show those months.

Is there any way to get it to show the full 6 months of "data" (or lack thereof) without dumping empty data in the dataset?

Best Answer

The best solution I've found to this type of issue is to create a date dimension table that shows various information for dates- in this case it could simply be date and month, but other implementations I've seen include whether or not a particular day is a holiday or a day in which employees will work.

Then, apply the date criteria to this dimension table and left join the results onto it-

CREATE TABLE date_dim (Date_eval, Month)

...

SELECT  [t.DataPoint], [dd.Month]
FROM   date_dim dd
LEFT JOIN Table t 
   ON t.[When] = dd.Date_Eval
WHERE
    Date_eval >= DATEADD(MONTH, -7, @CurrentMonth)
    Date_eval < @CurrentMonth

The downside of this approach is that you have to populate a table with data and that if you use a date dimension attribute that can change, such as whether or not employees work a certain day, the table is difficult to maintain automatically.

The upside is that you have a framework to build reports like this on, and one that can be reused as often as you need it.

Related Question