Introduction to SQL Server 2000 Reporting Services
Lots of people (myself included) are excited about the coming release of Microsoft SQL Server "Yukon," the successor to SQL Server 2000. But you don't have to wait until 2005 to see one of the advances that "Yukon" is delivering to developers. Last month Microsoft released SQL Server 2000 Reporting Services, an add-on for the current version of SQL Server (it will be packaged with new copies of SQL Server 2000 later this year). In this article, I'll introduce Reporting Services and show you how you might make use of it in your own development efforts.
The Reporting Services Architecture
Reporting Services runs as a middle-tier server, as part of your existing server architecture. You need to have SQL Server 2000 installed for the database server, and Internet Information Services 6.0 as a Web server. The report server engine takes in report definitions, locates the corresponding data, and produces the reports. You can interact with the engine through the Web-based Report Manager, which also lets you manage things like refresh schedules and notifications. End users view the report in a Web browser, and can export it to PDF, XML, or Excel with the click of a button
Report designers work inside of special Visual Studio .NET projects (so make sure you count the cost of a VS .NET license when you're toting up the costs of moving to Reporting Services). Figure 1 shows the designer, which is pretty straightforward. As you can see, it handles typical "banded" reports perfectly well, but that just scratches the surface here. The designer can also create crosstab-like grids called matrixes, graphs and charts, and other interfaces. Of course you can flexibly group and sort the reports. Finally, because the reports run in a Web browser, they're not static. It's easy to enable drill-down and drill-through scenarios that allow users to retrieve additional data. Figure 2 shows a Reporting Services report open in the browser.
The designer can also import reports from Access databases, but there are some limits on this (not all Access features are supported, and you can't import just a single report), which limits the usefulness of this feature.
Designing the report is not the end of the process. A report in VS .NET is only available to the designer. In order to make the report available to other users, the developer needs to publish it to a report server.
Managing Reporting Services
The Web-based Report Manager lets you perform a wide variety of tasks with published reports. I suspect this interface will mainly be used by DBAs rather than developers. For starters, you can change the data source and security credentials of a report. This is a necessity in any organization that implements separate development and live servers; changing the credentials lets you deploy a report from a test server to the real server.
Report Manager also lets you change execution properties for any report. You may be used to thinking of reports as always being generated from current data, but that can be a problem if the database is large and a report involves a heavy load on the database server. Reporting Services offers more tunability in this area than I've seen in other products. You can:
- Always render the report on demand, from current data (the default option)
- Render the report, and then cache a copy. Serve from the cache for a fixed number of minutes.
- Render the report, and then cache a copy. Serve from the cache on a fixed schedule. For example, you can expire the report at midnight each weekday.
- Render the report on a fixed schedule, and then serve that copy until the next scheduled render.
You can also set a report timeout, so that long-running reports are killed before they use up all of your server resources.
Reporting Services features a role-based security scheme that you can control from Report Manager. You can designate users or groups into default roles of Browser, Content Manager, My Reports (can handle their own part of Report Manager, but not other users' reports), or Publisher, as well as define new roles and determine which folders and reports these roles apply to.
Finally, there's a subscription mechanism built right into Reporting Services. Using Report Manager, you can set up a new subscription to deliver fresh copies of the report via file share or e-mail. Subscriptions can produce new copies of the report on a fixed schedule. There are also data-driven subscriptions, which use information from the report itself to decide who to send copies to.
Microsoft seems to have given particular attention to extensibility in Reporting Services. Unlike some competing products, this is a very open architecture. Here are some of the key points where you can customize Reporting Services:
- You can manage a report server via SOAP.
- You can use .NET to create custom rendering components to drop into any report.
- You can export reports as TIFF, PDF, Excel, XML, HTML with Office Web Components, or other useful formats.
- You can create new data processing extensions to allow Reporting Services to draw data from new or unusual sources.
- You can create delivery extensions to allow subscriptions via media other than e-mail and file sharing.
- You can configure a report server through Windows Management Instrumentation (WMI) classes.
Overall, Reporting Services provides an extremely flexible way to make data available to end-users. The drawback to this, of course, is that learning to program and extend Reporting Services is not a trivial task. Plan on some significant developer and DBA investment if you decide to implement this tool in your own organization.
Pricing and Licensing
Finally, it's worth taking a look at the licensing scheme for Reporting Services, and the implications of this for your costs. Microsoft has posted a Licensing FAQ to help you sort these issues out, but here are the salient points. First, if you own a SQL Server 2000 license, you're licensed to run Reporting Services. That means that if you want to run Reporting Services on the same server as SQL Server, you're covered. But remember, this means that you also need to have IIS 6 installed on that server, and the server itself has to be hefty enough to handle the additional load of constructing and delivering reports, as well as its current database tasks.
Many organizations will probably find that they need to split Reporting Services off to its own server. This means buying another server, licensing SQL Server (the full product; Reporting Services doesn't work with MSDE), and of course setting everything up - costs that can easily run into four or five figures. So keep an eye on memory and CPU utilization, as well as statistics within SQL Server, as you evaluate this software for actual production use.
A Solid Solution
Despite a few tiny version 1 teething pains (I managed a non-reproducible crash in Report Manager, and some of the help content is slightly out of synch with the actual product, SQL Server 2000 Reporting Services offers an impressive and well-integrated way to create reports from SQL Server. If you're running a Microsoft-oriented shop, it's definitely worth a look. Remember, too, that if you manage to centralize key reports you may be able to eliminate or reduce ad-hoc reporting done in Access and Excel, which will have its own benefits in lowered costs and higher reliability.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his new book From Coder to Developer, published by Sybex. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.