RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Create Reports from Any Data Source Using SQL Server Reporting Services Custom Data Extensions

SQL Server Reporting Services makes it easy to build reports from database data, but what if the data isn't in a database? This article shows you how to take advantage of Reporting Services' unique extensibility model to build reports from ADO.NET datasets.

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.
Approach Requirements Implementation Effort Usage Scenarios
ReportViewer (local mode) SSRS 2005 and VS.NET 2005 Low Generating a local report and binding it to a collection-based object
XML Data Provider SSRS 2005 Low 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 Medium Building a dataset in a SQL Server database.
Custom data extension (CDE) SSRS 2000 or 2005 Standard Edition or above High Reporting from custom data sources.

ReportViewer Controls
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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date