Sunil Reddy Enugala

…..is just walking through MSBI Street.

How to create Data Driven Subscription in SSRS

Posted by Sunil Reddy Enugala on August 26, 2013

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

Today, lets discuss about creating a Data Driven Subscription for a SSRS Report.

I have created a dummy report which gives the Employee Details for a given department. Please go through below dummy report.

image

Now click on the drop down button of the report for which you want to create a Data Driven Subscription and select Manage option

image

Now select the Data Sources tab and give the Connection string and Credentials details as shown in the below figure. Click on Test Connection button to ensure the given details.

image

Go to the Subscriptions tab and click in New Data-driven Subscription button as shown in the below figure.

image

We need to follow 7 steps to create a Data-driven Subscription, please go through below figures to know about those steps.

Step 1:

image

in the above step we need to mention:

  • Description of Data-driven Subscription
  • Type of delivery to the recipients
  • Data Source for the recipients information

There are three types of delivery options available for Data-driven Subscriptions

  1. Windows File Share : Delivers report to a shared folder.
  2. E-mail : Delivers report through E-mail as an attachment or URL link.
  3. Null Delivery Provider : This type of delivery is used to preload a cache with ready-to-view parameterized reports. Mainly to improve report server performance by preloading the cache.

SSRS is not confined to only 3 type of deliveries, you can create custom delivery extensions on which we can discuss through my next post.

In my example I have used Windows File Share as a delivery option and checked specify for this subscription only to declare data source for the recipients information.

Now click on Next button.

Step 2:

image

In the above step we need declare Connection string and Credentials to access data source of the recipients details. After specifying the details click on Next button.

Step 3:

image

Now I have created a table(dbo.ReportInput) with the delivery extension settings and Report parameters as column names. Please go through the below figure.

image

I have inserted details like delivery file name, delivery path, render format, report parameter value etc., into the table.

In the Query tab shown in the above figure, write the query to fetch the delivery setting details including the parameter value of the report. Click on the Validate button to validate the query. after validating successfully click on Next button.

Step 4:

image

The columns which mentioned in the select query of Step 3(Query tab) will be used  in Step 4/Step5. Check the radio button Get the values from the database: to map the respective columns of the ReportInput table.

Now click on the Next button to map the Report Parameter column.

Step 5:

image

Map the Dept_Name column of dbo.ReportInput table to the parameter of the Report as shown in above figure. Now click on Next button to schedule the Data-driven Subscription.

Step 6:

image

Check the option On a schedule created for this subscription  and click on Next button to schedule the subscription.

Step 7:

image

Provide the schedule details as shown in the above figure and click on Finish button to complete the process of configuring Data-driven Subscription with Windows File Share delivery Option.

Go through the below figures to check the status of the scheduled Data-driven Subscription.

image

image

Creating Data-delivery Subscription with Email – Delivery Option:

Everything is same still Step 2 except selecting the delivery option as E-Mail.

Step 3:

image

I have created another table to store Email recipients information, please go through the below figure.

image

Click on Validate button to verify the Query  written in the Query tab. Click on Next button.

Step 4:

image

map the columns of dbo.EmailReportInput table with the options in Step 4.

Click on Next button.

Step 5:

image

map the Dept_Name of dbo.EmailReportInput table with the option in Step 5 and Click on Next button to schedule the Subscription.

Step 6:

image

check On a schedule created for this Subscription and click on Next button.

Step 7:

image

Provide the schedule details and click on Finish button to complete the process of configuring Data-driven Subscription with E-mail delivery option.

Go through the below figures to check the status of the scheduled Data-driven Subscription.

image

Let me know if you have any issues while implementing Data-driven Subscriptions.

Sunil Reddy Enugala

About these ads

11 Responses to “How to create Data Driven Subscription in SSRS”

  1. Manoj Sahoo said

    Thank u for such a lovely post,
    I am Facing one problem. I followed all your steps to create a Email Data driven subscription. But after the scheduled time passed it is showing an error like
    Done: 1 processed of 1 total; 1 errors

    Please Help.
    Thanks
    Manoj

  2. Hi Manoj, Am glad, that you have gone through my post.
    Reporting Service will log the error details in a log file which will be under the following path:
    \Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles\ .

    Can you go through it and let me know error message in it.

    Thanks

  3. Manoj Sahoo said

    Hi SUNIL,
    As per Your instruction i took the Log file from our Remote Server. The Error Message in the Log File as Follows.

    Error sending email. System.Runtime.InteropServices.COMException (0x8004020E): The server rejected the sender address. The server response was: 530 5.7.0 Must issue a STARTTLS command first. uw6sm13867183pbc.8 – gsmtp

    what i will do now ??

    Please help me out.

    Thanks
    Manoj Sahoo

  4. It was the issue with the SMTP server provided in the Report service configuration, try to use different SMTP server or do configuration change that needs to be updated in “RSreportserver.config” file which is located at “C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\RSreportServer.config”

    Open the file and do the following
    * search for ‘SendUsing’ and set it value to 2
    * search for SMTPAuthenticate and set it value to 2

  5. Manoj Sahoo said

    I have changed SendUsing and SMTPAuthenticate value to 2.
    But still it throws the same error.

    Thanks
    Manoj

  6. Manoj,
    Can you let me know about SMTP server. I think the SMTP server which you are using requires Secure Sockets Layer (SSL) / Transport Layer Security (TLS) authentication, which is not supported directly in Reporting Services. The easiest thing to do is to use the SMTP Service in Windows 2003 Server. Configure the service to pick up from a local directory and forward mail to your SMTP service using a specific Windows user.

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

  8. AJV said

    Hello Sunil – Thank you for posting this. I do have couple of questions:
    1) How many times will the subscription trigger. Lets say if I schedule it to run on monday at 5 AM based on the data from the query , will it run once or as long as the query fetches the data?
    2) Again , if my query does not fetch any resultset at 5am do I have an option to retry the subscription. But if the query returns resultset , I do not want my subscription to execute until the next day 5 am.?
    Please provide your inputs.

    Regards
    >>>>>>>>>>>
    AJV

    • Hi ajv, Thanks for going through my article. Please go through following.
      1. After scheduling the day(like monday), you have option to set start and stop dates in the same page. it will run once in a week if scheduled it for single day.
      2. You have no option to retry the subscription automatically, you can do it manually by executing the subscription job. I have explained that process in Execute SSRS Report from SSIS Package part#2 post. Please go through it.

      Thanks
      Sunil Reddy

      • AJV said

        Thank you Sunil. I do not have ssis installed. I will probably write a small piece of code using the webservices in C# .net to trigger the subscription and then call the .exe file from a scheduler based on the load completion.

        Regards
        >>>>>>>>>>>>>>>>>>
        AJV

  9. pies said

    Thank you for a great post, it helped me understand data driven subscription. I have slightly a different scenario, maybe you can give me your inputs on how to approach it because I know it can only be handled by datadriven subscription. I need to create pdf files for all individual product numbers that my report returns. Lets say I have two columns ProdNumber and ProdType. ProdNumber 1 has 3 types ProdTypes and ProdNumber 2 has 2 types ProdTypes which gives me over all 5 records. With this, 2 pdf files should be generated based on the ProdNumber. And the report will not show the result, it will just create pdf files in a folder when you run the report. ANY kind of help would be appreciated, thank you!

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 659 other followers

%d bloggers like this: