Category list

Part I: Getting Started

Chapter List

Chapter 1: Lets Start at the Very Beginning
Chapter 2: Putting the Pieces in Place—Installing Reporting Services

Chapter 1: Let’s Start at the Very Beginning

Overview

SQL Server 2000 Reporting Services was Microsoft’s entry into the web-based reporting arena. This first version of Reporting Services enabled you to easily share business information—what is commonly known as “business intelligence” these days—with management, coworkers, business partners , and customers throughout the world. In an interconnected workplace, it makes sense that your reporting solution should offer company-wide, nationwide , and even worldwide communication.

SQL Server 2005 Reporting Services builds on the success of the original. Where almost every other aspect of SQL Server 2005 represents a completely new platform, Reporting Services adds to the solid foundation provided by the earlier version to make a great product even better. The 2005 release provides an additional report-authoring environment, improved report-development features, and enhanced capabilities for distributing reports .

Reporting Services was code-named Rosetta during its internal development at Microsoft. This name comes from the Rosetta Stone, a stone slab found in 1799 that contains an inscription in both Egyptian hieroglyphics and Greek. This stone provided the key piece of information necessary to unlock the mystery of Egyptian hieroglyphics for the modern world. Just as the Rosetta Stone brought key information across 1,400 years of history, Rosetta, or Reporting Services, is designed to bring key information across distances to unlock the mystery of success for your business.

The Rosetta project was originally conceived as a feature of SQL Server 2005. However, as Microsoft told prospective customers about the features in Rosetta and demonstrated the first alpha versions, the reaction was strong: “We need this product and we need it now!” Because of this reaction, Microsoft decided that Rosetta would not wait for 2005, but, instead, would be made its own product to work with SQL Server 2000.

Just what are the features of Reporting Services that got everyone so excited? Reporting Services provides an environment for creating a number of different types of reports from a number of different data sources. The reports are previewed and refined using this authoring tool. Once completed, the reports are deployed to a Report Server, which makes the reports available via the Internet in a structured, secure environment. Last, but not least, the report management and distribution portion of Reporting Services is free of charge to anyone with a SQL Server 2000 or SQL Server 2005 license.

Why did this set of features generate so much excitement? When you put them all together, the result is a product that facilitates the creation, management, and timely use of business intelligence.

Sharing Business Intelligence

Because you are reading this book, you are probably the keeper of some type of information that is important to your organization. You may have information on sales, finance, production, delivery—or one of a hundred other areas. All this information makes up the business intelligence necessary to keep today’s corporate, academic, and governmental entities humming along.

The Need to Share

In addition to maintaining this information, you also have a need to share this information with others. This need to share may have come from an important lesson you learned in kindergarten (“The world would be a much happier place if we all learned to share”) or, more likely, this need to share your information was probably suggested to you by a manager or executive somewhere higher up the food chain. See if any of these situations sounds familiar.

The Production Manager

Your company’s order-entry system automatically updates the inventory database every four hours. In your company’s line of business, some orders can require a large quantity of a given product. Because of this, it is important that the Production Manager knows about these changes in the inventory level in a timely manner, so he can adjust production accordingly .

The Production Manager has asked you to provide him with an up-to-date inventory report that is created immediately following each update to the inventory database occurring during business hours. He would like this report to arrive on his PC as quickly as possible, so he can make changes to the production schedule within an hour of the updates. He would also like to be able to print this report, so he can add his own notations to the report as he works out his new production schedule.

One more fact to keep in mind: Your company’s inventory system is in Cleveland, but the production facility is in Portland!

The Vice President of Sales

You are responsible for maintaining information on the amount of credit your company will extend to each of its clients. This information is updated daily in the company database. A report containing the credit information for all clients is printed weekly at corporate headquarters and mailed to each sales representative.

The Vice President of Sales has requested that the credit information be made available to the sales staff in a timelier manner. He has asked that this report be accessible over the Internet from anywhere across the country. The sales representatives will print the report when they have access to the Internet, and then carry it with them for those times when they cannot get online. He has also asked that this online version of the report be as up-to-date as possible.

The Chief Executive Officer

