Introduction
If you publish reports you will come across these question:
- How do I generate these Reports automatically?
- How do I distribute a large number of reports?
The answer is, Microsoft SQL Server Reporting Services allows you to give out reports using the concept of subscriptions.
There are two ways to deliver reports which Reporting Services provides.
You can send reports through e-mail, or deliver reports to a file shared on the file system.
When you create a subscription, you specify which delivery mode to use.
In this article I am going to explain delivering a report to the file system.
Before you start with subscriptions, make sure your client's server (Hosting Server) is having this (subscriptions) Reporting feature.
For more help on this topic please refer to
this MSDN article.
Subscription Overview
A subscription is a standing request to deliver a report at a specific time or in response to an event, and then to have that report presented in a way that you define.
Subscriptions provide an alternative to running a report on demand.
On-demand reporting requires that you actively select the report each time you want to view the report.
In contrast, subscriptions can be used to schedule and then automate the delivery of a report.
Standard and Data-Driven Subscriptions
Standard subscriptions are created and managed by individual users.
A standard subscription consists of static values that cannot be varied during subscription processing.
For each standard subscription, there is exactly one set of report presentation options, delivery options, and report parameters.
Data-driven subscriptions are dynamic in that the presentation, delivery,
and parameter values are retrieved at run time from a data source.
You might use data-driven subscriptions if you have a very large recipient list or if you want to vary report output for each recipient.
File Share, E-Mail, and Custom Delivery
When a user creates a subscription, he or she can choose one of the available delivery formats to determine how the report is delivered.
Reporting Services includes support for e-mail delivery and delivery to a file share.
Getting Started
Creating, Modifying, and Deleting Standard Subscriptions (Management Studio)
A standard subscription is one that is created by individual users who want to have a report delivered through
e-mail or to a shared folder. A standard subscription is always defined through the report on which it is based.
Step 1: Start with Management Studio
Select the Server type to Reporting Services. I am using Windows authentication throughout this article.
In this way you will get connected to SQL Server's Management Studio. You can also use Report Manager for the same purpose.
Step 2: Expand Tree View
I have created "AdventureWorksReports". This is a Report project and to be deployed on your server. You are able to see that in tree view under Home Node.
You can also see various child nodes under AdventureWorksReports. These are: Datasource, History Subscriptions (the important one).
Now all you need to do is right-click on Subscriptions and go for "New Subscriptions"
Step 3: New Subscription
- Select Report Server File Share from the Notify by list box.
- Click in the text box after File name, and type a file name for the report.
- If you want the file extension automatically appended to the report file name, select True from the File Extension list box. Otherwise, select False.
- In the Path text box, type the path of the file share that contains the report.
- Select a format from the Render Format list box.
- In the User name and Password text boxes, type a user name and password. This would be a Windows password in case of Windows authentication
- Select a mode from the Write mode list box.
- Click OK.
Step 4: Scheduling
On this screen you will able to schedule report.
- Click Scheduling in the Select a page area on the left.
- To use an existing shared schedule, select On a shared schedule, choose the preferred schedule from the list, and click OK.
- To design a schedule, select On a custom schedule. To specify a repeated schedule, click Hour, Day, Week, or Month. Additional options are displayed. Specify values as appropriate, and click OK. To create a schedule that runs only once, select Once, and click OK.
- Optionally specify a later start date by clicking the drop-down arrow for Begin running this schedule on, and select a date from the calendar.
- Optionally select a date to end the schedule. The schedule stops running on this date but is not deleted.
- Click OK.
Parameters
If your report has parameters you will see that the Page contains one more option for parameters. You can able to set the parameters default values here.
Important
When you right-click on the Subscriptions node, and if you are not able to select "New Subscriptions..." then Check for datasource.
Now refer to Fig 2. and right-click on DataSource [datasourceadworks] and select Properties.
Here is what you will get.
Let me explain: to get Subscriptions we have to store credentials on the server. This would be your Windows user name and password.
Once you perform this step you will see "New Subscriptions..." option will become enabled.
Future Work
I will soon be adding Data-Driven Subscriptions in this article.
If anyone has problems in creating Subscriptions, please let me know.
