Professional SQL Server Reporting Services

John Wiley & Sons

Chapter One

SQL Server Reporting Services is an amazing offering from Microsoft that will change the way you create and deploy reporting solutions. It's difficult to fully appreciate the revolutionary nature of this product until you understand its architecture. The look and feel of the Report Designer environment and the functionality of a particular report view window have little to do with its full capabilities. Like your favorite media player program, you can always bolt-on another skin or façade, but it's what's inside that really matters. The product group has done a stellar job by providing a design environment and a nice web-based report management and viewing application. The impressive part is the underlying architecture that makes SQL Reporting Services a fully scalable and extensible solution that is also surprisingly easy to work with.

If you are impressed by the capabilities of the .NET Framework, web services, SQL Server, and ASP.NET, you should know that by using these technologies Reporting Services takes data accessibility to the next level. Microsoft is making good on their promise of making information available "any time, any place, and on any device." Reports may be designed using specific rendering formats and page sizes to support mobile devices. There are many other reporting tools with impressive capabilities but none of them are quite like this one.

This chapter will introduce several topics that will be covered in greater detail later in the book. This will be a high-level view of the need for, purpose, capabilities, and mechanics of SQL Server Reporting Services.

In short, this chapter includes a discussion on the following main topics:

The history of reporting

Business Intelligence (BI) and decision support in current and past reporting solutions

Reporting solutions and application types used to deliver reports Installing Reporting Services, setup options, resources, and tools

Report Definition Language (RDL)

Who Is This Book for?

Since you've picked up this book, you may be in need of a reporting solution. You may be an application developer, solution architect, project manager, database administrator, or business owner. Maybe you're not a technical professional and you just need reports for your business. Perhaps you are the executive sponsor of a project and you need to know what kinds of capabilities are available for IT professionals to build a solution for you. We assume that you have made (or are considering making) an investment in Microsoft products to manage a business process of some kind. You may need SQL Server or Visual Studio .NET.

We have made it a point to address several aspects of reporting from the perspectives of executives and business managers who need to have solutions developed for them; project managers, business analysts, and software developers who will design and create solutions; and for database and system administrators who will configure, deploy, and maintain databases and business reporting infrastructure.

After spending a couple of months with the early beta release versions of Reporting Services and building solutions with them, I had the opportunity to conduct some early adopter classes for BI and report solution professionals before the product was released. For most, this was their first look at SQL Reporting Services. Everyone was impressed and excited about putting it into practice. I was taken back by a handful of non-developers who complained that they wanted to use Visual Studio to create reports. "Why should we have to buy this product and learn to use it?" they asked.

In Chapter 11 you will see how designing reports isn't restricted to the Visual Studio .NET design environment. There will likely be other design tools for building reports in the market soon. The fact is that designing reports is easy. If you have used other report design tools, I'm sure you will agree. One nice thing about using the Visual Studio report designer is that it feels like the other Microsoft products you already know how to use. If you are a Microsoft developer, you'll love it. If you're not a developer, you'll love it when you realize how easy it is to design, deploy, and manage very powerful reporting solutions with it.

Agility

Imagine that you are sitting in a presentation meeting at the corporate office of a key customer. You are a senior sales representative for a company that sells high volume data backup systems, and the solution they decide on will be implemented in several regional data centers around the world. Your team has been preparing for this meeting for months. Your success depends on your ability to demonstrate your competence to the customer and a clear understanding of their needs. Your team has done their homework, and you know the customer has a history of scanning printed medical records and storing them as image files. Based on this information, you are certain that a particular product will adequately provide the file backup facilities for their moderate volume of image files. You have made it a point to familiarize yourself with the capabilities of the system that appears to be the best fit.

During your customer's opening presentation, they tell you that they have recently made a huge investment into full motion video imaging equipment. Now they need a backup system that can handle large file capacities. They are prepared to make an investment that is substantially larger than what you had anticipated for a capable backup solution. Your company began to offer a large-scale solution just a couple of weeks ago but you aren't very familiar with its capabilities. You've spent so much time preparing to sell the smaller system that you haven't had time to learn more about this new product. Your associate is doing introductions, and it will be your turn in about 15 minutes.

Discretely, you open your Pocket PC Phone and access the World Wide Web. You login to your company's secure report server, select the product catalog report; choose the product category and then drill-down to the new product. The report has a drill-through option that lets you quickly view a detailed specification report for the new, high-volume backup system. After noting the pertinent specifications, you save this report to a PDF file and then choose the customer sales inquiry history report. Looking up this customer, you learn that someone named Julie made an inquiry about two months ago regarding video media backups from this very company.

Looking around the room, you find a name card with her name on it. You explore the details of this call, and you find that she had asked if you offer a solution comparable to a very expensive product from a competitor. Checking the competition's web site, you discover that the competing product Julie had mentioned uses older technology, has a smaller capacity than the new system, and it costs considerably more. You save a report with all of the pertinent specifications to your memory card, hand the card to the administrative assistant sitting next to you, and ask that he make printed copies of the PDF file it contains.

