Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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. –  SnOrfus Mar 8 at 0:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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