Solved

SSRS: Scheduling a Report

Posted on 2011-03-02
5
1,018 Views
Last Modified: 2012-08-14
I am trying to schedule a report to automatically email on the last day of each month. How do I do this? Since some months have 30 days, and others have 31.
0
Comment
Question by:InfoTechEE
  • 3
5 Comments
 
LVL 6

Expert Comment

by:AkAlan
ID: 35023407
When you schedule a job, there is an option to schedule it for the last day of every month



Capture.JPG
0
 
LVL 6

Expert Comment

by:AkAlan
ID: 35023423
Alaso, here is some code that will return the last day of every month

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

If you don't understand why it works you can read more at this site:

http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

The important part to understand is that 0 in sql  is the date '1/1/1900 ' and with that understanding you can do lots of cool stuff.
0
 

Author Comment

by:InfoTechEE
ID: 35031629
Just to clarify, see the attached image. It shows what options I can use to schedule to email the report.
1.JPG
0
 
LVL 9

Accepted Solution

by:
sureshbabukrish earned 500 total points
ID: 35037964
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
0
 
LVL 6

Expert Comment

by:AkAlan
ID: 35038001
OK, I don't use sql reporting services, what I was showing you was the scree shot from SQL Server Agent. Is creating a job that runs a report from the agent an option?
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now