Browse DevX
Sign up for e-mail newsletters from DevX


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

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

Installing Your Driver
Once you have implemented the required API's you will need to install the driver you built. You install your driver by first copying the DLL into the following directories:

C:\Program Files\Microsoft SQL Server\ MSSQL\Reporting Services\ReportServer\bin C:\Program Files\Microsoft SQL Server\ 80\Tools\Report Designer

Yes, you do have to copy the code into two locations. One is for the report server and the other is for the report designer. Why the designer and server don't use the same extension is beyond me. One item to be aware of is you might need to shut down the VS.NET IDE if you are working on a report that uses your code. After you have copied your DLL to the appropriate directories you will need to modify a number of config files that come with SQL Server Reporting Services. These entries are as follows:

Add the following entry to the <Extensions><Data> section of the RSReportDesigner.Config file.

&lt;Extension Name="CODECUSTOM" Type="VBRSTEST.DpsiConnection,VBRSTEST"/&gt;

The VBRSTEST.DpsiConnection entry instructs SQL Server Reporting Services to use the DpspiConnection class object from the VBRSTEST Namespace. The second part of the entry (after the comma) points at the namespace as well.

The next step is to grant rights to your data extension. To do this you need to add the following entries to the Rssrvpolicy.config and the rspreviewpolicy.config files found in the ReportServer and Report Designer directories respectively (where you copied your DLLs).

<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="CustomGroup1" Description="Custom extension "> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="c:\Program Files\Microsoft SQL Server\ MSSQL\Reporting Services\ReportServer\ bin\VBRSTEST.dll"/> </CodeGroup>

Figure 13. Data Source Dialog: In this figure, you can see the custom data extension.
The URL parameter must point to the DLL in the directory the config file is responsible for. That is, the rssrvpolicy file should point at the DLL in the ReportServer\bin directory. The designer config file should point at the DLL in the Report Designer directory.

Calling Your Data Extension
The next step is to create a report that uses your data extension. Your data extension will show up in the list of data types in the Data Source dialog box (see Figure 13). For your custom data extension you will need to put some "dummy" text into the Connection String property. The data extension you created does not really support a connection string but the report designer requires that you use one. You will also need to check the "No Credentials" checkbox on the Credentials tab.

Once you specify your dataset, enter some text in to the query window and your custom extension will return the data from the GetDataSet function. Figure 14 shows the data returned from your function in the Preview pane.

Figure 14. Report Preview: You can preview the report with data from a custom data extension.
Now at this point you can create the layout of your report using the same steps you used to create the department report.

What's Next?
This is just an initial foray into creating a custom data extension. In a future article I will take you through more advanced extension techniques including: implementing the interfaces required to support parameters, driver debugging techniques, code access security techniques, and finally how to use the Web service interface to call reports using your custom driver.

Rod Paddock is Editor-in-Chief of CoDe Magazine.
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