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 very 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 prints 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 that 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 that 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 the 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, 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 very 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:00 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:00 A.M. As you search for solutions to this one, remember that there is no budget 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 that these requests are easy to fulfill.

An HTML Solution

The first candidate to explore when 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 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 that the information must be printable. HTML screens can look great in a browser window but 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 ability 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 will 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. In order 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

There are certainly reporting environments from other companies that 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 got 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 CEO. 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 environment for creating reports from database information. This report-authoring tool is contained within Microsoft Visual Studio .NET 2003. Even though this is the same integrated development environment used by programmers to create Visual Basic .NET and C# applications, no programming is required to create reports.

Later in this book we will look at some simple Visual Basic expressions that can be used to spice up your report’s presentation. Note, however, that these expressions are not necessary to create useful reports. They are also simple enough that even those who are totally new to Visual Basic will be able to master them with ease.

A Server with a View

Reporting Services includes a report viewer that works with your browser. The 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.


When Reporting Services renders a report, it gathers the most recent data from the database, formats the data in the manner that 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, a report can be configured to requery the database every time it is accessed. This ensures that 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 allows users to access reports via the Internet. It also includes security features that 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.

The Price Is Right

For anyone who has a licensed copy of SQL Server 2000, the price of Reporting Services is certainly right. Free! You will need to purchase a copy of Visual Studio .NET 2003 for creating reports, if you do not already have one. However, once you have obtained this, it will not cost you a 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 that only sales representatives with the appropriate user name and password can access the report.

Finally, 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 may not look good on paper. In order 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 it 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 that the subscription service delivers the report in either PDF or TIFF format. The best part is that, because you already have a copy of Visual Studio .NET 2003 for other development, 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!

Microsoft SQL Server 2000 Reporting Services
Microsoft SQL Server 2000 Reporting Services Step by Step (Pro-Step by Step Developer)
ISBN: 0735621063
EAN: 2147483647
Year: 2003
Pages: 109 © 2008-2017.
If you may any questions please contact us: