Chapter 17: Reporting Services

Overview

Microsoft first introduced Reporting Services in January of 2004 under the title SQL Server 2000 Reporting Services. Since that time it has become an award-winning product (see Intelligent Enterprise Reader's Choice award for Ad Hoc Query & Reporting, 2004). More importantly, Reporting Services has become critical to many business intelligence suite implementations. Face it, reporting is the centerpiece for consumption of business intelligence information. Sure, doing the number crunching and actual decision-making are important and not reporting related per se, but you will want to cover all the bases and complete the job right. And to accomplish that you can even do better than just create tabular reports; you can create charts that show the results in a much more appealing and easy-to-understand fashion. Related to that is the ability to embed your own custom static graphics in your reports, like your company logo, for example. Some companies out there integrate seamlessly with Reporting Services to provide additional enhancements beyond those described here; one such company is Panorama Software (http://www.panorama.com/), whose products provide drag-and-drop placement of report objects in reports for use against Reporting Services.

Even after all the cool integration discussed in the Integration Services chapter, you will see that what you are about to read here is similarly cool, if not blatantly hip. Not only can reports be built by dragging and dropping data objects onto a canvas with SQL subsequently generated behind the scenes, but you can also drag and drop Analysis Services-specific objects, like dimensions and calculated members, onto that same canvas with MDX subsequently generated behind the scenes. Using the report designer you can design reports from relational as well as multidimensional databases. Indeed, you get to use a highly integrated business intelligence reporting infrastructure that requires little more than drag-and-drop techniques to create reports.

In an effort to realize the great techniques just discussed, you learn to do the following things in this chapter: how to create a report on top of a relational database, deploy that report to the reports server, and then browse it locally or over your corpnet. You learn to create a report using an Analysis Services database from the report wizard, and further enhance your Analysis Services reporting skills by creating calculated members and using specific properties within a report. You also learn to create charts in reports for better representation of data, how to manage reports from the Reports Server front end, and finally, how to build ad-hoc reports from a UDM using the report builder capability.