ince its first release, Microsoft SQL Server Reporting Services (SSRS) has supported a plethora of data sources. Out of the box, its data modules, called data extensions
, allow you to build reports from SQL Server and Oracle database, as well as any other database that comes with an OLE DB or ODBC provider. But what if you want to build a report from a data source that isn't a database or doesn't have a provider? For example, what if you want the report source to be data from an ADO.NET dataset? To meet such requirements, you can extend SSRS by writing (or acquiring) a Custom Data Extension (CDE).
Choosing Data Integration Approach
Before deciding to write a custom data extension, it's worth exploring all your data integration options with non-standard data sources. Table 1 enumerates some of them.
Table 1. Data Integration Options: The table lists alternate data integration approaches, their tool requirements, the effort required for implementation, and shows when you might consider using each.
|ReportViewer (local mode)
||SSRS 2005 and VS.NET 2005
||Generating a local report and binding it to a collection-based object
|XML Data Provider
||Reporting from ADO.NET datasets or XML documents from URL-addressable resources, e.g. Web services.
|CLR stored procedure
||SQL Server 2005 as a data source
||Building a dataset in a SQL Server database.
|Custom data extension (CDE)
||SSRS 2000 or 2005 Standard Edition or above
||Reporting from custom data sources.
Going through the various options shown in Table 1, you can first consider the SSRS 2005 ReportViewer controls
that ship with Visual Studio.NET 2005. You can configure these in a local report-processing mode where the application is responsible for supplying the report data. In local-processing mode, the application can bind a local report to various collection-based objects, including ADO.NET regular or typed datasets. If you don't need to deploy your report to the report catalog and you target SSRS 2005, the report viewer controls may be the way to go.
XML Data Extension
A second option is when the source is an ADO.NET dataset (or XML document) streamed from a URL-addressable resource, such as an ASP.NET page or a Web service, consider the SSRS 2005 XML data extension. This blog post
explains how you can build a report that uses the XML data extension to invoke a Web service and use the returned ADO.NET dataset as a data source.
CLR Stored Procedures
A third possibility is to build the dataset at the data source. For example, if your application targets SQL Server 2005, you could implement a CLR stored procedure to build the dataset. Consider this option when you need to extract data from many table objects but want to avoid multiple trips from the application layer to the database. The article "Implementing Smart Reports with the Microsoft Business Intelligence Platform" demonstrates how this could be done.
Finally, as shown in the last row of Table 1, a CDE (the subject of this article) gives you the most flexibility. As with the other SSRS extensibility features, custom data extensions are supported with SSRS Standard or Enterprise Edition only.
Introducing Custom Data Extensions
A custom data extension is a .NET module that you can use to extend the SSRS data access capabilities if needed. For example, as shipped, SSRS doesn't support binding a server report to an ADO.NET dataset. Yet, many integration requirements call for the application to pre-process the data before handing it off to SSRS for display in a report. For example, a distributed application may need to retrieve a dataset from the middle tier, update its data based on some business rules, and bind the dataset to a deployed report. This is exactly the scenario the CDE you'll create in the rest of this article is designed to handle.