SSRS: Scheduling Reports Using SQL Server Reporting Services 2008

This blog post gives detailed step-by-step instructions on how to schedule reports to run using SQL Server Reporting Services 2008 Standard Edition. Additional features are available in the Enterprise Edition and these will be covered separately.


Prerequisites
The example Reporting Services project used throughout this guide (put together using Bussiness Intelliegnece Development Studio) is available for download here. You might want to download and unzip this file, load it into the Business Intelignence Development Studio and deploy the reports to your server before attempting to continue with this step-by-step guide.

Accessing Reporting Services
In order to access the Self Service interface for reporting services you need to point your web browser to the page:

https:///reports

You will then see a list of folders for the reports available on that server and the "Data Sources" folder which contains shared data sources (that we won't be using during this demo).

Click on "GoogleKnolSSRSProject".

You can now see three reports; ComplexParameters, SimpleParameter, and NoParameter.

Scheduling A Report With No Parameters
To start things out simply we'll begin by scheduling the "NoParameter" report. Click on it.

This is the report itself (it simply displays a label showing information about this report). Click on the "New Subscription" button at the top left.

This screen allows you to create a new Subscription for this report. As you can see you have to specify two things; the report delivery options (how you want to receive it), and the schedule for delivering the report to you.

The delivery options avaialble to you will depend on how the SQL Server you are using has been setup. The default (if available) is via email but I'll take you through both email and delivery to a fileshare (which is sometimes more convienient if you're looking at something that is accessed by multiple people for example).

Deploying the Report via E-Mail
Select "E-Mail" in the drop down:

The screen will change to show you options available for an email. These are all pretty self-explanatory so I don't intend to go through them one at a time. For the purposes of this example I'm going to be outputting to report into Excel so change the "Render Format" from MHTML (web archive) to "Excel".

It's worth noting that the version of this screen you see is dependant on the permissions you have on the server. If you are a "normal user" then you get the screen shown above. If you have extra permisisons you will see extra fields (i.e. CC, BCC, Reply-To, Comment, and the "To" field becomes editable so you can send the subscription to multiple users).

Now that what we want to do is defined click "Select Schedule":

This new screen presents the options you have in the scheduler. For the purposes of this test I'm going to select this report to run on weekdays a few minutes in the future (just so it runs while I'm writing this!).

At the bottom of this screen is an option to have a start/end schedule for the report:

I won't be using this but feel free to experiement.

Once you've selected the schedule you want click "OK".

You are now presented with the Delivery Options and Schedule you've created. Click "OK" to start the subscription.

After the subscription time has passed an email will arrive with the report:

Deploying the Report via a File Share
Select "Windows File Share" in the "Delivered by:" drop down:

Pick a schedule and click "OK".

NOTE: SQL Server needs to be able to "see" the share to which you're trying to write the file. If it can't (for whatever reason) you will see a message like:

You will need to contact the person reponsible for Administering your server in order to get these permissions problems resolved (especially if you are trying to write this to a "private" share).

Other Deployment Options
Other options are available depending on how your SQL Server is configured (for example if your SQL Server is running in SharePoint integrated mode).

Scheduling a Report with a Simple Parameter
Return to the report selection screen:

Select the "SimpleParameter" report:

Unlike the pervious report you're now being prompted for a parameter before the report is executed. Enter the value "5" and then click "View Report". As you can see this report is pretty similar to the previous one except that it displays the value of the parameter passed to it. Click on "New Subscription" at the top:

This screen is exactly the same as the previous screen so I don't intend going over the Report Delivery Options (top) and the Subscription Processing Options (middle) again. The new section at the bottom is "Report Parameter Values". This section allows you to specify parameters than should be used to run the report.

Enter the value "7" and setup an appropirate schedule and then click "OK".

At the scheduled time the report will be processed and you will receive an email:

You'll notice that leaving the Render format set to "MHTML (web archive)" gives you a nice email in outlook that you can see without having to open an attachment (which can be a problem on certain mobile devices).

Scheduling Reports with Complex Parameters
Return to the report selection screen:

Select the "ComplexParameters" report:

As you can see this report takes four parameters; Day and Time and both pciked from a list (day has a default value, time does not), and Attendees and Guests are simple paremeters (attendees has a default, guests does not).

Pick the time "08:00-11:59" and enter "2" for guests and click "View Report":

Again this report simply re-displays the values of the parameters passed into it. Click on "New Subscription":

The "Report Parameter Values" section at the bottom of the Subscription screen allows you to use the drop downs to pick values for Day and Time OR, in the case of Day, to check the "Use Default" checkbox.

Check the two "Use Default" checkboxes (for Day and Attendees) pick the time 08:00-11:59 and enter "1" for Guests set the Report Delivery Options and pick a schedule and click "OK".

After a few minutes an email will arrive:

The advantages of using Default parameters for scheduling is that, for example, if you are running monthly reports for a full month then if the Default value for the parameter is "last month" then by selecting to use the default when the value of "last month" (i.e. Jan > Feb > Mar > etc) changes the report will automatically pick up the change. If, in February, you ran the report and specified January when the report ran again in March it would just run again for January.