Hide Notes 

Slide 1: SQL Server 2000 Reporting Services – An Overview Nick Wienholt Following on the heals of SQLXML, the SQL Server 2000 Web Services Toolkit and SQL Server 2000 Notification Services, SQL Server 2000 Reporting Services is an add-on component for SQL Server 2000 that comes free-of-charge for existing licensees. SQL Server 2000 Reporting Services is an important release for Microsoft, and represents a continuing move up the software stack into the Business Intelligence (BI) market. In a special media briefing, Microsoft Australia’s Database Product Marketing Manager Terry Clancy gave Australian Developer a comprehensive once-over of Reporting Services and the opportunities that it offers to developers and IT professionals SQL Server 2000 Reporting Services is a surprise release in many ways. With the rapid approach of the first beta of the next version of SQL Server, which is code-named Yukon, untangling Reporting Services from the Yukon dependency web and making it available for SQL Server 2000 is a real accomplishment. Reporting Services also represents a significant increase in participation in the BI market for SQL Server and Microsoft, and places SQL Server in head-to-head competition with a number of established players in the BI and reporting market, particularly Crystal Decisions (makers of Crystal Reports), which was recently acquired by Business Objects. Reporting Services is being positioned as a complementary product to existing reporting offerings, and third-party tools like Crystal Reports, ActiveReports from Data Dynamics and Report Sharp-Shooter from 9Rays.NET will continue to satisfy particular market needs. A limited version of Crystal Reports is planned for the next version of Visual Studio.NET (codenamed Whidbey), and ActiveReports, which is the number one selling component on ComponentSource, will still be required for stand-alone applications that require royalty-free reporting functionality. Before diving into the technical aspects of SQL Reporting Services, it is worth covering cost and licensing issues upfront. SQL Reporting Services will be available free-of-charge for customers with a valid SQL Server 2000 license (there may be a small media redistribution cost depending on the distribution channels that a customer users), and the licensing model of Reporting Services is the same per-processor or per-client model as SQL Server (i.e. If you have a license to run SQL Server on a dual processor machine, SQL Reporting can be run on the same dual processor machine). One of the more important elements of the license conditions is that Reporting Services must be run on the same physical machine as the SQL Server instance that is being used for licensing purposes, which means that if the database server is operating at or near peak capacity and Reporting Services needs to be run on a separate machine for load-distribution purposes, a new SQL Server license is required. While the costs of a new SQL Server license may be acceptable, it is critical for project success and harmony that these issues are sorted out up front, and the cost of the project, along with deployment topology, is defined with a reasonable degree of accuracy. Product Vision During the Launch event, Microsoft highlighted that, in their vision, SQL Reporting
Slide 2: Services was much more than a server add-on product, and represented an important element in their Business Intelligence (BI) suite. Microsoft’s BI vision is: “Improving organisations by providing business insights to all employees leading to better, faster, more relevant decisions” (italics present in original.) To deliver on this vision, Microsoft has added BI capabilities and support to a range of products, and with Report Services, the BI building block offering, which spans from operating system to client application, is complete. Figure 1, which was presented by Microsoft during the launch event, shows where SQL Reporting Services fits into the overall BI platform offered by Microsoft. As the focus of this article is development issues, it is worth calling out the Visual Studio.NET column that runs from top to bottom of the BI stack. The column indicates that developers can build their applications upon the tier of the BI stack that makes sense for their particular task. Reporting Services, which sits towards the top of the stack, offers a great range of pre-built functionality that developers can leverage, but this functionality is targeted towards a specific task, which may be restrictive for certain scenarios. Figure 1. Microsoft BI Product Suite The relationship between SQL Analysis Services and SQL Reporting Services is interesting, and the two components offer functionality this is overlapping is some respects. Microsoft has positioned Analysis Services as a tool for dedicated data analysts who want fine-grained control over data views, and who actively want to “pull” information out of a data model as required. In contrast, Reporting Services is being
Slide 3: positioned as the tool to develop “pushed” information parcels out to information consumers that are only interested in a particular view of the data and who do not require a high level of control over data representation. There is an obvious overlap of functionality, and a complex Reporting Services application that supports advanced filtering will be similar to a simple Analysis Services application. Microsoft is positioning Reporting Services as their tool-of-choice for enterprise reporting needs. Heavy emphasis is being placed on the ability to design and develop reports that provides information to all employees in an organisation, from the executive team through to customer-facing staff that need targeted information relevant to their particular job. In addition to providing reporting functionality to groups at various hierarchies in an organisation, there is emphasis on providing reporting across the various departments in an organisation, including sales, marketing, HR, finance, manufacturing and customer services. Architecture With this rather lofty vision that Microsoft has developed for Reporting Services, it is worth drilling down into the technical details of how they hope to achieve this vision. The starting point for the drill-down is the SQL Reporting Services architecture, which is illustrated in Figure 2. Figure 2. SQL Reporting Services Architecture
Slide 4: Data and Security The most important point illustrated in Figure 2 is the data source options, which are displayed on the left side of the image. SQL Reporting Services is NOT confined to reporting on SQL Server data, and supports reporting on virtually any form of data available. The ability to report on heterogenous data stores is a key component is realising the vision that Microsoft has laid out, and is also a critical features for most enterprises, which have there corporate data spread over a wide range of backend systems. Reporting Services uses SQL Server as the back-end store for report metadata, which includes report definitions, subscriptions, schedules and users. Given that the licensing requirements of Reporting Services requires a valid SQL Server license, the storage of metadata inside SQL Server is a relatively minor implementation details that has no real significance from an architectural viewpoint. The range of data sources that have in-built support is quite varied, with SQL, OLE DB and Oracle all supported. Most propriety data stores have an ODBC or OLE DB interface, and the development techniques for developing these providers have been available for a decade or more. If a report needs to be developed against a propriety data store, a Reporting Services Data Extension can be developed, and a proof-of-concept sample that shows a Data Extension that allows a file system to be viewed as a data store ships with the product. The number of data stores that do not provide a standard API for accessing them is reasonably small, and it is unlikely that a developer will be called upon to develop a data extension. My discussed on database security articles, which appeared in Issues 13 and 14 of Australian Developer, highlights that security is a vitally important element of any data management or delivery system. Reporting Services was developed under Microsoft’s Trustworthy Computing Initiative, and, as would be expected, the security settings on the product are both prevalent and on by default. Reporting Services is reasonably insistent during the installation process that the administration website is run on a virtual directory with SSL enabled, and a dire warning is displayed if SSL is not enabled. The Reporting Service administration website allows reports to be viewed in a secure manner, but using this site as the main report viewing front-end in not recommended. Windows Authentication is the primary security mechanism for the administration website, and this doesn’t scale well in an internet deployment scenario. Building an ASP.NET website that uses Forms authentication, and then using web services to communicate to retrieve report data is the preferred architecture. Custom authentication and authorization plug-ins can be used, and Reporting Services fully supports extensibility for security settings. Rendering and Delivery Reporting Services supports a rich and extensible range of delivery targets and output forms. This is to be expected in an enterprise reporting offering, and the out-ofthe-box options will be sufficient for the majority of reporting needs within most organisations. Figure 2 shows the output format and delivery targets that ship with Reporting Services. Developers will always demand extensions points, and the ability to deliver reports to any medium in any format is an important element for Microsoft’s
Slide 5: vision of ubiquitous access to relevant data for all members of an organisation. The commitment to extensibility is not a vague promise and a difficult-to-find shipped-separately-on-request unsupported SDK. Reporting Services is implemented using the .NET Framework, highlighting both Microsoft’s ongoing commitment to .NET and the ability of .NET to produce high-performance, scalable enterprise solutions. Using the .NET Framework makes extensibility much easier than Win32 and COM-based extension APIs that where often difficult to use for developers not using Microsoft compilers. For delivery extensions, the relevant interface is Microsoft.ReportingServices.Interfaces. IDeliveryExtension, which is contained in the Microsoft.ReportingServices.Interfaces assembly that ships with Reporting Services. To provide assistance to developers writing a delivery extensions, a sample application showing the full implementation of a printer delivery extensions also ships with the main Reporting Services install, and contains over one thousand lines of sample code. The sample itself is unsupported, but is provided ‘as-is’, which allows organisations that which to use printer based delivery to compile and use the sample in production systems. The output format extensibility is facilitated by interfaces in the Microsoft.ReportingServices.ReportRendering assembly. IRenderingExtension is the main interface required to implement a rendering extension, and there are a number of assemblies that ship with Reporting Services that contain types which implement these interfaces (Microsoft.ReportingServices.CsvRendering , Microsoft.ReportingServices.ExcelRendering, Microsoft.ReportingServices.HtmlRendering, Microsoft.ReportingServices.ImageRendering, Microsoft.ReportingServices.XmlRendering). There is no source code sample for extensions renders, but the Intermediate Language (IL) in the out-of-the-box renders in not obfuscated, and tools like Reflector (http://www.aisto.com/roeder/dotnet/) can be useful if a custom rendering is being developed. It is worth noting that the renders and delivery providers that ship with Reporting Services are reasonably complete, and using a customised process based on one of out-ofthe-box components will generally be more cost effective than starting from scratch with IRenderingExtension or IDeliveryExtension. For example, if a tab-delimited report that needs to be sent to a serial port for display on a legacy LED display unit is required (a far-flung but not implausible example), using the standard XML renderer and file share delivery provider coupled with a custom Windows service that uses the FileSystemWatcher type to receive notification of new reports, an XSL Transformations (XSLT) to produce the tab-delimited format and finally a third-party component (or Visual Studio.NET Whidbey when it comes out) to write to the serial port will be a better option that writing custom renders and delivers. Display Options and Administration Continuing the circumnavigation of the Reporting Service peripherals shown in Figure 2, the main interface elements are shown at the top of the figure. For online report viewing via a browser, Reporting Services has an extensive range of display options that use the rendering elements discussed above to provide on-demand report generation. Basic formats like HTML and PDF can be used to provide browser-based delivery of reports to users on all platforms, while advanced options like HTML with Office Web
Slide 6: Components can be used to provide interactive report preparation. For enterprise users that already spend a significant amount of timing using a line of business application, report viewing through a browser is not the optimum experience, and integration with a Windows Forms (or MFC or VB6 or Delphi or Swing …) application will be required. Reporting Services supports this scenario through web services, which means that the range of client applications that can be used to display reports is essentially unlimited. The mechanism used by the desktop application to display the rendered report is entirely up to the developer – a ready-to-display format like HTML that can be displayed in an embedded browser control, or a raw format like XML can be used to build a DataSet object which can then be bound to a DataGrid control. The application can also use a format like XLS or CSV, which can be loaded by office productivity applications like Excel, where further analysis and manipulation can occur. Report Administration Reporting Services supports a wide range of administrative options through an ASP.NET website installed on the local web server. The Report Manager website supports management of report metadata (name, description, connections, credentials and parameters), report scheduling, execution properties (reports can be generated during quiet periods on the server and cached for subsequent viewing) and also includes a log of report executions. Report Manager supports role-based security based on Windows roles and Integrated Windows authentication, so different departments in an organisation can control particular reports, and different users in a particular department can control various aspects of a particular report. The authorization model has enough fine-grained control to allow the Report Manager website to be used as a viewing platform for endusers of reports, but, as stated in the section on security architecture, this is not the recommended solution for larger Reporting Services deployments, and the use of a custom ASP.NET website is recommended. An ASP.NET viewer control sample ships as part of Reporting Services, and this can be used to easily accommodate reports in existing web applications. Conclusion SQL Server Reporting Services offers a rich platform for enterprise reporting needs. Reporting Services offers a wide array of report delivery and formatting options that will allow most organisations to reports get up and running quickly. Extensibility of the inbuilt delivery and formatting options means that organisations with special requirements can customise Reporting Services to their particular needs. Reporting Services uses SQL Server as the store for its report metadata, but can consume report data from a wide range of sources, including SQL Server, Oracle, ODBC and OLE DB. Custom data sources are also supported, and can be written in any .NET Framework-compatible language. Astute readers will notice that one critical element has not been covered in this article – the actual authoring of Reporting Service Reports. Report authoring is a complex topic, and rather than providing a superficial overview, we’ll be back next month to drill into report authoring in detail.
Slide 7: Further Information: Microsoft SQL Server 2000 Reporting Services website (http://www.microsoft.com/sql/reporting/)