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 Jan 28, 2004

MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting - Page 3

By William Pearson

The Managing Phase

Central management of the enterprise reporting function is a common and natural requirement in most organizations. Reporting Services again leverages its integration within MSSQL Server, and across the spectrum of the Microsoft applications that are pervasive in our enterprises. Reporting management functions, as many of us know, include a wide range of activities. Among these are security, scheduling, data source management, custody and control of the physical report files and the folders that contain them, and the maintenance of a diverse set of properties and resources.

One of the principal advantages that Reporting Services offers is the capability to manage reports and related items from a central location. Items that we can manage include reports, folders, data source connections, and resources. For these items, we define security, properties, and in some cases, scheduled operations. We can also create shared schedules and shared data sources, and make them available for general use.

The Report Manager centralizes these and other Reporting Services functions. Reports can be uploaded to the report server easily from either the Report Designer or the Report Manager, and can be viewed thereafter via a web browser. Multiple types of security can be assigned during the upload process, as well as elsewhere, as we see in Illustration 3, where I have chosen the sample reports folder, along with Windows NT Integrated Security, in the selection of the reports location (called the "data source" in this step).

Illustration 3: Tell Report Manager Which Reports to Upload via the New Data Source Window

NOTE: We will explore the many options available in Report Manager in our article, The Managing Phase, later in the series.

Next, we can simply select the reports we wish to upload by typing them in, or by navigating to their locations, with the Upload capability, after which the Report Manager window is populated with the reports in variable presentations, as shown in Illustration 4.

Illustration 4: Report Manager Displays Uploaded Reports for Selection by Information Consumers

Management capabilities can be controlled for end users by an Administrator, who can assign permissions, so in addition to the access security provided within Reporting Services, we are also given functional security options, for a refined set of controls over user activities. We will explore the degrees of management and various capabilities that are possible in our article, The Managing Phase, later, but suffice it to say that the overhead involved is widely customizable to an organization's needs. I will also present a subsequent article on report server administration to explore the options and processes that can be involved from that perspective.

Report Access and Delivery

Like most web-enabled enterprise reporting packages that exist today, Reporting Services allows "self-serve" access, where information consumers can view reports on demand. Consumers can also access reports via subscription processes, whereby reports are generated and delivered to the consumer at a specific destination on a recurring basis. Consumers that are granted access privileges can select a report from a folder hierarchy on the web server, in a process as simple as clicking one of the reports as depicted in Illustration 4 of our last section. The result is a report generated on demand, an example of which is depicted in Illustration 5.

Illustration 5: Sample Report Selected and Generated from the Browser of an Authorized Consumer

Subscription options include notification when a report is run, after which intended consumers can browse it at a standard location. E-mailing of a copy of the report to the intended audience is also accommodated. Notification and other processes can be based upon data-driven criteria: the recipient list can rely upon stored data that exists when the subscription is triggered, such as the data found in a customer database, for example.

Many interchangeable options exist with regard to viewing / exporting formats, as depicted in Illustration 6. Selections within the browser interface include XML file with data, CSV, TIFF, PDF (Adobe Acrobat), Web Archive, Excel, and HTML with Office Web Components. As we have already noted, integration with Microsoft Office is another obvious advantage offered by Reporting Services.

Illustration 6: Interchangeable Viewing Options Abound in Reporting Services

Summary and Conclusion ...

When Microsoft decided not to wait until the arrival of Yukon, the next release of MSSQL Server, to deliver Reporting Services, it offered organizations an early opportunity to shed the high-priced baggage of proprietary reporting options that have dominated enterprise reporting for many years. Reporting Services offers true integrated report authoring, management, and access and delivery in a single package, providing organizations a new paradigm in enterprise reporting. As we continue in our new series, we will examine in detail many of the capabilities of Reporting Services, comparing and contrasting them to the approaches of the past, and looking forward to a more cost effective, integrated tomorrow.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

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