jump to navigation

Reports subscription in SSRS 2008 October 29, 2009

Posted by msrviking in Configuration.
trackback

I had a request from my clients asking “Can a user subscribe to reports all by his own?”. The quick answer I said “Yes”, and I did a POC to find how it could be done and what are the limitations.

After the POC I decided to document which I shared with the client team. I thought I should share with you all on what it is all about! Although the content isn’t exhaustive but probably could help for a initial startup.

/**************************************************************************************/

Scheduling a report in Report Server (SSRS 2008)

Subscription:

A subscription of a report is an on-demand reporting which could be scheduled and the delivery of the report can be automated. A subscription is processed in the report server and the delivered report can be shared in a folder of a file server of can be sent to email addresses.

A subscribed report uses stored credentials, and the user wanting to create subscription should have permission to view the report and also creating individual subscriptions. As part of configuration at reporting server level, scheduled events and report delivery (e-mail delivery should be configured separately) should be enabled. Additional delivery extensions can also be added by installing developed custom extensions.

Types of 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 get subscription information at run time by querying an external data source that provides values used to specify a recipient, report parameters, or application format. These types of subscriptions are typically created and managed by Report Server administrators.

The limitations of using data-driven subscriptions are as following,

  • Data-driven subscription functionality is not available in Standard Edition.
  • For subscription data, choose a data source that can provide schema information to the report server. The supported data source types include SQL Server, Oracle, Analysis Services databases, SQL Server Integration Services package data, ODBC data sources, and OLE DB data sources.

Permissions for subscriptions:

Users can subscribe to reports through two tasks described as below.

–          The “Manage individual subscriptions” tasks will allow creating, modifying, and deleting subscriptions that are owned by a user for a specific report. This task is part of the Browser and Report builder predefined roles. Any user assigned to these roles having the above task will the user to manage only those subscriptions that are owned.

–          The other task will allows users to access and modify all subscriptions is “Manage all subscriptions”. This task is for data-driven subscriptions and is part of the predefined role – Content Manager.

Creating standard subscription:

A standard subscription can be 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.  A user who creates a subscription owns that subscription. Each user can modify or delete the subscriptions that he or she owns.

Pre-requisites /Limitations of Standard subscription

Requirement 1

Permission to view the report which is chosen for subscription, which would mean the user, should be assigned to the role “Manage individual subscriptions”.

Requirement 2

The report must use stored credentials or no credentials to retrieve data at run time. A report can’t be subscribed which is configured to use the impersonated or delegated credentials of the current user to connect to an external data source. The stored credentials can be a Windows account or a database user account.

Requirement 3

If the model is used as a data source for a report contain security settings then this report can’t be subscribed.

Requirement 4

If the report requires a parameter to run during the processing time then the input parameter should be defined while scheduling the subscription.

Subscription creation

Once the subscription is created, a SQL Agent job with a system id is created with particulars like

–          Job name

–          Job description

–          Job schedule (day, hour, frequency)

–          Job running account – Local Service

–          Transact SQL referring to an event which will trigger the schedule process

When the job runs successfully an e-mail is sent and /or the scheduled report is put in the shared folder of the file server for later access.

Other details:

Subscriptions to reports create specific schedules that are defined through subscription properties, but shared schedules are easier to manage and maintain for the following reasons:

  • Shared schedules can be managed from a central location, making it easier to compare schedule properties, adjust frequencies and recurrence patterns.
  • If shared schedules are used, when scheduled operations occur is precisely known. This makes it easier to anticipate and accommodate server loads before performance issues occur.

/**************************************************************************************/

Let me know what you guys think?

Cheers!

 

Advertisements

Comments»

1. ChooriChikka - November 22, 2009

Thanks for sharing useful info.

2. vinu - September 10, 2010

thanks for the useful info

3. Vikash - December 24, 2010

gr8 info for beginners like me.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: