Custom Data Processing Extensions for Reporting Services

Developers want to reuse as much code as possible. It's in our nature. Why spend time rewriting the same logic in multiple places? We'd much rather move on to solving new problems. So, for the hundreds of developers who've asked whether it's possible to share a data set across multiple SQL Server Reporting Services reports, I'm happy to tell you that the answer is yes. To avoid putting the same query code in multiple reports, you can do the obvious thing and use stored procedures or views. But what if you work in an environment without stored procedures? Custom data processing extensions provide the solution.

This article shows you how to use a custom data processing extension for SQL Server 2000 Reporting Services to read and assemble queries from a single XML document,issue the query against an OLE DB data source, then return the results to a report data set. You'll want to be familiar with Reporting Services 2000, and I also recommend that you read Enrico Sabbadin's May 2004 article "Build an XML Data Extension for SQL Server Reporting Services."

Mixed Environment Challenges

I recently worked with a client who had multiple database environments—SQL Server, Informix, SAS, and IBM DB2. As I mentioned, the obvious approach to reporting in this environment would be to use database objects for report queries. I thought about using Reporting Services and the OLE DB and ODBC data providers to query the client's data sources. But the client's environmental standards prevented me from using views or stored procedures. I needed to produce many reports, and in several instances, the same query generated parameter values for each report.

I knew that I didn't want to embed the same query code in each report. Invariably, something about the query would change and I'd have to open and close the Report Definition Language (RDL), change one line of code, test the change, and redeploy the reports. The process would be costly to the client—and not my idea of fun. So I had to figure out how to reuse the code. The solution: Create a custom data processing extension for Reporting Services.

Create a Custom Data Processing Extension

Although Reporting Services doesn't offer globally shared data sets, it does provide a nice set of interfaces that you use to create custom extensions for rendering, delivery, security, and data processing. Data processing extensions look a lot like the data layer that applications use to connect to a database, retrieve data, and return it to an application. Simply put, a report data set uses data processing extensions to retrieve the data a report needs from a data source.

When you create a data processing extension, you implement a set of interfaces and classes in the Microsoft.ReportingServices.DataProcessing namespace. Check out the Microsoft article "Using an ADO.NET DataSet as a Reporting Services Data Source" at http://tinyurl.com/gtq7q. The walkthrough in the article shows you the basics of creating a data processing extension and explains the principles of how the extension operates. Our example shows these principles in action.

Because the custom data processing extension needs to result in a .NET assembly, the first step is to create a .NET class library project in your language of choice (either C# or VB.NET). In your project, you'll need to set a reference to the Microsoft.ReportingServices.Interfaces library. The library contains the data processing extension interfaces (located by default at C:programfilesmicrosoftsqlservermssqlreportingservicesreportserverbinmicrosoft.reportingservices.interfaces.dll). Our example implements all the required interfaces. You can find a list of the required interfaces in the MDSN article "Preparing to Implement a Data Processing Extension." When you've set up the project and implemented the required interfaces, it's time to move on to customizing a data processing extension.

Custom Data Processing Extension Example

Our example creates a custom data processing extension contained within a .NET assembly .dll and uses it as the data source for the AdventureWorks2000 Product Line Sales Report, one of the sample reports included with Reporting Services. Our example uses the SQL OLE DB provider to connect to the AdventureWorks2000 database, and it shows you how you would use the OLE DB provider to query an Informix, DB2, or SAS database. Table 1 lists the files that our example uses. You can download the .zip file that contains these example files from the "Dowload the Code" link at the top of the page.

Deploy a Custom Data Processing Extension

Let's get started. First, make sure you've got AdventureWorks2000 set up on your database server. Next, deploy a custom data processing extension by following the steps described in the MSDN article "Implementing a Data Processing Extension." The data processing extension exists as a .dll assembly in the reportserver bin folder, but you must make it possible for Report Server and Report Designer to discover it. Copy the sample assembly .dll file, oledbdataprocessingextension.dll, to the appropriate bin directories and modify the configuration files, as Listings 1, 2, and 3 show. Modifying the configuration files lets Reporting Services recognize the extension and grants the appropriate code-access security policies for the extension.

Next, verify that deployment to the Report Designer was successful. As Figure 1 shows, open the Sample Report Project in Reporting Services and double-click dsOLEAdventureWorks. On the General tab, you should see an entry for XML Query Repository Custom Data Extension. If the extension isn't listed, double check your deployment steps. Be sure that the paths in the Code Access Security entries are correct (see Listings 2 and 3). Then, verify that deployment to the Report Server has been successful. Go to Report Manager, add a new Data Source, then check that XML Query Repository Custom Data Extension appears in the Connection Type list.

Next, you must designate a single location for any code that you want to reuse. Our example stores queries to be reused in an XML document. XML is good at many things, and one of its best qualities is its ability to describe and organize string data. In addition, you can enforce XML structures through document type definitions (DTDs), which is an important feature if you have a class library that's dependent on the format of the XML.

Because we use the XML document as our single source of query code, when the custom data processing extension receives a request to populate a report data set, it assembles an SQL query from the XML document, issues a query, and returns the results to a report. You can add additional queries or modify existing queries defined in the XML document and not redeploy the extension each time something changes. Using the XML document provides a perfect solution to the dilemma of making many small changes that I mentioned earlier.

Now, decide where to put the queries.xml file.We put it off the root of the C: drive. When we defined the extension in the rsreportserver.config and rsreportdesigner.config files, we added a child node called Configuration. The IExtension interface defined in the Microsoft.ReportingServices.DataProcessing namespace has a SetConfiguration method, which is implemented in the dpxConnection class in our extension project. Implementing SetConfiguration lets the extension capture information defined in the Configuration node of our rsreportserver.config entry.

Note that because the data processing extension reads queries.xml to determine what query to issue, the built-in INTERACTIVE group must have Read access to the file. Go to the file, select the file properties, go to the Security tab and add the INTERACTIVE group with Read access. The XML document contains data set nodes that correspond to a report data set. Listing 4 shows that the data set node has child nodes that describe the query, parameters, and group by. The TopEmployee data set uses the query in Listing 4 in the Custom Product Line Sales Report. When the report requests the TopEmployee data set, our custom data processing extension assembles the query from the XML document, adds appropriate parameter values to the WHERE clause, and executes the final query against the OLE DB data source.

Connecting the report and extension is as straightforward as setting the Report Dataset CommandText equal to the name of the dataset node in the XML document that contains the query we want to use for the report's data set. Figure 2 shows you just how easy it is to link the report and extension. After you enter the CommandText, go to the Generic Query Designer window (note that this sample doesn't support using the Graphical Query Designer window). Click the Refresh button.The extension reads the queries.xml document, finds the node matching the CommandText for the report data set, and determines whether the node has any parameter child nodes. If the extension discovers any parameters defined for the data set, it returns these to the Report Dataset Designer, defines the parameters for the report data set, and automatically adds them as report parameters to the report definition, as Figure 3 shows.