The Chief Executive Officer for your company has a hands-on management style. She likes to participate in all facets of the decision-making process and, therefore, needs to stay well informed on all aspects of the company. This includes the corporate balance sheet, inventory and production, and the company’s stock price.

The CEO expects all this information to be available on her desktop when she arrives for work each morning at 7 A.M. The information must be in a format that’s appropriate to print and share with the corporate vice presidents at their meeting each morning at 9 A.M. As you search for solutions to this one, remember no budget is allocated for this project—and, of course, your job is on the line.

Possible Solutions

These situations, and a thousand others just like them, confront businesses each day. In our world of massive connectivity, these types of requests are not unreasonable. Even if that is the case, it does not mean these requests are easy to fulfill.

An HTML Solution

The first candidate to explore when you’re looking to move information across the Internet is, of course, HTML. You could use one of a number of tools for creating data-driven HTML pages. This would include Microsoft’s Active Server Pages, Macromedia’s ColdFusion, any of a number of Java environments, PHP—the list goes on and on.

Each of these environments is good at creating dynamic web content. However, they all take time and a certain level of programming knowledge. With deadlines looming, you may not have the time to create custom web applications to solve each of these problems. If you are used to manipulating data with Crystal Reports or Access reporting, you may not be ready to jump into full-blown application development and you may not have a desire to do it at any time in the near future.

Even if you did create an application for each of these scenarios, one important requirement in each case is this: the information must be printable. HTML screens can look great in a browser window, but they cause problems when printed. The content can be too wide to fit on the page, and there is no control of page breaks. In fact, the page can break right in the middle of a line of text, with the top half of the characters on one page and the bottom half of the characters on the next ! These types of formatting issues could make the output difficult for the sales representatives and the Production Manager to read. Asking the CEO to take this type of a report to the executive meeting could get you fired .

Let’s look for another option!

A PDF Solution

Because the capability to control the printed output is important, Adobe PDF should be considered . PDF files look good both on the screen and in print. You can control where the page breaks occur and make sure everything looks great. However, several issues need to be overcome with PDF files.

First of all, you need some type of utility to produce output in a PDF format. This could be Adobe’s full version of Acrobat or some other utility. Once this has been obtained, a document must be created that contains the desired database information. This is usually a report created with a reporting tool or development software. After this document is created, it is converted into a PDF document using an export function or a special printer driver.

Once the PDF document has been created, it can be copied to a website for access through the Internet. However, as soon as the PDF document is created, it becomes a static entity. It does not requery the database each time it is requested from the website. To remain up-to-date, the PDF document must be re-created each time the source data is changed. In addition, you may have to return to your programming environment to control access to the PDF documents on the website.

Perhaps there is a better way.

A Third-Party Reporting Environment

Reporting environments from other companies certainly overcome the limitations of our first two options. These third-party products allow reports to be built without requiring large amounts of programming. They can also dynamically generate output in a format such as Adobe PDF that will perform well onscreen and in print.

The problem with third-party reporting environments is the cost. Some products can run into the thousands or tens of thousands of dollars. This can be enough to break the budget—if indeed there is a budget—for reporting projects such as the ones discussed previously.

Microsoft Reporting Services

Now you can begin to see why companies get so excited about Reporting Services. It provides an elegant solution for all three of your demanding users—the Production Manager, the Vice President of Sales, and the Chief Executive Officer. Reporting Services does not have the drawbacks inherent in the possible solutions considered previously.

No Programming Required

Reporting Services provides a simple, drag-and-drop approach to creating reports from database information. You can use two different tools to author reports. The Report Builder enables you to create basic reports even if you do not know much about databases and query languages. The Report Designer lets you truly unlock the power of Reporting Services to convey complex information. The Report Designer is found in the Business Intelligence Development Studio, which comes with SQL Server 2005, and also in Visual Studio 2005, Professional version and above.

Even though these are both development environments, Business Intelligence Development Studio for developing SQL Server Analysis Services and data mining solutions and Visual Studio 2005 for creating software based on the .NET Framework, don’t let that scare you. You do not need to be a programmer to create Reporting Services reports. However, if you are comfortable with programming constructs, in Chapters 7 and 8, you learn some simple Visual Basic expressions that can be used to spice up your report’s presentation. Note, however, these expressions are not necessary to create useful reports. They are also simple enough that even those who are totally new to Visual Basic can master them with ease.