Your colleague finishes her presentation and then introduces you. Taking another quick glance at the new product specs, you begin your introduction. You explain that one of your team's greatest strengths is your real experience and understanding of how business can change day-to-day. In order to be responsive and competitive, it's necessary to adapt to these changes. You show the brochure for the mid-scale product and explain that this product would be an excellent solution for a company that just scans documents. But for digital video, a more capable solution is required. You share the product specification and qualify the product to your customer's needs. During your presentation, the administrative assistant returns with the printed specification report. Not missing a beat, you distribute these to everyone and conclude. Making brief eye contact with your colleague, he raises an eyebrow just before your customer's chief decision maker, Julie, aggressively shakes your hand, and thanks you profusely for your time and effort.

The Way We Were

In many business applications, reports were an afterthought. This lack of planning often forced developers to build ad-hoc reports with little opportunity for significant planning and design. Queries became complicated and difficult to support. Reports ran slowly and were prone to errors. To avoid these difficulties, you really need a plan. In a perfect world, you would architect the database and application around your reporting needs, and would completely understand your users' requirements before designing the system. In the real world, you may understand some of the users' needs ahead of time but chances are that new reports will be requested long after the other features are in place.

According to Frederick P. Brooks' The Mythical Man-Month, it's usually a good idea to learn from and throw away your first few attempts at almost any design. I typically try to develop reports in stages realizing that the first attempt will be a prototype. My experience has been that when you gather the initial requirements, users will ask for a handful of different reports based on some specific criteria. After the solution is deployed and people begin to use it, others will almost inevitably realize that they too would like reports to help make their jobs as easy as their associates. As users realize what kinds of information they can get, they will find new and exciting ways to sort, filter, group, pivot, and slice and dice their data - in ways they never thought possible. That is, until you show them the possibilities.

That Was Then, This Is Now

Static, printed reports may be an acceptable format for a list of products and prices or for a company, but not for the majority of the information people use to make important decisions today. Business decision makers need pertinent information, and they need to view it in a manner that applies to that person's role or responsibility. Since most users deal with information in a slightly different manner, you can create hundreds of reports, each designed for a specific need. Alternatively, you can create flexible reports that serve a broader range of user needs. For example, a sales summary report could be grouped or filtered by the sales person's region, by customer type, and include information for the week, month, quarter or year, or for a specific product category. To produce individual reports for each of these needs would be time-consuming and cost prohibitive. Besides, computer users are savvier than they were a few years ago and need to have tools that help them take informed decisions, not just look at the numbers.

I recall working at Hewlett-Packard several years ago in a manufacturing site IS group. Every Thursday a report cart would come around. There were several regularly scheduled reports that the mainframe system produced on a weekly and monthly basis. Users, typically department managers, would subscribe to these reports that were then printed in another building and delivered by hand to each subscriber. Many of these reports were little more than a huge list of numbers and text printed on continuous, fan-fed paper - some as large as 500 pages. I watched inquisitively as managers would meticulously scan through the pages, highlighting and circling figures of interest. Some would bind them into large books and give them to their administrative assistants to go through with a ten-key calculator and add up all of the figures they had highlighted.

At the end of the month dumpsters full of these reports were hauled off to landfills and recycling centers as their usefulness quickly came to an end. I spent nearly two years developing a reporting application for this group using Microsoft Access. We originally planned for eight to ten reports in this application. But as time went on, and users began to rely on the reports to perform their jobs, they would ask for the same reports with different sorting, grouping, and selection criteria. In the end, we deployed some 25-30 reports, most of which were variations on the few original reports.

Business Intelligence and Decision Support

Dennis Higgins is a BI Consultant with Strafford Technology, Inc. in Windham, NH. He says, "I think one of the greatest challenges to providing BI Solutions is to educate the customer as to the extent of the long-range problems (and the associated business costs) caused by disjointed attempts to derive information from corporate data. Closely related to that is to correct the normal tendency to apply band aids. Foresight and planning with a BI Strategy is the most effective means of halting the creation of stove-pipe data analysis systems. Once management perceives the benefits and buys into the process, a 'master plan' strategy can be formulated, that will guide the process of developing the solution. Integration of existing systems, new tools, or BI Platform migration can then be tackled based on priority and available resources."

Business executives understand that it's important to have good data. They reason that good data should lead to good decisions, and good decisions mean good business. This makes sense, right? A very common scenario today is that businesses trying to get that edge will invest in expensive ERP systems that effectively gather and store mountains of customer, product, and sales information. Mission accomplished? Wrong! These days, the time between data entry and consumption is very short, almost instant. More effective data-gathering mechanisms result in data silos and data warehouses populated to the gills with all kinds of facts.

The new generation of business workers are informed and empowered to make decisions. They need tools to get useful information and respond to changes. Having data available is useless unless it has business value and can be used to effectively take informed decisions.

A fundamental fact in business is that the people who gather and collect data are often not the people who use that data or need access to the information that the data represents. Business executives, managers, and analysts make strategic decisions everyday that may affect many people, the direction of their organizations, and ultimately, the way people and organizations will go about conducting business in the industry. These decisions are largely driven by the relative height of a bar displayed in a chart or a few numbers printed on a piece of paper. Having capable reporting tools doesn't necessarily solve this problem. Most businesses don't know how to effectively use the products they own.

Continues...

---

Excerpted from Professional SQL Server Reporting Services by Paul Turley Todd Bryant James Counihan George McKee Dave DuVarney Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---