Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted January 18, 2012

Report Subscription in SSRS 2008 R2

By Arshad Ali

Introduction

SQL Server Reporting Services (SSRS) is a server based scalable and extensible platform for authoring, deploying, executing and managing reports based on a variety of data sources. SSRS allows us to create interactive, tabular, graphical (using data visualization controls) or free from reports from relational, multidimensional (using MDX or DMX) or XML data sources. Furthermore it allows you to view/export your reports in varieties of formats. You can enable report caching, which improves the performance for subsequent requests if the parameters remain same.

You can set the subscription for the report in which case report server processes the subscription, generates the reports and delivers it to its intended audience via email delivery or file share delivery on the defined schedule. In this article I am going to explore the standard subscription feature in detail.

Understanding Report Subscription

"In the subscription model, a subscriber subscribes/registers himself with publisher to get the subscription (data, reports, updates etc.) on a scheduled/reoccurring basis."

A report subscription is a report snapshot generated on some defined scheduled time and delivered to the intended audience by the report server. SSRS allows you to create two types of subscriptions, e.g., standard subscriptions and data-driven subscription.

Standard subscription is created by any user having "Manage individual subscriptions" or "View a report" permissions with static values for the parameters. It means whatever parameters the report expects needs to be provided at the time of subscription creation and cannot be changed at runtime. In the case of data-driven subscription (can be created by someone who has "Manage all subscriptions" Permission or Content Manager), data or parameter values required during execution of the report can come from a query (or a database) including recipient list, delivery method and parameter values needed for each recipient's report execution.

SSRS has provided two delivery extensions out of the box; the first one lets SSRS save the report subscription to a file share location whereas the second one sends the report in email to the specified recipients. There is one more extension; if you have SSRS installation in SharePoint Integrated mode, which delivers the report to a SharePoint document library.

In this article, I am going to explore standard subscription in detail; for data driven subscription, refer to my next article.

Getting started with Report Subscription

To setup subscription for a report, go to Report Manager, navigate to the folder that contains the report, point to the report for which you want to set up a subscription, click on the down arrow and click on the Manage menu item as shown below:

Manage Report
Figure 1 - Manage Report

On the manage report page, click on Subscriptions on the left. This will show you two options; the first option (New Subscription) lets you create a standard subscription for the report whereas the second option (New Data-driven Subscription) lets you create a data-driven subscription. I will be talking more about data-driven subscriptions in my next article.

Subscription Page
Figure 2 - Subscription Page

When you click on the New Subscription link on the page shown above, it will bring up a page with two parts. In the first part you can define the report delivery method and in the second part you can specify subscription processing options. The Report Delivery Options section will change depending on the delivery method you choose. For example as you can see below, I have chosen a delivery method of E-Mail and hence it has options to specify TO, CC, BCC, subject line, whether to include the report as link or as an attachment, etc.

Report Delivery Options – Email
Figure 3 - Report Delivery Options – Email

If you change the delivery method to Windows File Share, the fields on screen will change to let you specify the file name, share location, credential to use to copy the file at that location, overwrite options, etc.

Report Delivery Options - File share
Figure 4 - Report Delivery Options - File share

The second part of the screen lets you define the subscription processing options. Broadly speaking, you can specify two settings here. First you define a schedule for the subscription to process and second you specify the parameter values to be used during processing of the reports. Here you can specify to use the default value for a parameter if it has been defined or you can specify a new value. Please note, if you have set report snapshot for your report, you can also specify subscription to run from the snapshot whenever the snapshot is refreshed instead of using a schedule to process the report subscription. Also in this case parameter values from snapshot will be used and you cannot change it.

Subscription Processing Options
Figure 5 - Subscription Processing Options

When defining a schedule for subscription processing, you can choose to use any predefined shared schedule (select one from the combo box) or you can use an embedded/dedicated schedule for this subscription processing. When you click on the Select Schedule button on the above screen, it will bring up a screen as shown below where you can define a schedule for daily, weekly, monthly, yearly processing, etc.

Subscription Schedule
Figure 6 - Subscription Schedule

Once created, you can view the subscription and its execution details in the subscription detail page as shown below. You can click on the Edit hyperlink to modify the subscription or select a subscription and click on the Delete button to delete it.

Subscription detail
Figure 7 - Subscription detail

Report server will generate a report for each enabled subscription on the defined schedule, for example, in my subscription I have specified file share as the delivery method and subscription to run every 2 minutes. The result you can see below; notice that each report has been generated every two minutes.

File share with created subscriptions
Figure 8 - File share with created subscriptions

As I said before, the subscription detail page displays the execution details of the subscription; notice the Last Run and Status columns below:

Subscription details with status
Figure 9 - Subscription details with status

There are a couple of points that you need to be aware of when creating subscription for the reports.

1. As we all know, data sources can be categorized in two categories; data sources from the first category do not have credentials and are used for connecting to Excel or XML, etc. Data sources from the second category need credentials and are used for connecting to SQL Server or other DBMS. If your report uses the first category of data sources (which does not need credentials) then there is no problem but if it uses the data sources from the second category (which need credentials) then all these data sources must have credentials saved with them.

2. If the delivery mode is file share, the user that you specify while creating a subscription must have the appropriate permissions (write access) on the file share location as this is the account that report server uses to connect to file share and put the file there.

3. If the delivery mode is Email, report server will not throw an exception if the report cannot be accepted by the email server because of the huge size of the report. In this case you need to have an exception set up on the email server for your account or can use a report link instead of an attachment, or use file share delivery mode.

4. By default, subscription and delivery extensions (for email you need to do email/SMTP configuration) are enabled; if you want to disable it you can set the ScheduleEventsAndReportDeliveryEnabled property to False in the Surface Area Configuration for Reporting Services facet of SQL Server Policy-Based Management.

5. When creating a standard subscription, you need to either specify to use a default value for the parameter or specify a value (static in this case) to use each time the subscription is processed.

6. Report server generates and delivers reports as per the definition in the subscription at the defined schedule and hence the report is static. It means it’s not interactive and data has been taken when the subscription was processed.

7. You can create multiple standard subscriptions for a single report with varying values of parameters or delivery modes.

8. All the associated subscriptions will be deleted if you delete a report.

9. A report subscription can become inactive if the report (or report snapshot on which subscription is based) is modified; to activate a subscription you can open and then save it.

10. SQL Server Agent must be running for creating and running report subscriptions.

Conclusion

A report subscription is a report snapshot generated on a defined, scheduled time and delivered to the intended audience by the report server. SSRS allows you to create two types of subscriptions, e.g., Standard Subscription and Data Driven Subscription.

In this article I talked about standard subscription in which you need to specify whatever parameters the report expects at the time of subscription creation and which cannot be changed at runtime.

In my next article I am going to talk about Data-driven Subscription in which case data or parameter values required during execution of the report can come from a query from a database, including recipient list, delivery method and parameter values needed for each recipient's report execution.

Resources

Subscription and Delivery (Reporting Services)

Subscriptions Page (Report Manager)

Managing Subscriptions

See all articles by Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM