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" at http://www.devx.com/dbzone/article/21214.
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:\programfiles\microsoftsqlserver\mssql\reportingservices\reportserver\bin\microsoft.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" at http://tinyurl.com/lxn2a. 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 at InstantDoc ID 49645.
Prev. page
[1]
2
3
next page 
