We arrive
at Step 3 - Create a data-driven subscription: Foodmart Sales.
This is
the point at which we enter the query we defined and tested earlier, whose
purpose is to retrieve subscription data from the RS_Subscriber database
(and the SubscriberDetails table within the database), which we have
constructed to hold the Subscriber information.
The
simple query we constructed earlier will return a result set that contains one
row for each of the subscription recipients we have defined. As we will see on
subsequent pages, the dataset is used to populate Data-Driven Subscription
extension settings.
11.
Type (or cut
and paste, either from below, or from the .sql file we tested and saved in the
earlier Populate the Table with Query Analyzer and Create a Select
Query section) the following simple
query into the Query pane:
SELECT *
FROM
dbo.SubscriberDetails
12.
Leave the Specify a time-out for this command setting at default.
13.
Click the Validate
button at the bottom of the page.
A Query validated
successfully message appears (in green lettering) in the bottom left corner
of the page. The Step 3 - Create a data-driven subscription:
Foodmart Sales page appears as
shown in Illustration 35, with our settings.
Illustration 35:
The Step 3 - Create a Data-driven Subscription: Foodmart Sales Page
NOTE: If you did not obtain indication
of a successful validation as shown, check your query, then perhaps the steps
setting up the database and table, if the query itself is not the issue.
We arrive
at Step 4 - Create a data-driven subscription: Foodmart Sales.
Here we
specify the fields in our SubscriberDetails table that we will use to
supply the respective requirements in the Data-Driven Subscription we
are establishing. One of the obvious advantages in pointing the subscription
to a table in this way is that the subscription will change to reflect changes in
the composition of the data in the database. This means that we can manage
large lists of subscribers, who might come and go regularly, as well as
assorted other details of the subscription, without modifying anything except
the central database.
15.
In the File Name
section, near the top of the page, select the radio button to the immediate left of Specify a static value.
16.
Type the
following into the Specify a static
value box:
FoodMart_Sales_Report
17.
Under File Extension,
select the radio button to the left of Specify a static value.
18.
Select True in the
selector to the right of Specify a static value.
We will be using a "built-in"
extension for File Share Delivery, and thus will not be required to
retrieve this information from the RS_Subscriber database.
19.
Under Path, select the
radio button to the left of Get the value from the database.
20.
Select File Share (the
column in our SubscriberDetails table where we specify the respective
UNC file share locations for our subscribers) in the selector to the right of Get
the value from the database.
21.
In the section below, Render
Format, select Get the value from the database via its radio button
again.
22.
Select Media Format in
the selector to the right of Get the value from the database.
Recall that the Media
Format column in our SubscriberDetails table houses the format type,
such as Excel, PDF, HTML and Image, for each
respective subscriber.
23.
In the next section of the
page, User Name, select
the radio button to the immediate left of Specify a static value.
24.
Type a valid
MSSQL Server User Name into the box to the right of Specify a static value.
I used my own User Name, as I am an MSSQL Server
Administrator on my PC, which uses integrated NT security.
25.
Under Password, select
the radio button to the left of Specify a static value.
26.
Type the password associated
with the User Name above into the box adjacent to Specify a static
value.
Keep in mind the
distinction between the stored credentials of the FoodMart Sales
Report and the credentials we have inserted above, which allow us to access
the RS_Subscriber database, and the SubscriberDetails table we
created therein.
27.
In the last section of the
page, Write Mode, select
the radio button to the immediate left of Specify a static value.
28.
Select Overwrite
in the selector to the right of Specify
a static value.
This instructs
Reporting Services to overwrite an existing file in the individual folders, to
which we will be writing our report files. This is one option for how to
handle, say, a scenario where Reporting Services is scheduled to deliver a file
on a recurring basis, and meets with an identically named file when it attempts
to do so.
While there is some
flexibility here, and the setting will need to fit the need of the report
consumers to whom the report is to be delivered, one way to ensure that the
file is the "most current" is to simply write over any pre-existing
file. (If consumers want to archive "snapshots in time," they can
rename the file with a date, as one option, or simply move the files from the "landing
folder" after receipt, but before the next scheduled report delivery. I
have formulated many other approaches at various clients, including elaborate
sweep mechanisms, and so forth).
The Step 4 - Create a data-driven subscription:
Foodmart Sales page appears, with
our settings, as shown in Illustration 36.
Illustration 36:
The Step 4 - Create a Data-driven Subscription: Foodmart Sales Page
» 