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?