Sunil Reddy Enugala

… just walking through MSBI Street.

How to create Standard Subscriptions in SSRS

Posted by Sunil Reddy Enugala on June 11, 2013

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: --@Date variable has datetime value 

   2: DECLARE @Date datetime = DATEADD( DD,-1, CONVERT( CHAR(8) , CURRENT_TIMESTAMP , 112 )) 

   3: SELECT,   

   4: CASE    

   5: WHEN el.RequestType = 0 THEN 'Interactive'   

   6: WHEN el.RequestType = 1 THEN 'Subscription'   

   7: WHEN el.RequestType = 2 THEN 'Refresh Cache'   

   8: END AS RequestType,    

   9: el.Format,   

  10: el.TimeStart,   

  11: el.TimeEnd,  

  12:  DATEDIFF(ss,el.TimeStart,el.TimeEnd) AS 'TotalDuration(Sec)',  

  13:  (el.TimeDataRetrieval/1000.00) AS 'Data Retrieval Time(Sec)',  

  14:  (el.Timeprocessing/1000.00) AS 'Processing Time(Sec)',  

  15:  (el.TimeRendering/1000.00) AS 'Rendering Time(Sec)',  

  16:  CASE   

  17:  WHEN el.Source=1 THEN 'LIVE'  

  18:  WHEN el.Source=2 THEN 'Cache'  

  19:  WHEN el.Source=3 THEN 'Snapshot'  

  20:  WHEN el.Source=4 THEN 'History'  

  21:  WHEN el.Source=5 THEN 'Ad hoc(Report Builder)'  

  22:  WHEN el.Source=6 THEN 'Session'  

  23:  WHEN el.Source=7 THEN   

  24:              'Report Definition Customization Extension(RDCE)'  

  25:  END AS 'Source',  

  26:  el.status,  

  27:  el.ByteCount/1024.00 AS 'Size(Kb)',  

  28:  el.[RowCount] AS 'Number of Records'  

  29:  FROM ReportServer..ExecutionLogStorage EL   

  30:  INNER JOIN ReportServer..Catalog c ON c.itemid=el.reportid  

  31:  WHERE TimeStart>= @Date

  32:  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.


E-Mail option

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 exists Subscription , please run the below script using ReportServer database.


   1: SELECT R.ScheduleID AS JobName   


   3:   ,C.NAME AS ReportName   


   5:   ,C.[Path] AS ReportPath   


   7:   ,S.SubscriptionID   


   9:   ,SUBSTRING(S.DeliveryExtension   


  11:            , 14   


  13:            , len(S.DeliveryExtension))    


  15:            AS DeleveryType   


  17:   ,S.[Description] AS Subscription_Desc  


  19:   ,S.LastStatus AS Subscription_Exec_Status  


  21:   ,C.CreationDate  


  23:   ,C.ModifiedDate  


  25:   ,S.LastRunTime  


  27: FROM dbo.[Catalog] C  


  29: INNER JOIN dbo.Subscriptions S   


  31: ON S.Report_OID = C.ItemID  


  33: INNER JOIN dbo.ReportSchedule R   


  35: ON R.SubscriptionID = S.SubscriptionID  


  37: --WHERE C.NAME = 'ReportsExecutionLog'   


  39: --Pass Report Name as C.NAME

Let me know your comments and queries.

I’ll meet you again with my post on Data Driven Subscription.Smile

8 Responses to “How to create Standard Subscriptions in SSRS”

  1. […] How to create Standard Subscriptions in SSRS. […]

  2. […] Hi, In my last post we have discussed regarding Subscription in SSRS and also a small example of creating  Standard Subscription for SSRS Report. […]

  3. […] discuss regarding another approach of Executing SSRS Report. This approach will use Subscriptions(Standard/Data-driven) of […]

  4. arnaudref said


    Your script is wrong. You forget some letters with your copy and paste.
    For example : ‘ 14: ROM dbo.[Catalog] C ‘

    • Hey thanks for going through my post and letting me know about the typo. Yes, the first letters of some statements where missing during insertion of code through plug-in. I have corrected now.
      Thanks again.

  5. arnaudref said

    You’re welcome.

    Thanks for your article ;)

  6. Dhana said

    Hi Sunil Reddy, thanks for article. I have few doubts can you please clarify..thanks

Leave a Reply...

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 664 other followers

%d bloggers like this: