SSRS: Scheduling A Report With 2008R2 (In SharePoint Integrated Mode)

In an ideal world you'd go to a report, enter your parameters, run the report, and a few seconds later you'd have all your data in the format you need. Unfortunately whilst going to the report and entering the parameters are (usually!) easy, the "few seconds" can quickly stretch to "a few minutes" and beyond before you get your data - particularly with some of the more complex GL-based reports.

As most reporting is clyclical (month end, year end, financial year, monthly sales, weekly orders, etc) it's possible to predict the reports you'll need for a particular period and it's possible, using Reporting Services and SharePoint to scheduled the reports to run when you know you'll need them.

The first step is to go to your report;

Simple SSRS Reporting Showing GL Segments

At the top-left of the web page is a "Actions" drop down. Select this and choose the "Subscribe" option. If you don't have a subscribe option then you probably need to contact whoever administers your server to see why not; there really isn't any reason why users who run reports shouldn't be able to schedule them to run out-of-office hours.

When you select "Subscribe" you'll be directed to the following web page to select the options for the report;

SSRS Subscription Properties

This page allows you to choose how you want to receive the report. By email is probably the simplest way (and is the default). The available options are;

• Email (default): You can choose to have a report sent to any email address (for example a distribution list) but you should already remember that only the person who sets up the Subscription will be able to edit it and if they leave and their Active Directory (AD) account is disabled the subscription will automatically stop
• Windows File Share: Select this to choose to have the report sent to a Windows File Share. You can specify your username and password to grant access. Useful under certain circumstances, but I'd recommend switching to a ...
• SharePoint Document Library: We like this one. Again it requires some setting up by your system administrator but once it's there then you get all the nice features of SharePoint (sharing, versioning, security, etc) without the permission problems of Windows File Shares. This is our preferred option for in-house reporting
• Null Delivery Provider: Sometimes it's necessary just to run the report without worrying about the results (triggering caching automatically for example). If that's the case then this is the provider to select.

Once you've picked the "Delivery Extension" (how you want the report delivered) then the next steps are to expand on that by selecting the following;

• Output Format/ Report Contents/ Render Format (Depends on Delivery Extension selected): Here you can select the format you want the report to be saved in. You can (in Sharepoint 2007 with SQL SSRS 2008R2) only select a single format which means if you want the report in multiple formats (for example PDF for archiving, and Excel for working with) you need to create multiple schedules (if you'd like to show Microsoft you'd like to see this changed you can vote for the item on Connect - see here)
• Delivery Event: You can select either; a) When a report snapshot is created, b) On a shared schedule, or c) on a Custom Schedule. You need to decide when you want the report and whether it's important or not if it runs at the *exact* time specified or within a few minutes/ hours. If it absolutely has to run at a specific time then select Custom Schedule 

"Delivery Event" Subscription Options*

• Parameters: Here you select the parameters for your report. Here you'll definitely need the help of the report writers who need to have written the report with scheduling in mind in order for it to work. For example if you have a report which has a Start and End Date then the default values need to be default to, for example, the start and end of last month so that when it's scheduled to run on the 1st of every month the user can just select "defaults" rather than having to hard-code values. With a bit of planning this can work really well (NOTE: The list of parameters includes *all* parameters for the report including ones developers have marked as Hidden - I've raised this as a bug with Microsoft click here)

Now that you've selected all the options click "OK" (at the top) to create the subscription. It won't run immediately but when the Delivery Event comes around it will try, remember to check back for errors!

*- you'll notice, if you squint really hard at the "Shared Schedule" option that we (OK, I) made a mistake when setting these up. The point of shared schedules should be to increase the flexibility of system administrators to juggle the jobs around but make sure they are still available when needed. "Monthly (1st) @ 7am)" isn't exactly flexible, what I should have created is "Monthly (1st) before 8:30" so I'd be able to move the reports around as and when.