Browse DevX
Sign up for e-mail newsletters from DevX


Creating Custom Data Extensions for SQL Server 2000 Reporting Services : Page 3

Microsoft's SQL Server Reporting Services was created with extensibility in mind. Learn how to create your own custom data extensions to take advantage of this powerful new tool.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Previewing and Deploying Reports
Figure 10. Report Preview Pane: Here's the Preview Pane with the export options shown.
To preview your report you simply select the Preview tab from the report designer. (It's the third tab over, the one to the right of the Layout view tab.) The Preview tab lets you zoom, page, and export your report to various formats. Figure 10 shows the Preview pane with the various export formats available in SQL Server Reporting Services. Once you have finished laying out your report you deploy it to your reporting server.

To deploy your report you must specify the location of your reporting server. To specify the reporting server location, right-click on your project, and then select Properties. Set the TargetServerUrl property to the location of your reporting server. Figure 11 shows this dialog with the TargetServerUrl property set.

Figure 11. Report Project Properties Dialog: This figure shows the dialog with the TargetServerURL filled out.
Figure 12. Report Manager: Here's the Report Manager Window containing a department query.
Now that you have a basic understanding of SQL Server Reporting Services you can now look at the process of creating data extensions.

Why Extend Reporting Services?
I am currently working with a client that has chosen SQL Server Reporting Services as their reporting platform. During their evaluation of Reporting Services they reached the conclusion that SQL Server Reporting Services would not meet all of their needs "out of the box." One primary need is the ability to query data from a data source and then pre-process that data before sending it to a report for layout. SQL Server Reporting Services does not handle this situation in its default configuration. When they asked Microsoft what they should do, Microsoft recommended creating a data extension. When Microsoft created SQL Server Reporting Services they had the foresight that people would need to extend reporting services. Thus were born Data and Delivery Extensions. Data extensions provide data. Delivery extensions output reports into different formats.

Data Extensions 101
A data extension provides data to the reporting services environment. In its most basic terms, a data extension is a class that very closely resembles a Data Provider class. It has objects like Connections, Commands, Readers, Parameters, etc. Let me walk you through the steps of creating a data extension using Visual Basic .NET.

Creating Data Extension
Begin creating a data extension by creating a new Visual Basic .NET class project. Next, add a reference to the reporting services interfaces assembly. You'll find this file at the following path:

C:\Program Files\Microsoft SQL Server\MSSQL\ Reporting Services\ReportServer\bin\ Microsoft.ReportingServices.Interfaces.dll

Once you have added this reference you can go to work implementing the interfaces required of a data provider.

The Data Extension Interface
With the reference added, your next step is to implement the minimal interfaces required. Your extension must implement the following interfaces found in the Microsoft.ReportingServices.DataProcessing namespace:

  • IDbConnection
  • IDbCommand
  • IDataReader
Thankfully, implementing these interfaces in Visual Studio .NET is pretty simple. Just use the Implements keyword after your class definition and Visual Studio will "stub" in all of the property and method signatures for you. You then simply implement each method and property.

Listings 1, 2, and 3 show the implementation code for each property and method used by SQL Server Reporting Services when accessing your data extension.

SQL Server Reporting Services could not meet all of their needs "out of the box."
As you can see, the interfaces you need to support are quite simple. The real meat of this code is the GetDataSet function found on the DspiReader class. You put your custom query code in the GetDataSet function. This standard .NET code creates a single table dataset. It's up to your imagination what to put in this function. One idea is to query some data from your database—that data will then be pre-processed and finally sent to your layout for display.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



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