Click here to Skip to main content
15,867,686 members
Articles / Operating Systems / Windows
Article

Distributing Reports Through Subscriptions (SQL Server 2005 RS)

Rate me:
Please Sign up or sign in to vote.
3.63/5 (6 votes)
20 Apr 2007CPOL4 min read 96.2K   13   11
Describes subscription and delivery processes used to distribute reports

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

Screenshot - 1.jpg

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

Screenshot - 2.jpg

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

Screenshot - 4.jpg

  • 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

Screenshot - 5.jpg

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

Screenshot - 6.jpg

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.

Screenshot - 3.jpg

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Australia Australia
Sunil Pawar has completed his Bachelor of Engineering (Information Technology)in 2004 from India.
He is a Microsoft Certified Professional (C# .Net).Sunil started Programming with VB 6.0,ASP and now working with ASP.NET(C#/VB.NET),SQL Server 2008/2012 and Reporting Services.

His hobbies include digital photography and watching documentary TV channel.

Comments and Discussions

 
SuggestionSSRS Report Distribution Pin
itmanager881516-Dec-14 13:21
itmanager881516-Dec-14 13:21 
QuestionExport data from sql server to excel Pin
prabusuccess4-Oct-12 2:21
prabusuccess4-Oct-12 2:21 
GeneralLooking for a way to export dozens of client reports to Excel or Pdf format daily Pin
clivej15-Jan-10 10:52
clivej15-Jan-10 10:52 
GeneralProgramming Reporting Service Subscription Pin
omlac26-Feb-09 23:40
omlac26-Feb-09 23:40 
QuestionSubscribed report seems like it's only executed when I click on the emailed url Pin
M5Pena11-Aug-08 7:54
M5Pena11-Aug-08 7:54 
QuestionFile Name format in Excel Pin
lakshmi817-Jul-08 0:35
lakshmi817-Jul-08 0:35 
How to add system date with file name?
For Example,Flie Name is "UserwiseReport" means,it automatically has to get updated with the Current Date as "UserwiseReport01-07-2008"
Pls tell me the solution.
GeneralSetting extension propert for new Subscription Pin
Rakesh Desai25-Sep-07 5:35
Rakesh Desai25-Sep-07 5:35 
QuestionProblems Scheduling Reports Pin
RoyAF15-Jun-07 5:03
RoyAF15-Jun-07 5:03 
AnswerRe: Problems Scheduling Reports Pin
Sunil_Pawar16-Jun-07 3:39
professionalSunil_Pawar16-Jun-07 3:39 
QuestionGr8 Article... Pin
samir4118025-Apr-07 0:11
samir4118025-Apr-07 0:11 
GeneralGood article! Pin
babalao21-Apr-07 12:09
babalao21-Apr-07 12:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.