Solved

How to schedule a report to run on the last day of the month in SSRS?

Posted on 2008-11-03
9
1,437 Views
Last Modified: 2012-08-13
I would like to schedule an SSRS report to run on the last day of each month, I cannot figure out how to do this on the scheduling page.

Will I be forced to amend my report and schedule it to run on the first day of the month for the previous month?
0
Comment
Question by:catherinelouise
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 42

Accepted Solution

by:
Eugene Z earned 500 total points
ID: 22936584
Create a subscription  then:
you can try to do
like in the link
http://developmentnow.com/g/115_2005_11_0_0_629283/Scheduling-outside-SRS.htm


0
 
LVL 5

Author Closing Comment

by:catherinelouise
ID: 31512637
Thanks EugeneZ - I haven't tested yet but certainly looks like the way to go.

Regards.
0
 

Expert Comment

by:Gav-B
ID: 23907768
I don't know what it is about that site but I am blocked (unusual) from viewing all of developmentnow.com on my work computer.

Could somebody kindly post the main details of this link http://developmentnow.com/g/115_2005_11_0_0_629283/Scheduling-outside-SRS.htm
to answer the "Last/First Working day of the month" question?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 5

Author Comment

by:catherinelouise
ID: 23907983
Here you go:

Hello All!

I have a requirement to set a schedule to run a report on the Tuesday
following the last Saturday of the month. It appears that this is not
possible via the Web UI so I hear I need to schedule it outside SRS. Oh yeah
and to make it more complex, if the Tuesday in in the new month, quarter, or
year then the parameter should select the previous month, quarter, or year
not the default of current month, current quarter, or current year. I think
the last part can easily be handled by getting the parameter values from the
date of the last saturday of the month, which would always give you the
correct month for month end reporting no matter if the next Tues is in the
next month or not.

what I need is the syntax for defining the parameters and rendering the
report. I would also like to specify a network drive where it can be
archived, email a link to an audience, and save a snapshot in history.

Can someone please help me with this??? If not all then parts would be
appreciated!!!

Anthony
      Kmistic
12/19/2005 5:35:03 AM
I am in the same situation. Were you able to resolve this?
--
--------------------------------------------
Yes, I searched first :)


[quoted text, click to view]
"anthonysjo" wrote:

> Hello All!
>
> I have a requirement to set a schedule to run a report on the Tuesday
> following the last Saturday of the month. It appears that this is not
> possible via the Web UI so I hear I need to schedule it outside SRS. Oh yeah
> and to make it more complex, if the Tuesday in in the new month, quarter, or
> year then the parameter should select the previous month, quarter, or year
> not the default of current month, current quarter, or current year. I think
> the last part can easily be handled by getting the parameter values from the
> date of the last saturday of the month, which would always give you the
> correct month for month end reporting no matter if the next Tues is in the
> next month or not.
>
> what I need is the syntax for defining the parameters and rendering the
> report. I would also like to specify a network drive where it can be
> archived, email a link to an audience, and save a snapshot in history.
>
> Can someone please help me with this??? If not all then parts would be
> appreciated!!!
>
> Anthony
      anthonysjo
12/19/2005 8:22:02 AM
Yes. Here is what you do. Create a subscription or a shared schedule but
set it to run only once and make it in the past. This will create a Schedule
ID in the database that you can reference later. Then open the Report Server
Database and return all rows for either the Schedules or Subcriptions table
depending on what you created. Copy the Schdule ID out of the table and make
note of the Event Type.

Now that you have this you can use the following code to fire the report
manually:

exec ReportServer.dbo.AddEvent
@EventType='SharedSchedule', --Enter the event type here from the schedule
table in ReportServer Database
@EventData='29C3FF88-D0D4-4B8A-A5D3-55DCBA8C215D' --Enter the subscription
ID here from the schedule table in ReportServer Database


I put this code inside the following stored procedure that is executed every
tuesday by a SQL job. If the @myoutput date = Getdate then it executes the
code above otherwise it does nothing.

CREATE PROC dbo.MONTH_END_SCHEDULE as
--Declare variables
declare @myoutputdate datetime
declare @mydate datetime
declare @minus int
declare @TuesdayFound char(1)
declare @subject varchar (255)
--set variables
set @TuesdayFound='N'
--seed the date with the last day of the month
set
@mydate=dateadd(dd,-1,convert(datetime,convert(varchar(2),datepart(mm,dateadd(mm,1,getdate())))+'/1/'+
convert(varchar(4),datepart(yy,dateadd(mm,1,getdate())))))

--a variable to backwards through the days of the month
set @minus=0
WHILE @TuesdayFound = 'N'
BEGIN
if datepart(dw,dateadd(dd,(@minus*-1),@mydate))=7 -- Find Last Saturday
BEGIN
set @myoutputdate=dateadd(dd, 3,(dateadd(dd,(@minus*-1),@mydate))) --This
will add 3 days to the last Saturday
set @TuesdayFound = 'Y'
END
set @minus=@minus+1
END

print @myoutputdate

if datepart(dy,(getdate()))=datepart(dy,(@myoutputdate))
BEGIN
exec ReportServer.dbo.AddEvent
@EventType='SharedSchedule', --Enter the event type here from the schedule
table in ReportServer Database
@EventData='29C3FF88-D0D4-4B8A-A5D3-55DCBA8C215D' --Enter the subscription
ID here from the schedule table in ReportServer Database
PRINT datename(mm, @mydate)+ ' ' + datename(yyyy, @mydate)+ ' Reports Fired '
SET @subject = datename(mm, @mydate)+ ' ' + datename(yyyy, @mydate)+ ' Month
End reports are ready for viewing '
Exec master..xp_sendmail
@recipients = 'someone@somewhere.com',
@copy_recipients = 'someone@somewhere.com',
@subject = @subject,
@message = 'Month End reports are now available via reporting services.

You can click on the link below and you will be taken directly to the
Month-End reports folder where you may choose to view the most recient
reports or view the history for archived reports.

http://localhost/Reports

If you have any questions please send an email to
someone@somewhere.com'
END
GO


0
 

Expert Comment

by:Gav-B
ID: 23909244
Thanks Catherine!

Did you get it to work as you wanted?
0
 
LVL 5

Author Comment

by:catherinelouise
ID: 23909465
Well as I'm a fan of working smarter not harder (aka lazy) - I fiddled my report and have it run at 00:01 on the first day of every month (which is easily set up within Subscriptions) and return data for the previous month.
0
 

Expert Comment

by:Gav-B
ID: 23910026
LOL, good idea :) if it works ok for you.

I'm thinking of doing the same thing for "First Working Day" and plumping for just the 1st of every month.

BUT, unfortunately, I really do need to be able to schedule the morning of the last working day of the month so I'm plugging away.
My perfectionism wont let me give up in any case ;)
- Gav B.
0
 

Expert Comment

by:Gav-B
ID: 23910155
http://www.informit.com/articles/article.aspx?p=470593&seqNum=7
 ...Gives some good insight into how SSRS schedules work.
 
- Gav B.
0
 
LVL 5

Author Comment

by:catherinelouise
ID: 23910198
Nice one - thanks!  Hope you get it worked out.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question