1

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?

1

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.

  • Fortunately, I have a SQL job that is running a bunch of (union'd) queries against a bunch of different remote DBs. It then truncates the local table (that holds the report data) and does a bulk insert of the data. I can probably do the same for the date_dim table. +1 for now. Will marked answer once I've got it working. Thanks Thomas. – Steven Evers Mar 8 '14 at 0:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.