Thursday, October 4, 2012

Set SSRS 2008 R2 (SQL Server Reporting Server 2008 R2) Subscriptions in SharePoint 2010

Reports can be pulled from the Report Manager by finding the report the user wants to have. The way to deliver reports at a specific time, in a specific format to the user is to use subscriptions. 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.

Report subscriptions are processed on the Report Server. They reach end users through delivery extensions that are deployed on the Report Server. 

E-Mail Delivery in Reporting Services:

Reporting Services includes an e-mail delivery extension that provides a way to e-mail a report to individual users or groups. The e-mail delivery extension is configured through the Reporting Services Configuration Manager tool and by editing the Reporting Services configuration files.

Select the Email Settings--> Sender Address, Current SMTP Delivery Method and SMTP Server.

 The subscription option can be accessible in the Web Part for the Reporting Service "Actions > Subscribe", or accessing in the "Site Actions > Site Settings"

central administrator SSRS

This option will give you different options and Types of Reports you can use to store the report associated for the schedule Reports.

delivery extensions in ssrs and sharepoint 2010

The subscribe option also give you the ability to configure and schedule Tasks for the reports.

schedule ssrs in sharepoint 2010
 This page allows you to choose how you want to receive the report. By email is probably the simplest way (and is the default). The available options are;

  • Email (default): You can choose to have a report sent to any email address (for example a distribution list) but you should already remember that only the person who sets up the Subscription will be able to edit it and if they leave and their Active Directory (AD) account is disabled the subscription will automatically stop
  • Windows File Share: Select this to choose to have the report sent to a Windows File Share. You can specify your username and password to grant access. Useful under certain circumstances, but I'd recommend switching to a ...
  • SharePoint Document Library: We like this one. Again it requires some setting up by your system administrator but once it's there then you get all the nice features of SharePoint (sharing, versioning, security, etc) without the permission problems of Windows File Shares. This is our preferred option for in-house reporting
  • Null Delivery Provider: Sometimes it's necessary just to run the report without worrying about the results (triggering caching automatically for example). If that's the case then this is the provider to select.
Once you've picked the "Delivery Extension" (how you want the report delivered) then the next steps are to expand on that by selecting the following;

Output Format/ Report Contents/ Render Format (Depends on Delivery Extension selected): Here you can select the format you want the report to be saved in. You can (in Sharepoint 2007 with SQL SSRS 2008R2) only select a single format which means if you want the report in multiple formats (for example PDF for archiving, and Excel for working with) you need to create multiple schedules  

Render Format – If you have chosen to include the report, you can pick a format here. The formats you can pick from vary based on your version of Reporting Services.
XML file with report data – the user receives an .xml file as an attachment.
CSV (comma delimited) – the user receives a .csv file as an attachment.
• Acrobat (PDF) file – the user receives a .pdf file as an attachment.
MHTML (web archive) – the user receives the report embedded in the body of the email, rather than as an attachment. (I was really excited when I discovered this!)
Excel – the user receives an .xls file as an attachment.
TIFF file – the user receives a .tiff file as an attachment.
Word – the user receives a .doc file as an attachment. This is new in 2008.

  • Delivery Event: You can select either; a) When a report snapshot is created, b) On a shared schedule, or c) on a Custom Schedule. You need to decide when you want the report and whether it's important or not if it runs at the *exact* time specified or within a few minutes/ hours. If it absolutely has to run at a specific time then select Custom Schedule 
"Delivery Event" Subscription Options*
Parameters: Here you select the parameters for your report. Here you'll definitely need the help of the report writers who need to have written the report with scheduling in mind in order for it to work. For example if you have a report which has a Start and End Date then the default values need to be default to, for example, the start and end of last month so that when it's scheduled to run on the 1st of every month the user can just select "defaults" rather than having to hard-code values.
 Now that you've selected all the options click "OK" (at the top) to create the subscription.
and that's it. You are Done!!
Viral Shah

Total Pageviews