It's uncanny: I meet my friends for dinner or a drink, we engage in small talk about home projects and the kids, but then we end up talking about SQL Server. I know, I'm sick, but that's what ends up happening. Just the other evening I met Andy Potter at a local eclectic tavern; he's an old friend who logged many hours with me on past SQL Server projects. We spent some time stalling with idle discussion but soon started talking about Reporting Services. The discussion became quite interesting when Andy brought up his latest "shortcut," a Reporting Services custom data processing extension designed for query reuse against most any data source.
Custom data processing extensions for Reporting Services provide a powerful mechanism for customizing access to report data sources. Many common scenarios dictate using a data processing extension to merge data from multiple data sources or interacting with an application's custom data layer and consuming a .NET DataSet. You can find more information about data processing extensions at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_dataproc_5c2q.asp .
Andy was recently in a situation in which he needed to query both SAS (data mining-- http://www.sas.com) and IBM DB2 data sources in Reporting Services reports. Both SAS and IBM DB2 include OLE DB providers that Reporting Services can use to query the respective data sources, but these providers would distribute common queries across numerous reports. Ideally, Andy could set up linked servers from SQL Server and use stored procedures containing OPENQUERY statements to consolidate queries for reuse in multiple reports. But his client's environment didn't allow linked servers from SQL Server, so the stored-procedures approach wasn't viable. Instead, Andy had to develop an alternative mechanism for query reuse.
To provide code reuse similar to stored procedures, Andy created a custom data processing extension that references an XML document containing the report queries. Report DataSets use this extension as a data source. The CommandText of the report DataSet indicates which XML document node contains the query for the DataSet. In addition, the XML node contains information regarding filter and sort information for the query.
The custom data processing extension receives the CommandText and parameter values from the report, parses the XML document, assembles the query, and issues it against the data source. If multiple reports use a query, the extension and XML document provide a single source for that query, rather than repeating the query across each report. If the query changes, Andy doesn't have to open every report; just modifying the XML document automatically affects each report that uses that query.
It was great to enjoy a good Trappist Ale with Andy as he enlightened me about his innovative use of Reporting Services with non-Microsoft data sources. Using a custom data processing extension exposes new possibilities for building a compelling SQL Server reporting solution that efficiently and quickly queries across multiple database environments.
