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

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

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 27, 2004

MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions - Page 3

By William Pearson

Subscription-Based Reporting

Within the context of subscription-based reporting, our designated report server uses information that we provide it to schedule and deliver the report to designated individuals in the organization, through the channels that we also specify. Subscriptions are established as "standing orders" to Reporting Services to deliver reports in one of two ways: subscriptions are based upon either an event or a schedule.

One of the obvious benefits that subscriptions provide is the elimination of the need of the designated information consumer(s) to perform any action to obtain an updated report. This "push" capability can also be useful in establishing reporting based upon data, and other, events, whereby a designated user can be informed of the occurrence of the event with little additional effort, freeing them to pursue other activities in the meantime. Regardless of the trigger for report generation, subscriptions can provide highly useful, automated delivery of updated information to intended consumers in a reliable manner.

There are two kinds of subscriptions from which we can choose in Reporting Services, standard subscriptions and data-driven subscriptions. Standard subscriptions are usually established and maintained by the information consumers for whose benefit they are created. Data-driven subscriptions, on the other hand, generate their subscriber lists when they are executed, along with other delivery options, and are typically created and maintained by Reporting Services administrators and other operatives with familiarity in report creation and operation.

Let's take a look at subscription basics in the following hands-on exercise steps. While our focus in this exercise will be the definition and operation of a standard subscription that delivers a single instance of an on-demand report, we will delve into data-driven subscriptions, which are a bit more involved, in a later article.

The Business Requirement

Let's say, for purposes of illustration, that we have been tasked with establishing a subscription whereby we will "push" the FoodMart Sales report to a designated group of information consumers. (Up until our establishment of the subscription, the report was regularly distributed manually to users, by attaching the respective report file to individual e-mails, and mailing these, once this was accomplished).

Ideally (according to the information consumers), the report will be "parked" in a shared folder that interested parties can access at will. The report would be most useful, for various analysis purposes, as an MS Excel spreadsheet. The report is parameterized, a fact that we will leverage in our exercise, because the intended audience consists of managers who are concerned with how Non-Consumable products are faring within the FoodMart chain, and have little interest in members of the other product families.

1.  Click the link for the FoodMart Sales report, as shown in Illustration 7.

Illustration 7: Accessing the FoodMart Sales Report (Partial Folder View)

The report executes, as we have discussed earlier, when we click the link in Report Manager's Folder view, and we again see the Report is being generated message. The report then appears, defaulted to the Drink Product Family in the parameter selector dropdown list appearing atop the report. Let's set the parameter to the appropriate selection.

2.  Select the Non-Consumable Product Family in the parameter selector dropdown list.

3.  Click the View Report button to refresh the report.

The report regenerates to accommodate the new parameter we have supplied, and appears as depicted in Illustration 8.

Illustration 8: The FoodMart Sales Report, Filtered to Non-Consumables

Our purpose here is merely to get an idea of "where we want to go," and to create a report against which we can compare our subscribed report, to ascertain that it is selecting the parameter that has been designated by the information consumers in the business requirement that we have received.

Before we begin the setup of a standard subscription in a report, we need to ensure that the following conditions are in place. They are likely to be easily accommodated for our ends, assuming that settings have not been altered in the sample reports / environment, but it is never too early to be exposed to real-world rules:

  • We must have access to / permission to view the report;
  • We must have capability within the context of our role assignment to create subscriptions;
  • The report must either require no credentials to run, or have the necessary credentials stored within its structure.

NOTE: We will cover these topics from numerous perspectives as we progress in the series.

MS SQL Archives

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