Transcript SSRS Data Driven Subscriptions

1
Reporting Services
– Data Driven
Subscriptions
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
2
Overall view
SSRS reports
with
parameters,
deployed to
SSRS Server
3
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.)
Data-Driven
Subscription
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
credentials
Data-Driven
Subscription
for report B
Shared schedule for
execution (written out as a
SQL Agent job)
What we want to do
4

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,
etc)

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
Subscriptions
Step 1, Identify report parameters
5
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
6
Deploy the report to an existing SSRS
Server
You can’t create a data driven
subscription until you first deploy the
report
Step 3, Create the Subscription
Definition Table
7
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
there)

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
8

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
specified

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
are PDF, MHTML,
Excel, Word, TIFF,
CSV, XML
Step 3, Create the Subscription
Definition Table
9

Note, in this example,
there’s just one report
definition in each of
the tables.

Each of the tables can
have many report
definitions.

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
2
On the SSRS server, click
on “Site Settings”, then
“Schedules”, then
“New Schedule”
1
10
3
• 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
subscription
• 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
subscription
11

On the SSRS Server, go to
the folder where the report
is deployed, click the
Dropdown, and go to
Manage.

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
subscription
12

In the Manage dialog, click on
Subscriptions

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
message

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
sufficient
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
user/developer
account
13

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.
14

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
table
15

Next page will prompt for table.

Since we specified “File Share” for the
delivery method, we need to tell it about
the definition table
dbo.tblDataDrivenSubscriptionFileShare

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
16
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
definition)

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

17
For email subscriptions, we need to
provide the following columns from
dbo.tblDataDrivenSubscriptionEmail

EmailAddress

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
18

SSRS will now prompt for how to deal with
parameters

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
19

Finally, tell SSRS when the subscription
should fire

Best practice is to select the shared
schedule

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
20
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
21