Subscription is used to execute a report on scheduled time and deliver it to the End User as an E-mail attachment or to be dropped in a file share in any specific format (like pdf, doc, etc.,). If the report server is configured for SharePoint integrated mode, you can also send a report to a SharePoint library.
Simply we can say that subscriptions can be used to schedule and then automate the delivery of a report.
SSRS supports two types of subscriptions, Standard and Data Driven Subscription.
Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing.
We cannot use the expression to specify the values for the parameter when creating subscriptions.
Creating Standard Subscription:
I have a report which gives last 24 hours Report Execution log details. Please go through below figure which shows the preview of the report.
Please go through the below script which uses ReportServer database to get last 24 hours Execution log details of Reports .
1: SELECT c.name,
3: WHEN el.RequestType = 0 THEN 'Interactive'
4: WHEN el.RequestType = 1 THEN 'Subscription'
5: WHEN el.RequestType = 2 THEN 'Refresh Cache'
6: END AS RequestType,
10: DATEDIFF(ss,el.TimeStart,el.TimeEnd) AS 'TotalDuration(Sec)',
11: (el.TimeDataRetrieval/1000.00) AS 'Data Retrieval Time(Sec)',
12: (el.Timeprocessing/1000.00) AS 'Processing Time(Sec)',
13: (el.TimeRendering/1000.00) AS 'Rendering Time(Sec)',
15: WHEN el.Source=1 THEN 'LIVE'
16: WHEN el.Source=2 THEN 'Cache'
17: WHEN el.Source=3 THEN 'Snapshot'
18: WHEN el.Source=4 THEN 'History'
19: WHEN el.Source=5 THEN 'Ad hoc(Report Builder)'
20: WHEN el.Source=6 THEN 'Session'
21: WHEN el.Source=7 THEN
22: 'Report Definition Customization Extension(RDCE)'
23: END AS 'Source',
25: el.ByteCount/1024.00 AS 'Size(Kb)',
26: el.[RowCount] AS 'Number of Records'
27: FROM ReportServer..ExecutionLogStorage EL
28: INNER JOIN ReportServer..Catalog c ON c.itemid=el.reportid
29: WHERE TimeStart>= @Date
30: ORDER BY TimeStart DESC
I have @Date parameter in the above script and configured default value(which gives yesterday’s date as a value) as shown in the below figure.
The first thing while creating subscription is to add Credentials for the DataSource of the Report.
Open Report Manager and Click on the Data Source(ReportServerDB) of the
report(ReportsExecutionLog) to which we need to create subscription.
Check Credentials stored securely in the report server option and specify windows credentials. also select the checkbox use as Windows credentials when connecting to the data source. Click on Test Connection button to check the connection. Now click on Apply button.
Click on Drop down button of the Report to which we need to create subscription and select Subscribe option.
On the Subscription page, select the delivery option as E-Mail/Windows File Share.
Windows File Share Delivery
Select Windows File Share as delivery option and create a shared folder and specify in the path tab. Specify the credentials to access the file and click on Select Schedule to automate the Report Execution.
Specify the time and day of current week and select radio button to repeat the schedule.Click OK button.
Now we have scheduled the report, Click on check box Use Default option as we can’t pass value to the parameter of the Report. Click OK button.
Subscription is created and is scheduled to 03:03PM, check the status of the subscription in the below figure. It has generated pdf file of the report and placed in the mentioned shared folder path.
Please go through the below figure of shared path folder.
Again click on the drop down button of the Report. Select Subscribe option.
Note: E-Mail option will be available only after setting mail configuration in the Reporting Services Configuration Manager. Please go through the below figure to know the process.
After setting the Sender Address and SMTP Server property values, click on Apply button to complete the mail configuration.
Select the Delivery by Option as Email. Specify the Render Format and click on Select Schedule button to automate the report execution.
Specify the schedule time and click on OK button.
Check Use Default option to pass default value as the parameter to the report. Click on OK button to complete the Subscription configuration.
Subscription is created and is scheduled at 4:10PM. Check the status of the subscription in the below figure. Mail was sent to given E-mail ID.
Now we have created Standard Subscriptions in two possible way. Once the Subscription is created for a Report, you’ll find a job under Jobs folder in the SQL Server Management Studio as shown in the below figure.
As we have created two subscription, we have seen two jobs in the above figure.
To know the details of subscription, please run the below script in ReportServer database.
1: SELECT R.ScheduleID AS JobName
2: ,C.NAME AS ReportName
3: ,C.[Path] AS ReportPath
6: , 14
7: , len(S.DeliveryExtension))
8: AS DeleveryType
9: ,S.[Description] AS Subscription_Desc
10: ,S.LastStatus AS Subscription_Exec_Status
14: FROM dbo.[Catalog] C
15: INNER JOIN dbo.Subscriptions S
16: ON S.Report_OID = C.ItemID
17: INNER JOIN dbo.ReportSchedule R
18: ON R.SubscriptionID = S.SubscriptionID
19: WHERE C.NAME = 'ReportsExecutionLog'
20: --Pass ReportName as parameter
Let me know your comments and queries.
I’ll meet you again with my post on Data Driven Subscription.