Home > SSRS Expert > SSRS #60 – Steps to implement a data-driven subscription

SSRS #60 – Steps to implement a data-driven subscription

I am back from vacation, and have spent a couple of weeks catching up my workload. Now I am ready to finish the SQL Server BI Quiz “Mass Mailing Your Report”.

Here is my original blog, SSRS #55 – SQL Server BI Quiz “Mass Mailing Your Report”. Thanks to the following readers who have posted their answers on http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/mass-mailing-your-report.aspx:

Underthefold
vshah555
alpesh5410
omvish

All of their answers mentioned about data-driven subscription engine in SSRS, but unfortunately, none has given details in implementation.

Since the quiz is all about the details in implementation, I have decided not to pick any winner.

In this post, I’ll blog the implementation using the data-driven subscription engine in Reporting Services.

I have seen developers using SSIS package to attach reports in emails. This approach requires spreadsheet templates and lots of scripting in SSIS.

With the data-driven subscription engine, you just need to create a SSRS report, and pass dynamic parameters to the SSRS report, and tell the data-driven subscription engine to send out the report as an attachment via email. There is no need for spreadsheet template to make the report pretty, and you can pass any values to the report parameters, and best of all, there is no need for any scripting.

There are four basic steps when working with data-driven subscription
  1. Create a SSRS report with the desired layout and parameters, and then
  2. Design a SQL table that will hold your dynamic parameters, and
  3. Pass the correct parameters to the SSRS report so the subscription engine does not reject them, and finally
  4. Tell the data-driven subscription engine to send the report (In my example, I’ll stay with email delivery option. With the file share option, it adds another layer of complexity to it, that is, using the correct service account to access the shared path when I get time, I’ll blog about it in a separate post.)
    1. Step 1: Create a SSRS report with the desired layout and parameters

      I assume that you already know how to create SSRS reports with parameters.

      Step 2: Design a SQL table that will hold your dynamic parameters
      In my example, I have the begin date, end date and the supervisor ID as my report parameters. When the supervisor ID is 1, I need send report to Supervisor1@company.com; when the supervisor ID is 2, I need send report to Supervisor2@company.com.

    image

     

    I can also use this same table to set the begin and end date for a month-to-date report, rather than just a daily report.

    Step 3: Pass the correct parameters to the SSRS report so the subscription engine does not reject them

    On the Reporting Server, find your SSRS report. Under tab Subscription, click “New Data-driven Subscription”.

    image

    In the Step 1 window,

    • 1) give the subscription a description.
    • 2) Select E-mail as the delivery option
    • 3) Select “Specify a shared data source”

    image

    On the Step 2 window, select the same data source your SSRS report uses.

    image

    On the Step 3 window, enter the SQL command as shown and click Validate button to make sure the command is good.

    Table SUBSCRIBER.T_SUBSCRIBER_TEST stores all my parameter values shown in step 2. Change it to your own table.

    image

    In the Step 4 window, choose “Get the value from the database” for the following fields:

    • To
    • Cc
    • Reply-To
    • image

    Also select Excel as the Render Format.

    This is the fun part. When you click the drop-down box, a pick list showing all the fields from my table SUBSCRIBER.T_SUBSCRIBER_TEST showed up.

    You can see that you can also make the email subject dynamic too.

    image

    On the Step 5 window,

    • Use field ParamBeginDate as the begin date
    • Use field ParamEndDate as the end date
    • Use field ParamSupervisor as the Supervisor

    image

    On the step 6 window, select the second choice.

    image

    This is the last window for the subscription wizard. Instead of triggering the subscription with a fixed schedule, we will trigger it in a SQL job step right after my SSIS package finishes the daily ETL process.

    Selecting Once will disable the fixed schedule. 

    image

    Last step, i.e. Step 4, trigger the subscription in a SQL job step right after my SSIS package finishes the daily ETL process

    Since I use SSIS packages to automate 99% of my SQL tasks, there is no surprise that I did this last step also in a SSIS package.

    This SSIS package is executed right after my daily ETL process. The primary purpose of this package is to trigger the above subscription I just created.

    image

    There are three things I need to take care before I start the subscription.

    1. I don’t want to send emails to users on weekends or during holidays. So I need to read in the holiday and the weekend flag. Then I used a Precedence Constraint to decide whether I need to continue to not,.

    2. The second thing I need to take care of is the table SUBSCRIBER.T_SUBSCRIBER_TEST. I need to populate it with the correct begin date and end date, and possible supervisor IDs.

    3. The last thing I need to take care of is to read the Subscription ID from the Reporting Server for my SSRS report.

    This is the query you can use to get the Subscription ID from the Reporting Server for your SSRS report.

    image

    I save the Subscription ID to a user variable varSubscriptionID.

    image

    Finally, I use the AddEvent SQL command to trigger the subscription, of cause, with the Subscription ID (passed in by ?).

    image

    We are done!

    Hope I didn’t confuse you or scare you away from using the data-driven subscription. If you are not comfortable with Integration Services, you do not need to use it. Simply triggering your subscription from a fixed schedule with a hard-coded Subscription ID in a SQL command is perfectly acceptable. 

    About these ads
    Categories: SSRS Expert
    1. Ali
      February 6, 2014 at 4:15 pm

      thanks for a great article …. now if i want to add a new row to a Table and have a different Start , End Date and Supervisor ID …. do i have to create a new subscription to run report with different parameters ….

      I have all my SSRS reports working . I call them USING C# Application.
      User click on the name and Select the Report Name. When clicked the App call the Report RDL from Report Server and shows him the report page.
      Now in report i have parameters , user will now select different parameters and click View button to view the report. The report comes up. I am having some performance issues.

      I now would like to create a Data Driven Report Subscription. I will create a interface in C# to select the report parameters and insert them in the table which will be accessed by Report Server for Subscription.
      Lets say user inserted 5 row in the table with different parameter’s.

      Now my question is :
      Do I have to create 5 different subscription or Just one Subscription , which will loop through 5 different records with different parameters and create 5 reports and save it on the file share or email.

      Thanks for your help

      Like

    2. February 7, 2014 at 5:30 am

      Hi Ali,

      You already answered your own question.

      “Just one Subscription , which will loop through 5 different records with different parameters and create 5 reports and save it on the file share or email.”

      Sherry

      Like

    1. No trackbacks yet.

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

    %d bloggers like this: