SSRS Data Driven Subscriptions

Reporting Services
– Data Driven
General Steps
Step 1, Create report and Identify report parameters
Step 2, Deploy the report to a server
Step 3, Create the Subscription Definition Table
Step 4, Define the schedule
Step 5, Try to add a data-driven subscription
Step 6, Store Secured Credentials for the Report’s Data Source
Step 7, Try to add a data-driven subscription again
Step 8, Tell SSRS about the definition table
Step 9, Map columns from definition table to what SSRS expects
Step 10, Set the report parameters
Step 11, Define the schedule
Overall view
SSRS reports
deployed to
SSRS Server
Subscriptions need Shared
schedules, Parameter
definitions, shared data sources
with stored credentials, table
definitions for how to execute
(delivery method, recipients,
output destination, render
format, etc.)
for report A
Definition table for data
driven subscription – with
recipients, output locations,
parameters, output render
formats, etc.
Shared data
source to report
data, with stored
for report B
Shared schedule for
execution (written out as a
SQL Agent job)
What we want to do
Deployed report that prompts for Manufacturer,
shows monthly shipped tons
We want to have the server run the report
automatically on a schedule, for specific
parameters (one run for Ford, one for Chrysler,
We want the scheduled executions to be
emailed to specific recipients (maybe managers
associated with each manufacturer)
We also want the scheduled executions to
export the report output to a file share
We can populate a SQL table that holds the
specifics on the executions, and tell the
scheduled job about the table
And anytime we want to add, change, or
remove recipients, all we need to do is modify
the contents of the table, and nothing else.
We do this through SSRS Data Driven
Step 1, Identify report parameters
When this report was created in Visual
Studio, the actual parameter name is
called “Manufacturer” (which might
be different from the caption)
Identify the actual values used behind
the scenes in the query for the report,
as you’ll need to provide those values
in a table.
In this case, the possible parameter
values are simply the names of the
Manufacturers (“Ford”, “Chrysler”)
However, that might not always be the
case. The display value might be
“Ford”, but the underlying key used for
the query might be an integer foreign
key or some kind of business key.
Either way, make sure you’ve identified
the possible parameter values!
Step 2, Deploy the report to a server
Deploy the report to an existing SSRS
You can’t create a data driven
subscription until you first deploy the
Step 3, Create the Subscription
Definition Table
Param1 through Param3 will hold parameter values for
executions. Generically named, since some reports might
prompt for dates, some for products, etc.
If you know you might have a report with as many as 10
parameters, then create 10 parameter buckets here
Create two tables
(preferably in the
same database that
holds the source data
for the report, but it
doesn’t have to be
One table will hold
email recipients for
report executions,
the other will hold
file share locations
Store the deployed
report name, render
format, email address
or output path, etc.
This maps to what the report’s parameter values would be. So
it could be an integer key instead of a string
Step 3, Create the Subscription
Definition Table
So for the first table,
we want to run the
report twice, once for
Chrysler and once for
Ford, and write out
PDF files to the Path
For the second table,
we want to email PDF
output for Chrysler
and for Ford, and
email to specific
recipients (You can
separate multiple
recipients in a single
run with a semicolon)
Valid Render Formats
Excel, Word, TIFF,
Step 3, Create the Subscription
Definition Table
Note, in this example,
there’s just one report
definition in each of
the tables.
Each of the tables can
have many report
So the table that
defines File Share
and/or email output
could have entries for
a dozen reports
Creating these tables
(in itself) doesn’t
create the data driven
subscription. We need
to tell the SSRS server
about these tables and
the columns
Note the
AutoIncrement value
for Write Mode: will
cover later
Step 4, Define the shared schedule
On the SSRS server, click
on “Site Settings”, then
“Schedules”, then
“New Schedule”
• Next, on the SSRS Server where the reports are deployed, we need
to define a shared schedule
• This will define the days/time that the job(s) run.
• Eventually we’ll tie the shared schedule to the actual data driven
• The recommended practice: create the minimum number of
shared schedules to serve the greatest number of report jobs
• Note: SQL Server Agent MUST be running on the database server
associated with the SSRS Server
Schedule details
Step 5, Try to add a data-driven
On the SSRS Server, go to
the folder where the report
is deployed, click the
Dropdown, and go to
This will give us a dialog of
the server properties for
the report, including the
ability to define a data
driven subscription
Step 5, Try to add a data-driven
In the Manage dialog, click on
And then on the subscriptions
page, try to click on the “New
Data Driven Subscription” link
Note that the link has an
exclamation point, indicating
that there’s probably an issue
In this case, we get the following
Basically, the report’s data
source must have specific
credentials stored on the server.
Very likely the report’s data
source is using Windows/SQL
authentication, and that’s not
Step 6, Store Secured Credentials for
the Report’s Data Source
This should be a
special domain
account set up by
the DBA.
Don’t use a normal
First, in the SSRS Data Source folder, create a new Data
Source for the report to use. Define the Connection
String that points to the Database Server and Database
Then provide stored credentials, for when the report job
accesses the server at execution time
Finally, in the report itself, go to data sources, and then
specify a shared data source, and point to the data source
created in the first step
Step 7, Try to add a data-driven
subscription again
SSRS also requires stored
credentials for the
connection (data source)
to the definition table
from Step 3. We might be
able to use same shared
data source with stored
credentials from Step 6.
Click on the Subscriptions page
And then on the subscriptions page, try to
click on the “New Data Driven
Subscription” link
Now, there’s no exclamation point in the
link, which means we can proceed
Will lead you to next page, where you’re
asked to give the subscription a name,
and then chose the delivery method.
File Share and Email are separate steps,
so if you have a data driven subscription
for each of the 2 delivery methods, need
to go through the process twice
If the definition table (from step 3) is in
the same database as the report source
data, and if you created a shared data
source with stored credentials in the
previous step, then select “Specify a
shared data source”
Step 8 – Tell SSRS about the definition
Next page will prompt for table.
Since we specified “File Share” for the
delivery method, we need to tell it about
the definition table
Provide a SELECT statement to return all
rows for the specific report for which we’re
creating the subscription
Remember that the definition table might
have subscription information for many
reports, so the WHERE clause needs to
reference the specific report
Make sure to click VALIDATE – this will
make sure that SSRS has the ability to read
the table. (It doesn’t validate the columns,
or even how many rows – it simply makes
certain that the query can be executed)
Step 9 – Map columns from definition
table to what SSRS expects
SSRS needs to have write
access to the output path
designation at runtime. So we
need credentials. Again, this
should be a special domain
account, not a regular user
domain account
From step 3, we had the following columns in our
definition table that we need to tell SSRS about:
FileName (the name of the output file for the specific
Path (where the file should go )
Render Format (the export type)
File Extn (whether a file extension should be created on
the file)
Write Mode (Whether to overwrite any existing file on
execution, or to auto-increment the file name by 1 with
a numeric suffix on the output file)
Notice that SSRS needs this information – so when it
asks for the output file name, we give it our
column….when it asks for a render format, we give it
out column, etc.
When we go through the steps for an email
subscription, we’ll be promoted for columns specific
to the other definition table (such as email address)
Note that we haven’t yet been prompted for the
report parameters or the schedule – that comes next.
Step 9 – Map columns from definition
table to what SSRS expects
For email subscriptions, we need to
provide the following columns from
Render Format
If you had additional recipients to CC: or
BCC:, you would have had additional
columns back in the definition table
If you’re sending an attachment for the
email (which will usually be the case), set
“Include Report” to true
You can also set priority, subject, and
comment if you wish
For Include Link, you would only set this to
TRUE if recipients have access to the
report server, and you want them to be
able to run the report manually. But for
people who can’t (or don’t want) to run
the report manually, set this to false
Step 10 – Set the report parameters
SSRS will now prompt for how to deal with
If the report had five parameters, it would
prompt for all five
We’re not entering the values for
manufacturer – instead, we’re telling SSRS
what column from our definition table to
use, when running at runtime
So in this example, the subscription will
run twice – for Chrysler and for Ford, with
2 separate outputs
Since we’d use this definition table across
many reports, that’s why we’ve kept the
parameter column names generic
(“Param1”, “Param2”, etc)
Step 11 – Define the schedule
Finally, tell SSRS when the subscription
should fire
Best practice is to select the shared
So we created the shared schedule first
(independently of any subscription), and
then we map multiple subscriptions to it.
(This is better than creating 5 report
subscriptions and hard-wiring the same
scheduled date/time for each one)
This will map the subscription directly to
the SQL Server Agent Job
What we have at the end
Final notes/recommendations
 Make sure you’ve identified all report parameters (by name, and also what
the underlying parameter values will be….whether they are string values,
integer keys, dates, etc)
 Think ahead of time about execution schedules, recipients, overall strategy.
Plan it out!
 Create 2 general definition tables for subscriptions – one for file share and
one for email subscriptions
 Create a minimal number of shared schedules (independently of the
subscriptions), and then apply them to the subscriptions as needed
 Create shared data sources with stored credentials
 Additionally, you’ll need to provide credentials for writing to the
UNC/shared location when doing file share subscriptions

similar documents