Sunil Reddy Enugala

…..is 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.

image

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 c.name,   

   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.

image 

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.

                       image

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.

image

Click on Drop down button of the Report to which we need to create subscription and select Subscribe option.

image

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.

image

Specify the time and day of current week and select radio button to repeat the schedule.Click OK button.

image

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.

image

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.

image

Please go through the below figure of shared path folder.

image

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.

image

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.

image 

Specify the schedule time and click on OK button.

image

Check Use Default option to pass default value as the parameter to the report. Click on OK button to complete the Subscription configuration.

image

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.

image

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.

image

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   

   2:  

   3:   ,C.NAME AS ReportName   

   4:  

   5:   ,C.[Path] AS ReportPath   

   6:  

   7:   ,S.SubscriptionID   

   8:  

   9:   ,SUBSTRING(S.DeliveryExtension   

  10:  

  11:            , 14   

  12:  

  13:            , len(S.DeliveryExtension))    

  14:  

  15:            AS DeleveryType   

  16:  

  17:   ,S.[Description] AS Subscription_Desc  

  18:  

  19:   ,S.LastStatus AS Subscription_Exec_Status  

  20:  

  21:   ,C.CreationDate  

  22:  

  23:   ,C.ModifiedDate  

  24:  

  25:   ,S.LastRunTime  

  26:  

  27: FROM dbo.[Catalog] C  

  28:  

  29: INNER JOIN dbo.Subscriptions S   

  30:  

  31: ON S.Report_OID = C.ItemID  

  32:  

  33: INNER JOIN dbo.ReportSchedule R   

  34:  

  35: ON R.SubscriptionID = S.SubscriptionID  

  36:  

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

  38:  

  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

About these ads

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

    Hello,

    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:

WordPress.com Logo

You are commenting using your WordPress.com 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

 
Follow

Get every new post delivered to your Inbox.

Join 654 other followers

%d bloggers like this: