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" at http://msdn2.microsoft.com/ms155387.aspx 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.