Last Day of Month subscription for Shared SSRS schedule

Question

Last Day of Month subscription for Shared SSRS schedule

Viewing 13 posts - 16 through 27 (of 27 total)

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

dso808 SSCrazy Points: 2102 More actions · Answer 1

Thanks for the reply Jeff. The script or code I used is for the parameter's default values. I had to use default values for my report so that I could use the subscription service for it. The user is free to change the parameters once they open the report and the report will reopen with whatever month/year data they're looking for.

That's the drawback I mentioned. When the user opens the report, it'll initially show last month's data - so they'll need to change the parameters and reopen the report to get the current month/year or whatever they're looking for.

So I set up the subscription for 1st of every month. The report will grab last month's data because the month parameter defaults to the previous month - which is why I wrote those IIF statements. For the month, it would've been simple to just write "Month(Now) - 1" for the default value. But then there's a problem with January of the new year.

I hope that clarified my post. I'd much rather be able to send this report at the end of the month like everyone else here, but I needed to make this work ASAP and this works fine for our report minus again the little drawback.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

That's what I'm talking about... You have the Users and the Subscription service (whatever that is for you) calling the same proc and you're running into trouble.

The "final" proc should accept date parameters without any defaults no matter what. When a User calls the proc, they call it directly with the required date parameters.

The Subscription service should NOT call the same proc directly. Instead, the Subscription service should call a similarly named proc (I use procname_SS where SS stands for "Subscription Service"). That proc contains the necessary datemath to make two correct date parameters for the preceeding month and then it calls the "final" proc with the correct date parameters. It makes life REAL easy.

So far as the calculation problem you're having with January, why aren't you using the DATEADD function to do your work for you? It KNOWS how to handle dates correctly so you don't need to worry about it. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SqlServerLover Ten Centuries Points: 1348 More actions · Answer 3

Can't you just run on the 1st day of the month at 12 am?

This is how i have done as a solution of this problem .

In my company, there won't be any transactions after 10 or 11 pm, so instead of running them on the last day of the month at 11 pm; i run them on the 1st day of the month.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

SqlServerLover (8/22/2010)
Can't you just run on the 1st day of the month at 12 am?
This is how i have done as a solution of this problem .
In my company, there won't be any transactions after 10 or 11 pm, so instead of running them on the last day of the month at 11 pm; i run them on the 1st day of the month.

Most of the companies I worked for had a 24/7 policy... and transactions between 10 and 11 pm.

The real key here is that you need to be safe. End of previous month runs should run correctly and without human intervention any time on any day for the whole month following.... IMHO that is. 😀 Heh... what good are computers if they require human interaction for things like previous month reports? :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SqlServerLover Ten Centuries Points: 1348 More actions · Answer 5

I did not mean to run manually.

Set up shared schedule to run at 12 am on the 1st of the month.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

SqlServerLover (8/22/2010)
I did not mean to run manually.
Set up shared schedule to run at 12 am on the 1st of the month.

That's what I'm talking about. What happens if there's a catastrophic event or other incident that prevents the job from running at 12AM on the 1st of the month?

You have to prepare for the worst because the worst will happen. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CaliDave SSC Eights! Points: 933 More actions · Answer 7

I created it as a stored procedure in SSMS so I could use it for multiple reports and then just add it as a dataset in SSRS.

First Day of Previous Month:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)

Last day of Previous Month (Time set to 11:59:59pm)

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

I don't remember what i did after that to get it working, but I think it's pretty obvious if you use the wizard. If you have any problems with this let me know and I'll look into it further.

David92595

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

David92595 (8/14/2012)
I created it as a stored procedure in SSMS so I could use it for multiple reports and then just add it as a dataset in SSRS.
First Day of Previous Month:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
Last day of Previous Month (Time set to 11:59:59pm)
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
I don't remember what i did after that to get it working, but I think it's pretty obvious if you use the wizard. If you have any problems with this let me know and I'll look into it further.
David92595

