subscription – The Data Queen

Report Manager Subscription Last Day of Month

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.

Data-Driven Subscription Fails but Report Runs Manually

In a continuation of my last blog post on Finding Report Subscription Errors, there was a tricky little reason why the subscription was failing for some of the parameter values, even though the reports could all be run manually. Just like the Current User Filter in SharePoint, it is CASE SENSITIVE.

I got this error in the trace log:

library!WindowsService_113!308!05/29/2012-10:31:17:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: , Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter ‘Manager’ is not a valid value.;

But all of the values being fed to my subscription were valid values which worked when manually running the report. Why did some of the reports in the subscription render successfully and others not? It turns out the underlying query in SSRS for the Manager parameter dropdown had a mix of either lowercase or the first two letters capitalized. The query I was using in my data-driven subscription was all lowercase. So only those in the report dropdown which were lowercase were running successfully.

I solved the problem by changing the underlying query feeding the SSRS Report Parameter to lowercase, and ensuring that my subscription query was also lowercase. This resolved all the errors.

Finding Report Subscription Errors

I had an issue trying to find what was causing a data-driven report subscription error. The SSRS report is deployed to SharePoint, and Reporting Services is in SharePoint Integrated mode. The subscription was showing last results as “Done: 15 processed of 15 total; 7 errors.” It took awhile to find the pieces I needed to figure out what was causing the error.

I found some information about looking at Report History in SharePoint and creating a New Snapshot, which would give me the last known error. However, since some of the reports had run successfully this did not work.

Next I tried looking at the Report Server database in the ExecutionLog tables. I isolated the query results to just the one report subscription by writing a query like this:

Use ReportServer
select * from ExecutionLog3
where RequestType = ‘Subscription’ AND timeStart >’2012-05-29 11:30:00.000′
ORDER BY TimeStart DESC

This only returned the successes, not the failures.

Finally, I tried looking in the Report Server Trace Log file. There was very little in the log file and nothing to do with my subscription. I knew that the Trace Log file should hold the information I needed. After much poking around I realized that I had made a fundamental error in my assumption about the architecture. The Trace Log file resides on the SharePoint server, not the Report Services database server.

Find the Trace Log

The Trace log files can be found on the SharePoint server, usually here: C:Microsoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesLogFiles

Find the ScheduleID for your Subscription

Find the Schedule ID of the most recent subscription by querying the ReportServer database:

select
‘SubnDesc’ = s.Description,
‘SubnOwner’ = us.UserName,
‘LastStatus’ = s.LastStatus,
‘LastRun’ = s.LastRunTime,
‘ReportPath’ = c.Path,
‘ReportModifiedBy’ = uc.UserName,
‘ScheduleId’ = rs.ScheduleId,
‘SubscriptionId’ = s.SubscriptionID
from ReportServer.dbo.Subscriptions s
join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
join ReportServer.dbo.Users uc on uc.UserID = c.ModifiedByID
join ReportServer.dbo.Users us on us.UserID = s.OwnerId

Search for the ScheduleID in your Trace Log and find the Error message

Open the appropriate log file based on the time stamp being the most recent after the subscription ran, and search for the ScheduleID. Once you find the first entry for your ScheduleID, look for anything that starts with e ERROR

The error message can look like this:

Credits

In preparing this post, I found the following articles to be useful:

http://blogs.msdn.com/b/deanka/archive/2010/02/16/troubleshooting-subscriptions-part-ii-using-the-report-services-trace-log-file.aspx