SSRS Reporting query code

Question

SSRS Reporting query code

khushbu_gupta

Valued Member

Points: 50
More actions
November 19, 2009 at 9:11 pm

#228040

Hi
My report needs to show data for the specified period (normally a whole month). But for comparision purposes, it also needs to show historic data for the calendar month prior to this month, and also data for same month last year. eg. If user wants my data for Oct 09, then my report also needs to show data for Sep 09 and Oct 08.
How do I get these dates in my query?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

tnk SSCrazy Points: 2613 More actions · Answer 1

If I understand your problem correctly, I don't think this is an SSRS question as much as it's a query question. I would suggest left joining to the prior month and then left join again to get the same month last year data. Once this is all in your dataset you can design the report to display it all as you wish.

William S SSC Enthusiast Points: 117 More actions · Answer 2

Hi,

I guess the filter in current current dataset may look like this: datetime_data between @date1 and @date2. This can be changed to something like:

datetime_data between @date1 and @date2 OR

datetime_data between DateAdd(”Month”, -1, @date1) and DateAdd(”Month”, -1, @date2) OR

datetime_data between DateAdd(”Year”, -1, @date1) and DateAdd(”Year”, -1, @date2)

and then group the table in report layout using an expression:

=month(datetime_data) & " " & year(datetime_data)

I hope this helps.

Regards,

William S.

tnk SSCrazy Points: 2613 More actions · Answer 3

It depends on how you want to show the data. If you just want all the data for the previous month or same month last year as additional rows, then sure, you can use OR clauses in the WHERE filter to pull in the correct data. If however, you want to show the previous month or same month last year on the same row, then you need to LEFT JOIN to that data. Either way this is a T-SQL issue and not so much a reporting services issue unless you are asking about sub-reports or some SSRS specific construct.