Thursday, October 18, 2012

SSRS- Implementation of File Share Subscription for a Report


1.     Background

The purpose of this article is to describe how to create a file share subscription for a report using SQL Server Reporting services. By using File share subscription, we can deliver a report to a folder at a specific point of time in a required format.       

2.     What is Subscription

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. Subscriptions can be used to schedule and automate the delivery of a report.

3.     Delivery Extensions provided by SSRS


Delivery extensions are used to determine how to deliver a report and in which format. SQL Server Reporting services support two types of delivery extensions.
·     File Share Delivery : To deliver report to a folder
·     Email Delivery : To deliver a report  to email recipient

4.     Pre-requisite for File Share Subscription


· SQL Server Agent Service should run.
· Service account that is using for file share subscription should have write access to shared folder.
· Users who will use these reports must have read only access to the shared folder.

5.   Step by Step procedure to implement file share subscription:


                      i.      Go to Report Manager URL e.g  (http://vishal-pc/reports ). Below is the screenshot of Report Manager which is showing Data Source, Dataset and Report folder.


                    ii.            Go to Report folder e.g “MyReport” and see what the reports available are. We can see one report named “EmployeeDetailReport”



                     iii.            Run the report to ensure that report is working fine.

Report is working fine so we can go ahead for subscription.
                        iv.  For File Share Subscription, go to Report Manager URLè Report Folderè Select the Report that need to subscribeè click on drop buttonèSelect Subscription


                         v.  It will open the Subscription page for the report. Select the Delivery By as “Windows File Share”.

                        vi. Fill the information for below mentioned fields required for subscription.

·         Path : Shared folder where reports need to deliver

·         Render format: Select the format in which report needs to be delivered. Below are the formats in which we can render a report:



·         Credentials used to access the file share: Pass the UserName and password of a user who will access the reports.
·         Based on requirement, we can select overwrite option
·         Subscription Processing option: for Selecting the schedule for subscription.
                        vii.   We are using the below settings for the subscription:



                      viii. Click ok to create subscription.

                      ix.  To Check whether subscription is created or not, go to Report Manager home page and click on “My Subscription”.

                          x. Subscription page will show the newly created subscription:

                         xi. We have created subscription to run every minute for demo purpose only. We can schedule the report as per requirement. So let’s see whether subscription worked successfully or not. To check whether a subscription working successfully or not, again click on “My Subscription”. It will show the subscription execution details.

Subscription status: Report is delivered in Pdf format in shared folder.

                       xii. Go to shared folder and check whether report is delivered or not

                         xiii. Open the pdf file and check whether report delivered properly or not.

  

6.     Conclusion


By using file share subscription, we can deliver SSRS report in folder at a specific point of time in a required format.
----------------------------------------------------End of Document---------------------------------------------------

No comments:

Post a Comment