Next, on the Report Data tab, make sure you're using the Generic Query Designer window, then click the red exclamation mark (!) at the top of the window. The data set results appear in the window below the Query Designer.

Behind the Scenes

Let's look at Listing 5 to see what's going on behind the scenes. The extension is built on foundations and concepts that you saw in the Microsoft walkthrough article "Using an ADO.NET DataSet as a Reporting Services Data Source." Our sample extension adds logic to build queries from the XML document.

Our customization starts in the dpx-Connection class, where the custom data processing extension reads the configured path of the queries.xml file via the implementation of the SetConfiguration method. After SetConfiguration determines the path to queries.xml, the path is handed off to the dpxCommand class. The xmlCommandText property matches the CommandText defined by the report data set ( TopEmployees) with a data set node in the XML document of the same name. Listing 5 shows you how this process works.

Now, take a look at Listing 6 to see how the report data set uses the GetParameters method of the ICommandAnalysis interface implemented on the dpxCommand class to discover parameters. Whenever a Reporting Services data set executes, it calls the GetParameters method. GetParameters then loops through the parameters node list from the XML document, adding a dpxDataParameter to a collection each time it runs through the loop. The GetParameters method returns the dpxDataParameterCollection after it reads the last item in the parameters node list.

The dpxDataReader class does the heavy lifting in the extension. Logic in this class assembles the query, transforms the results into a DataTable, then reads the results back to Reporting Services. The LoadDataReader internal method organizes the query embedded in the XML document—the method calls internal functions and assembles the parts into a single query to execute against the database. The data set node of the XML document contains child nodes describing the SELECT, WHERE, and GROUP BY parts of the query.

The call to the OLE DB database returns a high-performance, read-only DataReader, perfect for reporting. To provide a little more flexibility with the query results, use the GetTable internal function in dpxDataReader to get the results from the DataReader and put them into a DataTable object. GetTable loops through the DataReader to build a DataTable. GetTable can specify inserted rows to add to the results, which is useful when you want to use the query results to populate parameter values and present the user with an ALL parameter selection. Typically, you'd use a UNION query to create an ALL row in the results.

Listing 7 shows how in the queries.xml document you can specify a row to manually insert along with the query results from the database. Listing 8 shows you how the GetTable function handles the inserted row after the results have been put into a Data-Table object. With the results in DataTable, the Report continuously calls the Read method of the dpxDataReader to return the rows for the Report.

Finally, debugging the sample will help you walk through the extension class library and examine the inner workings of the code. I recommend that you read the Microsoft article "Debugging Data Processing Extension Code" for details about how to debug extensions.

SQL Server 2005 Extends Reporting Services Even More

You've seen the power of extending Reporting Services and have a starting point for reusing code in reports. You can expand on the logic for assembling queries to include more complex WHERE clauses. In Reporting Services 2005, you can specify a Web Service as a data source, which lets you create a repository as a Web Service and separate the code repository logic, eliminating the need to configure an extension for Reporting Services.