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.")
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.
Security
The reporting platform you choose must meet your organization's security requirements, and you must be able to implement and maintain the security solution with a reasonable amount of effort. Every organization has its own specific report-security requirements, but most security needs fall into two categories: report security and data security. Report security defines which users or user groups can access which reports. Data security protects the data that populates those reports. Of the two security types, data security is often more complex and more difficult to administer.
For report security, Crystal Enterprise supports multiple security models, including table-based Lightweight Directory Access Protocol (LDAP) and Windows NT. Reporting Services uses a role-based model in which user roles control access to reports and data. User roles are associated with one or more Windows users or groups. All other security models in Reporting Services require the development of custom security extensions. Security extensions are essentially custom modules that integrate with file- or session-based authentication schemas that provide a UID to the Report Server. This UID interacts with the defined roles in the same way as it interacts with Windows users.
In both platforms, data security is part of report design rather than report administration. By using either the Windows UID or the UID passed in from an alternative security system, you can include in the data-source query a filter based on the data set available to the current user. This feature raises a couple of interesting points. First, for organizations generating reports for large numbers of non-Windows users (e.g., clients or vendors), an anonymous or group user authentication might seem a good strategy for report security but might become a data-security problem. The reporting platform can't effectively filter data unless all members of the shared UID have access to identical data sets. Second, each reporting platform offers data filtering at two levels: data source and data set. Data-source filtering defines the population of the report query (e.g., the data that makes up the query result set). Data-set filtering occurs after the query executes and returns a subset of the query data. Thus, you can generate multiple UID-specific reports from one common query, resulting in less query traffic to the database and potentially significant performance gains. Regardless of which platform you use, the ability to manage access to data is essential to security as well as performance. Although the specific security implementations are different in Crystal Enterprise and Reporting Services, both platforms support these security functions.
Administration
In Reporting Services, Microsoft includes the Report Manager interface for access to administrative tasks such as data-source management, report security, subscription management, and scheduling. Data-source management includes the ability to edit data-source connection definitions for all reports that you deploy to the server. Maintaining the data-source connection definition in the administrative environment rather than the development environment creates a level of abstraction between the report-designer and report-administrator roles. This abstraction lets the report developer work independently of the production environment, relieving potential security and deployment management concerns. Data-source management also includes the ability to define shared data sources that allow for single-point management of data connections across several reports. Figure 1 shows Reporting Services' report-administration interface.
By using native Windows security integration or a custom security extension, Reporting Services' report security lets the report administrator grant or deny users or user groups access to individual reports or report groups. Subscriptions in Reporting Services let you schedule report delivery to email recipients or as files delivered to file shares. And the subscription functionality supports the use of parameters to target the report data to specific users' needs. When you deliver reports by email, you can send them as hyperlinks or as fully generated reports. In addition, Report Manager functionality is exposed in the Web service API, which means you can develop a fully customized management interface.
Administration of Crystal Enterprise reports reflects the product's more complex architecture. The primary interface to report administration is the Management Console, which Figure 2 shows. However, access to some of the platform's important functionality is available only through the module utility interfaces for specific capabilities. For example, a scheduling utility supports report scheduling, and a configuration utility provides access to Crystal Enterprise servers and server components. Like Reporting Services, Crystal Enterprise can deliver reports based on events or a calendar schedule. Also like Reporting Services, Crystal Enterprise lets users who have appropriate permissions schedule their own reports.
Monitoring Performance and Usage
Small organizations, particularly those that need reports primarily for internal use, can dedicate relatively less energy to tracking and tuning reporting performance than larger organizations that might need to generate reports for people outside the organization. Although smaller organizations can monitor performance by communicating directly with the handful of users who receive reports, larger organizations might have hundreds or thousands of reporting users. To monitor and optimize performance across a community of this size, you need an effective system for monitoring report performance with good tools for administering the reporting platform.
The Crystal Enterprise Management Console is a Web-based tool that gives report administrators access to most server objects (e.g., users, groups, reports) as well as some monitoring of report-usage metrics such as query frequency, duration, and execution time and date. The level of detail available in the Management Console isn't as granular as some administrators might like, but you can add detail by using the administration API to customize the report. For most organizations, Crystal Enterprise's out-of-the-box monitoring capabilities are adequate, and for those who need extended capabilities, the supplied capabilities are a good place to start.
Reporting Services doesn't currently ship with a monitoring capability that's sufficient to satisfy many organizations' needs. As with other Reporting Services functionality, you can develop a custom application for performance monitoring. To understand how to create a historical monitor of report-execution activity, you can start with the collection of SQL scripts and the Data Transformation Services (DTS) package that Microsoft supplies on the SQL Server CD-ROM.
Choosing Wisely
So which of these reporting platforms is better? The answer is a resounding, "It depends." Both platforms are viable solutions to most organizations' reporting needs. The only "show-stopping" difference is that organizations that require the report server to run on a non-Windows server can't use Reporting Services.
Aside from the OS requirement, choosing the right platform comes down to evaluating your organization's reporting needs and strategies against the relative strengths and weaknesses of each platform. Crystal Enterprise is a more mature product and provides more business-userfriendly features. But the trade-off for this user-friendliness comes at the cost of customization and control capabilities that hard-core report designers strongly desire. Reporting Services provides excellent customization and extensibility but at less than a year old, it's the less mature product and is a "build-it-yourself" environment that requires more initial investment in the redeployment effort as designers learn the technology and build replacement reports that users will accept.
The initial cost of licensing Crystal Enterprise is significantly higher than that of Reporting Services. However, depending on how you count it, the Crystal installation base is huge—typically somewhere near 20 percent of all reporting and analysis applications—so it's likely that a Business Objects license of some kind is already in place in most midsize to large organizations. Although this fact doesn't necessarily translate into reduced licensing costs, significant savings are available to organizations that are simply upgrading from earlier releases. An equally important consideration is the cost of retraining and retooling. Changing platforms can be quite expensive, so you need to figure out the time required to recover your investment. Even given its large lead, however, Business Objects has to be concerned about the continued market share growth of Microsoft's SQL Server suite. Microsoft's product positioning—bundling Reporting Services, DTS, and Analysis Services as part of the SQL Server license—makes every company using SQL Server a potential Reporting Services client. Meeting reporting needs without purchasing any additional licensing is a compelling option that will be more compelling as Reporting Services' capabilities mature.
Neither Microsoft nor Business Objects is likely to sit on its platform laurels, and although both companies continually restate their commitment to cooperation, it's clear that competition for the reporting-platform space will drive future releases of these products toward better feature sets. Business Objects owns the reporting-solutions space today, but Microsoft's use of common components in Reporting Services should let the company quickly build functionality or integrate third-party capabilities that will make higher-cost solutions hard to justify.
