I have a client who wants to create report subscriptions which run on the last day of each month. I am surprised to discover that this doesn’t come out-of-the-box with Report Manager.
The subscription schedule allows a number of options by hour, day, week, month. The Monthly Schedule option allows for Last week of month, but the user then has to choose the day of the week or calendar day. And when I insert 31 as the calendar day it gives an error message, presumably because some months do not have a 31st day. There is no option for last day of month.
Here is one workarounds to this: One option would be to run the subscription on the first day of the month, defaulting the report to the previous month. For this to be an option you would need : 1. A data parameter in the report and 2a. The report already defaults to the previous month or 2b. An edition of SQL server which supports data driven subscriptions in order to override the default parameter to last month. This is not an option for my client.
The solution that worked was a little different. You need access to be able to create a Shared Schedule on the Report Manager site, and access to the ReportServer database and SQL Server Agent on the report server. What you will do is create a Shared Schedule, which in turn creates a SQL Server Agent job. And then you will edit that job in SSMS to run on the last day of the month. Any report subscriptions can then use that Shared Schedule to run on the last day of the month. You could do something similar for each individual subscription, but why would you, when you can do it only once in a Shared Schedule.
Here are the steps:
1. Create a Shared Schedule in Report Manager
In Report Manager, go to Site Settings and select Schedules. You will need the right permissions to be able to access this. Click on New Schedule. Create a schedule. It doesn’t really matter what you set it to, since you will be changing it on the back end. Note that the front end Schedules list will reflect the changes that you make to the back end, but if you try to edit the schedule your original values will still be there. Give the schedule a descriptive name to identify it. I called it "Last Day of Month".
2. Find out the ScheduleID of the Shared Schedule you have created
Run this query in the ReportServer database to find out the ScheduleID.
SELECT ScheduleID FROM Schedule WHERE Name = 'Last Day of Month'
3. Find the SQL Server Agent Job for that ScheduleID
In SSMS navigate to SQL Server Agent and find the corresponding job.
4. Edit the properties of that job to run on the last day of the month
Make sure the schedule is Enabled.
The Shared Schedule will reflect Last Run and Next Run values correctly in the Schedules pane. Note that the details of the schedule will NOT reflect this, since it is not an available option in the Report Manager interface.
Now a user can choose this schedule when setting up a subscription.
I found this blog by Andreas Halleraker really helpful in finding a good solution.