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---------------------------------------------------

3 comments:

  1. SQL Server Reporting Services comes with many features to visualize your business data. In term of Report delivery, SSRS supports both on-demand (pull) and event-based (push) delivery of reports where users can view reports in a Web-based format or in e-mail. The event based delivery of reports is known as Report Subscriptions in SQL Reporting Services which provides a way to us to configure the automatic delivery of specified reports by e-mail or to a file share at scheduled intervals.
    configure Report Subscriptions in SQL Reporting Services

    ReplyDelete
  2. "Subscriptions cannot be created because the credentials used to run the report are not stored or if a linked report, the link is no longer valid" is the very generic error in SQL Server Reporting Services. This error occurs because SSRS requires a Login to connect to the data source to process the report whenever subscription will be occurring at its scheduled time.
    Subscriptions cannot be created because the credentials used to run the report are not stored or if a linked report, the link is no longer valid

    ReplyDelete
  3. We know that SSRS supports both on-demand (pull) and event-based (push) delivery of reports where event based delivery of reports is known as Report Subscription. We can easily configure the automatic delivery of specified reports by e-mail or to a file share at scheduled intervals. In this tutorial, we will learn how to subscribe a report at shared file location or directory.
    File Share Delivery Subscription in SSRS 2016

    ReplyDelete