Subscriptions are a great feature in Reporting Services that will run a report unattended and deliver it to users either by email or Windows File Share. Subscriptions can also be scheduled so if your end users need a report monthly you can automate the delivery process. The steps given in this article will show you how to configure subscriptions using the Windows File Share delivery method.Before you start to configure your first subscription make sure to have the following taken care of first:Start the SQL Server AgentWhen you installed Reporting Services it also created the ReportServer database. This database stores all subscription related data among other things. Ensure the SQL Server Agent is running on the database engine that has the ReportServer database. To start the SQL Server Agent you can either login to the database engine in SQL Server Management Studio or open the SQL Server Configuration Manager then right-click and select start on the SQL Server Agent.
Subscriptions are a great feature in Reporting Services that will run a report unattended and deliver it to users either by email or Windows File Share. Subscriptions can also be scheduled so if your end users need a report monthly you can automate the delivery process. The steps given in this article will show you how to configure subscriptions using the Windows File Share delivery method.
Before you start to configure your first subscription make sure to have the following taken care of first:
Start the SQL Server Agent
When you installed Reporting Services it also created the ReportServer database. This database stores all subscription related data among other things. Ensure the SQL Server Agent is running on the database engine that has the ReportServer database.
To start the SQL Server Agent you can either login to the database engine in SQL Server Management Studio or open the SQL Server Configuration Manager then right-click and select start on the SQL Server Agent.
SQL Server Configuration Manager
SQL Server Management Studio
Enabling sharing on folder delivering toIf you have decided to deliver reports via Windows File Share then you must configure the folder that will accept the reports for sharing. Navigate to the folder that you want to be used with the Subscription and right-click on it then select Properties. Go to the Sharing property tab to enable sharing.
Enabling sharing on folder delivering to
If you have decided to deliver reports via Windows File Share then you must configure the folder that will accept the reports for sharing. Navigate to the folder that you want to be used with the Subscription and right-click on it then select Properties. Go to the Sharing property tab to enable sharing.
Use SQL Server Authentication on data sources used in reportTo enable Subscriptions on a report you must have a SQL Server Authenticated account used to access the data sources. In SQL Server Management Studio, create a new SQL login that has db_datareader access to the databases used in the report data sources.
Use SQL Server Authentication on data sources used in report
To enable Subscriptions on a report you must have a SQL Server Authenticated account used to access the data sources. In SQL Server Management Studio, create a new SQL login that has db_datareader access to the databases used in the report data sources.
After you have created this account change the report data sources to make sure it is being used in the Report Manager.
Now that these prerequisites have been taken care you can actually create a new Subscription. Click on the report that you want to add a Subscription to and click New Subscription in the report toolbar. If you do not see the New Subscription option you may need to have your privileges elevated.
There are several options you have when creating a Subscription:Delivered by – Can either be Windows File Share or Email. If you have not setup your SMTP server to send email then your only option is Window File Share. The SMTP configuration is setup in the Reporting Services Configuration Manager.File Name – What you want the file that is created to be called. Also, there is a checkbox for “Add a file extension when the file is created” which is checked by default. There may be some special circumstances where you do not want the file extension, like your work email does not accept attachments with an .xml extension.Path – The file path where the file should be sent to. It must be written as the full UNC path (\\ServerName\FolderName). For example, my path would be \\Devin-PC\Reports.Render Format – How you want the format to be rendered. Your options are XML file with report data, CSV (comma delimited), Acrobat (PDF) file, HTML 4.0, MHTML (web archive), Excel, RPL renderer, TIFF file, or Word.Credentials used to access the file share – This must be a login that you setup to have access the file share in the eariler prerequisites. Overwrite options – Overwrite an existing file with a newer version is set by default and will always replace a file if they are named the same. Do not overwrite the file if a previous version exists will keep the original file and cause the subscription to fail. Increment file names as newer versions are added will continue to add a new file if one already exists with the same name but will increment it (ReportName_1.pdf, ReportName_2.pdf, ReportName_3.pdf).
There are several options you have when creating a Subscription:
Delivered by – Can either be Windows File Share or Email. If you have not setup your SMTP server to send email then your only option is Window File Share. The SMTP configuration is setup in the Reporting Services Configuration Manager.
File Name – What you want the file that is created to be called. Also, there is a checkbox for “Add a file extension when the file is created” which is checked by default. There may be some special circumstances where you do not want the file extension, like your work email does not accept attachments with an .xml extension.
Path – The file path where the file should be sent to. It must be written as the full UNC path (\\ServerName\FolderName). For example, my path would be \\Devin-PC\Reports.
Render Format – How you want the format to be rendered. Your options are XML file with report data, CSV (comma delimited), Acrobat (PDF) file, HTML 4.0, MHTML (web archive), Excel, RPL renderer, TIFF file, or Word.
Credentials used to access the file share – This must be a login that you setup to have access the file share in the eariler prerequisites.
Overwrite options – Overwrite an existing file with a newer version is set by default and will always replace a file if they are named the same. Do not overwrite the file if a previous version exists will keep the original file and cause the subscription to fail. Increment file names as newer versions are added will continue to add a new file if one already exists with the same name but will increment it (ReportName_1.pdf, ReportName_2.pdf, ReportName_3.pdf).
You can also click Select Schedule to design a set time for the Subsciption to run. You can also configure when you want the schedule to start and how long it should continue. Once you have set the schedule you want click OK.
This will return you to the previous screen where can lastly decide to pass in different values to the report parameters before you finalize the Subsciption by clicking OK. On the Subscription tab of the report you should see listed all the Subscriptions that have been created for this report.
This will return you to the previous screen where can lastly decide to pass in different values to the report parameters before you finalize the Subsciption by clicking OK.
On the Subscription tab of the report you should see listed all the Subscriptions that have been created for this report.
When the Subscription runs you should see the file now in your shared folder. If you open Management Studio and take a look at the SQL Agent jobs you will see there is a new one for the Subscription you created. Here you can run the Subscription manually so you do not have to wait for the scheduled time.
WARNING: Do not change any settings of this job. Even just changing the name could cause the Subscription to fail.