What is the data type of the date/time values you compare the above to? A better way is to use an open-ended value at the upper end of the range test. Instead of

somecolumn <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

you should use

somecolumn < DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)

CaliDave SSC Eights! Points: 933 More actions · Answer 9

Either one works, I prefer to define/ know the exact time the query will be using. Which include the time being 11:59:59...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

David92595 (8/14/2012)
Either one works, I prefer to define/ know the exact time the query will be using. Which include the time being 11:59:59...

The way I showed above does the same thing, you would be pulling all records (for example) greater than or equal to '2012-07-01 00:00:00.000' and less than '2012-08-01 00:00:00.000', there by getting all records for July 2012. Also, it won't be affected by a change in datatype. Using 11:59:59 can miss records greater 11:59:59.000 and less than 00:00:00 of the following day if your column in the comparision is defined as a datetime data type.

Martin Stephenson SSCrazy Points: 2950 More actions · Answer 11

For various reasons lotsof things get set to zero at 00:01 on the first day of the month, so running thereports then isnt a possibility. However running all the queries i need into a set of temp tables at 23:00 on the last day and the reports running from them just after midnight is a work round. Not an elegant one but it is simple. May not be suitable for everyone......

Nate the DBA SSC-Addicted Points: 430 More actions · Answer 12

Because this thread is such a high result on Google for "ssrs schedule last day of month", I wanted to link to this solution: https://dataqueen.unlimitedviz.com/2014/05/report-manager-subscription-last-day-of-month/ . Essentially, while SSRS scheduler may not support "last day of month", the SQL Agent scheduler DOES (at least, 2008 & onward; I did not have a 2005 box to test with!). So we can take advantage of the fact that SSRS subscriptions are implemented simply as Agent Jobs in the SSRS server, create a shared "Last day of month" schedule, and have our SSRS subscriptions use that schedule! Presto.

[Ignoring debates over whether you actually need to use "last day of month" vs. "first second of next month to report on prior month's data" ad nauseum. FYI, there are use-cases for it, despite what the academics say. Don't get me wrong, I prefer the latter, but sometimes you need the former.]

PS: It's worth noting that you may have to manually/behind-the-scenes find the subscription job and change its schedule via SSMS/SQL Agent, rather than via Report Manager. This is fairly simple if you know how to query the ReportServer database; if not, just search the SQL blogging community or obviously these forums -- something like "ssrs find job name of report subscription".

-Nate the DBA natethedba.com

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

Nate the DBA - Thursday, March 15, 2018 9:44 AM
Because this thread is such a high result on Google for "ssrs schedule last day of month", I wanted to link to this solution: https://dataqueen.unlimitedviz.com/2014/05/report-manager-subscription-last-day-of-month/ . Essentially, while SSRS scheduler may not support "last day of month", the SQL Agent scheduler DOES (at least, 2008 & onward; I did not have a 2005 box to test with!). So we can take advantage of the fact that SSRS subscriptions are implemented simply as Agent Jobs in the SSRS server, create a shared "Last day of month" schedule, and have our SSRS subscriptions use that schedule! Presto.
[Ignoring debates over whether you actually need to use "last day of month" vs. "first second of next month to report on prior month's data" ad nauseum. FYI, there are use-cases for it, despite what the academics say. Don't get me wrong, I prefer the latter, but sometimes you need the former.]
PS: It's worth noting that you may have to manually/behind-the-scenes find the subscription job and change its schedule via SSMS/SQL Agent, rather than via Report Manager. This is fairly simple if you know how to query the ReportServer database; if not, just search the SQL blogging community or obviously these forums -- something like "ssrs find job name of report subscription".

Heh... shouldn't ignore such debates, Nate. Lot's of good stuff comes out of those... especially good for developing a thick skin when it comes to the "nauseum" part. 😀

I don't use SSRS but that's a handy link you posted. Thanks for that!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)