If you're like many IT professionals, designing and managing reports isn't likely to make your list of "top 10 ways I'd like to spend my day." The continuous maintenance and modification tasks inherent in reporting can be a significant strain on already stretched resources, and you might often see such tasks as impediments to meeting higher-priority deadlines. But reporting is crucial to most organizations' overall information-management strategy and, from the users' perspective, represents the real value of all the data gathering, cleansing, modeling, and storage that you consider high-priority activities for the IT department.
Choosing the right reporting platform is essential for balancing the needs, constraints, and priorities of the technology and user communities. Several enterprise reporting platforms are available, and although they all claim to solve the same business problems, you must consider several factors when selecting a reporting platform for your needs. Architecture; cost; the usability of the report-design interface; report delivery, automation capabilities, and formats; the platform's extensibility and security; ease of administration; and performance are all essential factors that can make your decision-making process complex.
In this analysis, I compare two reporting platforms that are receiving considerable attention: the current market leader in the reporting solutions space, Business Objects' Crystal Enterprise, and the newcomer, SQL Server 2000 Reporting Services. In general, both Crystal Enterprise and Reporting Services are enterprise platforms capable of supporting most organizations' reporting needs. Each provides robust report-design and report-management tools. However, in comparing the two products, I discovered some interesting and important differences that could affect which of these two products would better meet your organization's needs.
Architecture and Cost
Crystal Enterprise's and Reporting Services' architectures are similar in that each is a collection of several server components that when combined, provide a complete reporting platform solution. However, the nature of each platform's components is distinctly different.
Crystal Enterprise has the potentially significant advantage of supporting multiple OSs as well as most common data sources. Although Reporting Services supports virtually all data sources, it requires SQL Server 2000 for its repository and runs only on the Windows OS. Therefore, for organizations that don't support the Windows OS or the SQL Server relational platform, Reporting Services isn't an option.
As Table 1 shows, Crystal Enterprise comprises 12 components: Publishing Wizard, Management Console, Configuration Manager, Import Wizard, Web Component Server and Web Connector, Automated Process Scheduler (APS), Page Server, Cache Server, Event Server, Job Server, Input and Output File Repository Servers, and ePortfolio. These components are significantly more mature and specifically purposed than their Reporting Services counterparts. These components also provide stronger support for users and designers of varying skill levels.
As Table 2 shows, pricing for Crystal Enterprise varies greatly depending on your environment and the features you want. A typical database shop can figure on starting with the base cost of $23,750 for Crystal Enterprise 9. (Business Objects tells me that its Manufacturer's Suggested Retail Price—MSRP—is $25,000 for a single-processor Crystal Enterprise 10 license.) This initial investment is high, and possibly prohibitive for some shops that don't already have a Business Objects platform.
Reporting Services, released in January, requires Microsoft server components such as SQL Server for repository storage and the .NET Framework and Visual Studio .NET for report development. These are widely used components that many organizations have already deployed before installing Reporting Services. Reporting Services is licensed as part of the SQL Server 2000 suite. So although it's not technically free, Reporting Services is a lower-cost option than Crystal Enterprise. Table 3 shows the list price for each of the SQL Server editions. Keep in mind, however, that list price is rarely the actual price your organization will pay—most organizations pay much less. When calculating the total licensing cost of SQL Server, remember that you need an additional license for Microsoft IIS, which typically is installed on a separate Web server. In addition to the licensing cost for SQL Server Reporting Services, you also need to purchase Visual Studio .NET. Any edition will do, and the most basic edition is available for as little as $99.
Licensing cost isn't the only cost you have to consider in selecting a reporting platform. You must also consider the cost of educating report designers, operations personnel, and users as well as development costs to redeploy existing reports in the new technology. And in the end, your users have to accept the reporting system before you can consider it a success. Be sure you factor in the cost of marketing the reporting solution to users. Even if you deliver the redesigned reports in a more timely manner, in a more efficient format, and at a lower cost, your users still might be hesitant to embrace them. So, you must assume some effort will be required to win over the user community.
Design Interface
Crystal Enterprise is the more mature product (Business Objects recently released version 10 of the platform) and offers Experts (i.e., wizards) to guide less technically adept report designers through the creation of simple to somewhat complex reports without the burden of writing code. The Crystal Enterprise interface is relatively intuitive and considerably less cluttered with design objects than its Reporting Services counterpart.
The Reporting Services design interface (Visual Studio .NET 2003) is much less friendly to business analysts accustomed to defining their own reports. Visual Studio .NET supports some drag-and-drop capabilities, but realistically, you can't create a useful Reporting Services report without writing some code. The Visual Studio .NET interface is developer-focused and doesn't offer much in the way of wizards to guide report-design tasks. But keep in mind that Visual Studio .NET is only the development environment; end users never see the development UI, only the report.
Ease of design, however, often comes at the cost of flexibility. For example, Crystal Enterprise uses a banded approach to report design, dividing the report into sections (bands) containing such information as header, footer, or detail data. Although this design environment provides built-in features such as grouping, paging, and subtotaling within bands, creating free-form reports that include these features across multiple bands is difficult. Reporting Services employs a report-page concept that allows considerably more design flexibility. Any component, including subtotals and charts, can appear anywhere on the report. This design approach, however, places more responsibility on the designer to control report behavior.
Microsoft's decision to use Visual Studio .NET as the development environment for Reporting Services has created considerable discussion among report designers and organizations that want to define the skill set and responsibilities of reporting personnel. To less-technical report designers, Visual Studio .NET feels more like a programmer's interface. So for organizations in which business users play the report-developer role, designing in Reporting Services might be a challenge. For technically savvy report designers, however, Visual Studio .NET provides an almost endless array of extension and customization possibilities. The flexibility and extensibility of Reporting Services is a significant advantage for designers who have strong technical skills and are willing to do the development work. But there's the rub: The report-developer role requires business knowledge along with technical expertise. Finding the right balance of these skills, particularly in the same person, is a challenge, and companies often have to sacrifice one skill for the other.
Report Delivery
Once you've designed a report, you have to deliver it to users either by pull (users access the report when they need it) or push (the platform automatically sends users the report according to a calendar schedule or data event) methods. Both Crystal Enterprise and Reporting Services provide push and pull report delivery, though I discovered some interesting differences.
Crystal Enterprise supports user access to reports (pull) through its ePortfolio utility. ePortfolio is a Web application included with Crystal Enterprise that's essentially the counterpart to Reporting Services' Report Manager. Like Reporting Services, Crystal Enterprise lets you use subscriptions to deliver (push) reports to users according to an administrator-defined schedule. But unlike Reporting Services, ePortfolio lets users access alert definitions that deliver reports based on changes in data conditions. You can develop this functionality for Reporting Services as well, but implementing it would require some fairly complex code development.
As with Crystal Enterprise, users can pull Reporting Services reports directly through a URL. Users can also receive regularly occurring reports by means of subscriptions that report administrators define in Reporting Services. Additionally, Reporting Services supports data-driven parameters, which let you target reports for specific users and ensure data security by controlling such factors as report parameters, recipient lists, and rendering options. (For information about how parameters work in Reporting Services, see Rodney Landrum's article "Pushing the Parameters," August 2004, InstantDoc ID 43139.)
Microsoft provides a sample HTML application, Report Manager, which exposes all the report-administration and viewing functionality of Reporting Services. Although the Report Manager is fully functional and you can use it by simply opening it in a browser window, most organizations will likely use Report Manager as a template from which to develop a customized, corporate-branded administrative interface that's consistent with existing intranet or Internet applications.
Both platforms support multiple rendering options, but Crystal Enterprise supports more report formats. A key difference between the two platforms is the ability—or inability—to print usable hard copies of reports directly from HTML. Crystal Enterprise supports printing directly from HTML; in Reporting Services, you have to render the report into a more printable format such as Microsoft Internet Explorer (IE), Microsoft Excel, or PDF. Current Crystal Enterprise users might perceive this requirement as a functional step backward.
Also, the rendered versions of reports in both Reporting Services and Crystal Enterprise can be difficult to predict. The report design determines what the rendered version of the report will look like to some degree, but you need to understand that the amount of data rendered and the functionality the rendered format supports might result in a report that looks different than you expect. In most cases, for example, rendering is a what-you-see-is-what-you-get (WYSIWYG) proposition; data that's within the drill path of the report but isn't visible on screen won't be exported. To get the data you want in your report, you need to understand the behavior of the rendering function you're using. Table 4 shows a list of a few of the rendering formats that each platform supports.
Extensibility
Because of its server-component integration, Reporting Services is highly extensible—an important aspect of Microsoft's strategy. Extensibility lets developers, Independent Software Vendors (ISVs), and OEMs create fully customized interfaces, functionality, and solutions around the Reporting Services platform. You can create data extensions to access data from any source; create security extensions to integrate with any security system; and through the Web service API, create fully customized portals to expose as much or as little server functionality as you want.
Reporting Services stores report definitions as files in Report Definition Language (RDL)—an XML derivation—in the reporting server repository, a SQL Server database created during the installation of Reporting Services. Because RDL is an XML data type, it's fully extensible, so any custom application can create, access, and update RDL within the repository.
Crystal Enterprise also lets you extend its functionality through its software development kit (SDK). The Crystal SDK supports report development in multiple development environments including COM, Java, and .NET. Like Reporting Services, Crystal Enterprise stores report definitions and objects in a common repository. Unlike Reporting Services, Crystal Enterprise stores these objects as proprietary binary data types. Although manipulating or generating RDL isn't easy for most developers, storing the report definition in RDL instead of proprietary binary types makes the Reporting Services environment easier and less costly to modify than Crystal Enterprise.