How to include categories for which there are no values in an ssrs chart 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] &gt;= DATEADD(MONTH, -7, @CurrentMonth)
        [When] &lt; @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?
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 &gt;= DATEADD(MONTH, -7, @CurrentMonth)
    Date_eval &lt; @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.