March 29, 2012 at 1:05 pm
I designed a relatively simple report in SSRS 2008 R2 and publish it on SharePoint. The report needs to be run at the beggining of each month and get published via SSRS subscription function on SharePoint. That's easy if back end database had live data. But it doesn't and I have to set my report date with two month lag. So, I am using the following code to define my Report Start and End Dates:
declare @start_date datetime,
@end_date datetime,
set @start_date = dateadd(mm, datediff(mm, 0, getdate()) -2, 0)
set @end_date = dateadd(mm, datediff(mm, 0, getdate()) -1, -1)
Two Issues/Questions:
1. If I run this report today, I get all January 2012 records except for Jan 31st. I'd assume that's happens since I am running the report today rather than the begining of the month (Let's say April 1st 2012).
2. I need to create a second but similar report that its' records should be cumulative. For example, if I run this second report in April not only I should get Feb records but the report should include Jan records as well. Thus, in contrast to the first report that includes records for only one month (Jan), the second report will include cumulative records (Jan & Feb. )
My question is how do I set start and end date for this second report so that it grabs the cumulative records?
Thank you,
March 30, 2012 at 1:34 am
i would look at creating a calendar table with the dates in for start and end of month, that way you can do dateparts on GETDATE to get the month and year, pass that into the calendar table to get the true first and last day of the month so that you always get 31days or 30days, and 28/29 days for Feb
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply