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
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
Reporting Services was code-named Rosetta during its internal development at Microsoft. This
The Rosetta project was originally conceived as a feature of SQL Server 2005. However, as Microsoft told
Just what are the features of Reporting Services that got everyone so excited?
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
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
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
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.
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
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
One more fact to keep in mind: Your company’s inventory system is in Cleveland, but the production facility is in Portland!
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 Vice President of Sales has
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
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
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
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
Let’s look for another option!
Because the capability to control the printed output is important, Adobe PDF should be
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
Perhaps there is a better way.
Reporting environments from other companies
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.
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.
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
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.
Reporting Services includes a report viewer that works with your browser. This
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.
In addition to presenting reports in your browser using dynamic HTML, Reporting Services can
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
When Reporting Services
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.
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.
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
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!