A Server with a View

Reporting Services includes a report viewer that works with your browser. This report viewer provides a high-quality presentation of each report using dynamic HTML. Reports are presented in multiple pages with “VCR-button” controls for navigating between pages.

Because the report viewer uses dynamic HTML, it does not require any additional programs to be downloaded on your PC. There is no ActiveX control to install, no Java applet to download. Any browser that supports HTML 4.0 can view reports.

Plays Well with Printers

In addition to presenting reports in your browser using dynamic HTML, Reporting Services can render a report in a number of additional formats. These include Adobe PDF, TIFF, and even a Microsoft Excel spreadsheet. All these formats look great onscreen when they are viewed or on paper when they are printed.

Note

When Reporting Services renders a report, it gathers the most recent data from the database, formats the data in the manner the report’s author specified, and outputs the report into the selected format (that is, HTML, PDF, TIFF, and so on).

Even when being output in the PDF or TIFF format for printing, a report can be configured to requery the database every time it is accessed. This ensures the report is always up-to-date.

Special Delivery

Reporting Services provides several different ways to deliver reports to end users. The Report Manager website enables users to access reports via the Internet. It also includes security features, which ensure that users access only the reports they should.

Users can also subscribe to reports they would like to receive on a regular basis. Reporting Services will send out a copy of the report as an e-mail attachment to each subscriber on a regularly scheduled basis. Alternatively, a Reporting Services administrator can send out a copy of the report as an e-mail attachment to a number of recipients on a mailing list. If that isn’t enough, reports can be embedded right in .NET applications.

The Price Is Right

For anyone who has a licensed copy of SQL Server 2005, the price of Reporting Services is certainly right. Free! As long as the report server is installed on the same computer as the SQL Server database engine, your SQL Server 2005 license covers everything. With this single server architecture, it will not cost you one additional penny to share your reports with others using Reporting Services.

Reporting Services to the Rescue

Let’s take one more look at the three scenarios we considered earlier—the Production Manager, the Vice President of Sales, and the Chief Executive Officer. How can you use the features of Reporting Services to fulfill the requests made by each of them?

The Production Manager wants a report showing the current inventory. It is certainly not a problem to query the inventory data from the database and put it into a report. Next, he wants to get a new copy of the report every time the inventory is updated during business hours. The Production Manager can subscribe to your inventory report and, as part of the subscription, ask that a new report be delivered at 8:15 A.M., 12:15 P.M., and 4:15 P.M. Finally, the inventory system is in Cleveland, but the Production Manager is in Portland. Because a subscription to a report can be delivered by e-mail, the Reporting Services server can be set up in Cleveland, produce the report from the local data source, and then e-mail the report to Portland.

The solution for the Vice President of Sales is even more straightforward. He wants a report with credit information for each client. No problem there. Next, he wants the report available to his sales staff, accessible via the Internet. To achieve this, you can publish the report on the Report Manager website. You can even set up security, so only sales representatives with the appropriate user name and password can access the report.

In addition, the Vice President of Sales wants the report to look good when printed. This is achieved with no additional work on the development side. When the sales representatives retrieve the report from the website, it is displayed as HTML. This looks good in the browser, but it may not look good on paper. To have a report that looks good on paper every time, the sales representatives simply need to export the report to either the PDF or TIFF format, and then display and print the exported file. Now they are ready to go knocking on doors!

For the CEO, you can build a report or, perhaps, a series of reports that reflects the state of her company. This will serve to keep her informed on all facets of her business. To have this available on her desktop at 7:00 A.M., you can set up a subscription that will run the reports and e-mail them to her each morning at 6:15 A.M.

Finally, because she wants to print this report and share it with the corporate vice presidents, you can make sure the subscription service delivers the report in either PDF or TIFF format. The best part is that, because you already have a SQL Server 2005 license, the Reporting Services solution costs the company nothing. You have earned a number of bonus points with the big boss, and she will make you the Chief Information Officer before the end of the year!