MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data - Page 9
June 23, 2004
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.
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.
14. Click Next.
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:
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.
29. Click Next.