?
Solved

SSRS: Scheduling a Report

Posted on 2011-03-02
5
Medium Priority
?
1,035 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
[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
  • 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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 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