SQL Server Reporting Services subscriptions are an easy way to automatically run and deliver SSRS reports. You can schedule reports to run at a regular interval and deliver to file share, e-mail, or a SharePoint document library (if you’re using SharePoint integration mode).

How it’s done:

Here is my SSRS 2008 R2 instance, with a couple of prepared reports:

I’m going to create a subscription for SSRS_example_multivalue, which has a parameter with multiple values to select from.

STOP!

Before you run away with the subscription, you need to make sure that SQL Server Agent is running. Chances are your SQL Server Agent isn’t running, so you won’t be able to run any subscriptions. You can start SQL Server Agent through SQL Server Configuration Manager:

Microsoft has steps on how to start SQL Server Agent if you’re curious.

Starting on the subscription

Right-click on the report that you want to add the subscription to. Select Manage.

Next, go to Subscriptions. When the subscription list loads, if the button to add a New Subscription has a warning icon, you won’t be able to add a subscription just yet.

Regular reports that use databases for the data sources need to have the credentials stored with the report. You will get an error message if you try to add a subscription. So let’s see how to fix that issue.

Modifying the data source

Go to Data Sources.

In my example, I’m using the AdventureWorks database, which is normally configured to use Windows integrated security. I’m going to switch to Credentials stored securely in the report server:

After adding the credentials, make sure to test your connection. I had to check-off Use as Windows credentials when connecting to the data source, but then I was able to connect to the database.

Click Apply when you’re done.

Going back to the subscription

Now New Subscription should be free of the warning icon.

You may notice that there are two types of subscriptions.

Data-drive subscriptions are available in the Evaluation, Developer, or Enterprise editions of SSRS.  They provide more dynamic functionality at run time, such as picking the report recipients based on a query. Jes Borland (@grrl_geek) breaks down how to do data-driven subscriptions to a file share and e-mail in a couple of blog posts.

For this example, I will focus on regular subscriptions.

Click New Subscription. You will be directed to the Report Delivery Options form.

Delivery method

The first thing is to select the delivery method. If you are using SSRS in native mode, you can deliver to a file share or an e-mail account. You should note that e-mail delivery requires additional configuration. For simplicity, I’ll use the file share for my example.

Select Windows File Share and specify the path to the shared folder.

It is important to note that the account that is running the report (see below) has WRITE permissions to the shared folder.

Render Format

All the rendering extensions from the SSRS installation are available as export options. For this particular report, I’m going to export to XLS using SSRS’ native Excel export option.

Subscriptions also work with OfficeWriter‘s Excel and Word export options. I’ll briefly cover that later.

Credentials and Output Options

As mentioned above, the account running the report must have WRITE permissions in the shared folder. Additionally, there are some options for writing out the reports. I opted to have unique numbers appended to the reports, to avoid overwriting existing files.

Subscription Schedule

To specify the subscription schedule, click Select Schedule under Subscription Processing Options.

For this example, I created a schedule that runs the report every 2 minutes starting about 10 minutes from the time I created the subscription. Realistically, you would want to schedule the report to run once a week or month etc.

Report Parameter Values

Because this isn’t a data-driven subscription, I have to hard-code which values to use when running the report.

Click OK when you’re done.

Congrats! You’re done!

You should see your new subscription in the subscription list:

Subscriptions with OfficeWriter

You can also create subscriptions with the OfficeWriter renderers for Excel and Word. When setting up the subscription, just select Excel designed by OfficeWriter or Word designed by OfficeWriter.

Success:

I also created a subscription that uses the OfficeWriter for Excel renderer (ExcelWriter_SSRS_example.xlsx). Now go automate those reports.

Additional Resources

• How to Configure Report Subscriptions in SQL Reporting Services (MSDN)
• Report Subscription in SSRS 2008 R2 (Arshad Ali, Database Journal)

Related posts: