Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 23, 2004

MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data - Page 10

By William Pearson

We arrive at Step 5 - Create a data-driven subscription: Foodmart Sales.

On this page, we have the option to specify the last of the subscriber database references, the source of the parameter information for the Data-Driven Subscription. The potential for this capability is great, as we have the ability to specify (via the appropriate field in our database) the parameters that are applied to a report before it is delivered - for each individual subscriber! We can thus ensure that consumers receive only relevant information on a recurring basis, with parameterization of the report supporting virtually any sort of filtering we might imagine. This is a Reporting Services capability that, together with numerous others within the Data-Driven Subscription functionality, provides advantages over the capabilities of other enterprise reporting solutions. (Sure, you can accomplish this in many with macros or other coding, but this is about as "out of the box" as it gets.)

30.  Select the radio button to the left of Get the value from the database.

31.  Select Parameter (the column in our SubscriberDetails table where we specify the "fill in" for the report parameter, for each respective subscriber) in the selector to the right of Get the value from the database.

The Step 5 - Create a data-driven subscription: Foodmart Sales page appears, with our settings, as shown in Illustration 37.

Illustration 37: The Step 5 - Create a Data-driven Subscription: Foodmart Sales Page

32.  Click Next.

We arrive at Step 6 - Create a data-driven subscription: Foodmart Sales.

Finally, we have come to the scheduling portion of the Data-Driven Subscription setup process.

33.  Click the radio button to the left of On a schedule created for this subscription, as depicted in Illustration 38.

Illustration 38: The Step 6 - Create a Data-driven Subscription: Foodmart Sales Page

We thus elect to establish a schedule (versus an event) trigger for our new Data-Driven Subscription. We will specify the details on the next page.

34.  Click Next.

We arrive at Step 7 - Create a data-driven subscription: Foodmart Sales, the last of the Data-Driven Subscription creation pages. Here we specify the processing schedule of the subscription, including time and starting / ending dates.

35.  Click the radio button to the left of Hour in the Schedule details section.

36.  Specify a time that is a few minutes away, if convenient. (I chose five minutes later than my present time when inputting the time initially).

37.  Assign today in the Begin running this schedule on box, in the Start and end dates section of the page (the calendar icon allows selection of a date, or it can be typed in).

The Step 7 - Create a data-driven subscription: Foodmart Sales page appears, with our settings, as shown in Illustration 39.

Illustration 39: The Step 7 - Create a Data-driven Subscription: Foodmart Sales Page

38.  Ensure that MS Excel is not open.

39.  Click Finish

The next page that appears lists the subscriptions we have created, and, once the scheduled time for processing has passed, the various details about the most recent processing cycle (we may have to refresh the page at some point after the subscription was scheduled, in our last step, to run). Our entry should indicate that four reports have been processed, with no errors. The page with entry for our newly created and processed subscription should appear similar to that depicted in Illustration 40.

Illustration 40: Status of the Data-driven Subscription (Post-Processing)

NOTE: If the subscription indicates errors, a good place to start to troubleshoot it is the ReportServerService_[DATE].log, although other trace logs are also available. See the Books Online for more information. I will also be addressing various troubleshooting and processing optimization topics in future articles in this series.

We can edit both Standard and Data-Driven Subscriptions from this page by clicking the Edit link appearing next to the respective subscription title, and walking through the setup pages to the point(s) we wish to modify.

40.  Click My Subscriptions to open the My Subscriptions page.

The My Subscriptions page opens, and appears as depicted in Illustration 41.

Illustration 41: The My Subscription Page

We can also view, modify and delete existing subscriptions from the My Subscriptions page. This is a "maintenance" center, as it were, and does not provide the capability to create subscriptions. My Subscriptions typically also shows (depending, however, upon our security setup) only those subscriptions that we have created - not those that are created by other users.

41.  Return, via the Edit link, to reset the subscription settings you have made (that is, the recurrence settings, etc.), or perhaps take steps to delete the Data-Driven Subscription entirely, as desired.

42.  Close Report Manager, after reviewing the steps we have accomplished in this lesson - and after experimenting further, as desired.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM