Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Creating a Reporting Services Data Driven Subscription

  • 4 January 2010
  • Author: DevinKnight
  • Number of views: 16658
  • 0 Comments

Previously I wrote an article on Subscriptions and how to configure them on a report to have it delivered unattended on a schedule.  If you have not read that article make sure to review the prerequisites to running subscriptions:

·         SQL Server Agent Must be running

·         Enable sharing on folder that report is being delivered to or configure SMTP server

·         Create a SQL Server Authenticated account that the report data sources can use

In this article I will be going beyond configuring regular Subscriptions and discuss how you can configure Data Driven Subscriptions.  This kind of subscription is only available with either the Enterprise or Developer editions of SQL Server and gives you a way to dynamically deliver and process reports. 

For example, if you have a report that uses a parameter to return different values you can use a Data Driven Subscription to create a report with the results of every possible parameter value.  You could also use this kind of subscription to dynamically deliver your reports based on a query your write.

To configure a Data Driven Subscription first open the report that you want subscribe to in the Report Manager.  Once the report is open select the Subscriptions tab.

Then select the New Data-driven Subscription option. 

If you see a warning sign on top of the icon then it is likely that you have not correctly set your data sources for this report to use SQL Server authentication.

Step One:  Select delivery method

After clicking on New Data-driven Subscription you can start the configuration.  Here you can give the subscription a description, decide how to deliver the reports, and what data source will to query.

·         You have three options for delivery methods:

·         Windows File Share – places reports in a folder setup for sharing

·         Email – uses an SMTP server to email reports

·         Null Delivery Provider – Saves reports into cache to improve report performance

If you have SharePoint installed there’s also a third option to deliver to SharePoint library.

The data source selection will depend on if you have already deployed the data source that is used for subscription query.  Remember the Data Driven Subscription will require you to write a query that will populate things like the name of the file created, the kind of file to deliver, and who to deliver it to.  If you do not already have the data source to write this query this select specify for this subscription only but if the data source is already deployed select Specify a shared data source.  Once you have decided what kind of data source you have click Next.

In this example, I will do a Windows File Share using a shared data source.

Step Two:  Select/Create a data source for your query

Select the data source that you will use to write a query on.

I will be showing AdventureWorksDW2008 as my data source.

Step Three: Write query

                Now you are ready to write the query that will handle the subscription.  The query you write can populate any parameter in the report as well as these Subscription properties:

·         FILENAME

·         PATH

·         RENDER_FORMAT

·         WRITEMODE

·         FILEEXTN

·         USERNAME

·         PASSWORD

Each of these settings will be talked about in the next step.  Once you write your query you can manipulate the command time-out and also validate the query.  Once you are satisfied with your query select Next.

                Here I am writing a query to populate the report parameter and the Subscription property FILENAME.

Step Four:  Delivery Configuration

                Now you must specify the settings for delivery of the report.  Each one of these properties could have been manipulated with the query written on the previous step.  You can either type in a static value or get the value from the database, which is just the query you wrote.

·         File name – the name of the file(s) to be created

·         Path – the file path where the file will be sent, which must be full UNC path. (Ex.\\ServerName\Folder)

·         Render Format – what type of file will be created (Ex. Excel, PDF, Word…Just to name a few)

·         Write mode – whether the file that is created can overwrite other files with the same name or to auto-increment (Ex. ColorReport_1, ColorReport_2…)

·         User name – user that has permissions to save files to the shared folder in the Path property

Step four will look different depending on the delivery method you choose.  The configuration for Email delivery obviously has different properties.  Once your settings are complete hit Next.

My example I have mostly static values except for File name that is bring populated from the query.  My path is covered in the screen shot mine looks like this (\\Devin-PC\Reports).

Step Five:  Fill report parameter values

All report parameters can be set to dynamically change when using Data Driven Subscriptions.  Based on the query you wrote you can make it so each report that is delivered has a different value populated for the parameter.

                I used my query to fill the value of parameter.  Each report that is generated will show the results with a different parameter.

Step Six:  Select scheduling options

                Scheduling the subscription gives you the ability to send reports unattended at any point and time. 

                Your options are:

·         When the report data is updated on the report server – when changes have been made to the report

·         On a schedule created from this subscription – setup a schedule just for this subscription

·         On a shared schedule – a predefined schedule

Choose when you want to subscription to run this hit Next.

I will show how to setup a schedule just for this subscription.

Step Seven:  Create a schedule

                Now you will create a schedule for when the report should run.  The report can be set to run hourly, daily, weekly, monthly or just once.  You can also set a date range for how long this schedule should run.  When this is done select Finish.

My options show that I will just be running this schedule once.

                The Data Driven Subscription is now complete.  You can view a list of all the subscription you have for the report back on the Subscriptions tab.

               

                This also created a SQL Agent job to run the subscription.  So if I don’t want to wait around for my schedule to test the subscription I can just run the job directly from SQL Server Management Studio as a job.  When the job actually runs if you followed my example you should see nine files sent to a folder that looks like this:

               

Each file has a different value passed into the parameter providing different results for each.  Also, each file is dynamically named so you can tell the difference between each report.

Print
Categories: Performance Tuning
Tags:
Rate this article:
5.0
DevinKnight

DevinKnightDevinKnight

Other posts by DevinKnight

Please login or register to post comments.