Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1055
  • Last Modified:

SSRS: Scheduling a Report

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
InfoTechEE
Asked:
InfoTechEE
  • 3
1 Solution
 
AkAlanCommented:
When you schedule a job, there is an option to schedule it for the last day of every month



Capture.JPG
0
 
AkAlanCommented:
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
 
InfoTechEEAuthor Commented:
Just to clarify, see the attached image. It shows what options I can use to schedule to email the report.
1.JPG
0
 
sureshbabukrishCommented:
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
 
AkAlanCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now