Click here to Skip to main content
Click here to Skip to main content
Go to top

Implementing a Data Processing Extension

, 16 Jan 2008
Rate this:
Please Sign up or sign in to vote.
I will take you through how to deploy your own Custom Data Process Extension for SQL Server Reporting Server 2005, both on the server and for use within your development environment.

Custom Data Source Extension

Introduction

I will take you through how to deploy your own Custom Data Process Extension for SQL Server Reporting Server 2005, both on the server and for use within your development environment.

Background

I've been using and writing Reports for SQL Server Reporting Services for some time now, and it has come time to write my own Security Implementation for the reports. In order for me to do this, I needed to find out how to change the Data Sources within Reporting Services.

I did find some resource information on Microsoft TechNet: Implementing a Data Processing Extension. I found this article both useful and hard to follow, hence I have written this CodeProject article to help you get up and going as quickly as possible.

Prerequisites

  • I assume you are familiar with SQL Reporting Services, what they are, design, and installation. For more info about this subject, click here.
  • You should have SQL Reporting Services 2005 already installed.
  • Visual Studio 2005 with SQL Server Reporting Services Report Templates installed.

Compile the Custom Data Source Extension

After loading the source code, you will need to reference the "Reporting Services Interfaces". In my case, it is located in: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.ReportingServices.Interfaces.dll; however, this may differ for your installation.

Once you have made the reference, you should be able to compile the application.

Installation of the Custom Data Source

After downloading the source above for the custom data processing extension, you must install it to enable access. The installation process is performed in two steps:

  • Installing and configuring the extension.
  • Configuring extension security.

This particular extension is used both by the Reporting Server and within the Visual Studio 2005 Report Designer; both have a different location for configuration, either on the server or your development machine. However both configurations are very similar.

Development Installation

Two configuration files are required to be changed in order for you to be able to see your Custom Data Source from within the development environment. These files typically are held in C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies; these files are:

  • RSReportDesigner.config
  • RSPreviewPolicy.config

Copy your assembly

First, you need to copy the Custom Data Source files to the "PrivateAssemblies" folder, as this is where they are referenced.

Setup the configuration file

The RSReportDesigner.config holds the reference to the Extension. The following needs to be added to the <Data> tag within the <Extension> tag:

<Extension Name="CUSTOM_DATASOURCE" 
   Type="DataSourceExtension.DataSetConnection, DataSourceExtension" />

In order for the designer to use the Custom Data Source, you need to add the following to the <Designer> tag, usually situated just below the <Data> tag:

<Extension Name="CUSTOM_DATASOURCE" _
 Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,
       Microsoft.ReportingServices.QueryDesigners"/>

You will notice that this time we are pointing to the standard Microsoft Generic Query Designer. This is because we are trying to build a Custom Data Source Extension and not a Query Designer.

What you should end up with is a RSReportDesigner.config file, looking something like this:

<Configuration> 
    <Add Key="SecureConnectionLevel" Value="0" /> 
    <Add Key="InstanceName" Value="Microsoft.ReportingServices.PreviewServer" /> 
    <Add Key="SessionCookies" Value="true" /> <Add Key="SessionTimeoutMinutes" Value="3" /> 
    <Add Key="PolicyLevel" Value="rspreviewpolicy.config" /> 
    <Add Key="CacheDataForPreview" Value="true" /> 
    <Extensions> 
        <Render> 
            <Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.
                                        XmlDataRenderer.XmlDataReport,
                                        Microsoft.ReportingServices.XmlRendering" /> 
            <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.
                                        CsvRenderer.CsvReport,Microsoft.
                                        ReportingServices.CsvRendering" /> 
            <Extension Name="IMAGE" Type="Microsoft.ReportingServices.Rendering.
                                          ImageRenderer.ImageReport,
                                          Microsoft.ReportingServices.ImageRendering" /> 
            <Extension Name="RGDI" Type="Microsoft.ReportingServices.Rendering.
                                         ImageRenderer.RemoteGdiReport,
                                         Microsoft.ReportingServices.ImageRendering" 
                                         Visible="false" /> 
            <Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.
                                        ImageRenderer.PdfReport,Microsoft.ReportingServices.
                                        ImageRendering" /> 
            <Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.
                                            HtmlRenderer.Html40RenderingExtension,
                                            Microsoft.ReportingServices.HtmlRendering" 
                                            Visible="false" /> 
            <Extension Name="HTML3.2" Type="Microsoft.ReportingServices.Rendering.
                                            HtmlRenderer.Html32RenderingExtension,
                                            Microsoft.ReportingServices.HtmlRendering" 
                                            Visible="false" /> 
            <Extension Name="MHTML" Type="Microsoft.ReportingServices.Rendering.
                                          HtmlRenderer.MHtmlRenderingExtension,Microsoft.
                                          ReportingServices.HtmlRendering" /> 
            <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.
                                          ExcelRenderer.ExcelRenderer,
                                          Microsoft.ReportingServices.ExcelRendering" /> 
        </Render> 
        <Data> 
            <Extension Name="SQL" Type="Microsoft.ReportingServices.DataExtensions.
                                        SqlConnectionWrapper,
                                        Microsoft.ReportingServices.DataExtensions" /> 
            <Extension Name="OLEDB" Type="Microsoft.ReportingServices.
                                          DataExtensions.OleDbConnectionWrapper,
                                          Microsoft.ReportingServices.DataExtensions"/> 
            <Extension Name="OLEDB-MD" Type="Microsoft.ReportingServices.DataExtensions.
                                             AdoMdConnection,Microsoft.
                                             ReportingServices.DataExtensions"/> 
            <Extension Name="ORACLE" Type="Microsoft.ReportingServices.
                                           DataExtensions.OracleClientConnectionWrapper,
                                           Microsoft.ReportingServices.DataExtensions"/> 
            <Extension Name="ODBC" Type="Microsoft.ReportingServices.DataExtensions.
                                         OdbcConnectionWrapper,Microsoft.ReportingServices.
                                         DataExtensions"/> 
            <Extension Name="XML" Type="Microsoft.ReportingServices.DataExtensions.
                       XmlDPConnection,Microsoft.ReportingServices.DataExtensions"/> 
            <Extension Name="RS" Type="Microsoft.ReportingServices.DataExtensions.
                       RSDPConnection,Microsoft.ReportingServices.DataExtensions"/> 
            <Extension Name="CUSTOM_DATASOURCE" 
                       Type="DataSourceExtension.DataSetConnection, DataSourceExtension" />
        </Data> 
        <Designer> 
            <Extension Name="SQL" Type="Microsoft.ReportingServices.QueryDesigners.
                       VDTQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="OLEDB" Type="Microsoft.ReportingServices.QueryDesigners.
                       VDTQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="OLEDB-MD" Type="Microsoft.ReportingServices.QueryDesigners.
                       ASQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="ORACLE" Type="Microsoft.ReportingServices.QueryDesigners.
                       VDTQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="ODBC" Type="Microsoft.ReportingServices.QueryDesigners.
                       VDTQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="XML" Type="Microsoft.ReportingServices.QueryDesigners.
                       GenericQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="RS" Type="Microsoft.ReportingServices.QueryDesigners.
                       SMQLQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 
            <Extension Name="CUSTOM_DATASOURCE" Type="Microsoft.ReportingServices.
                       QueryDesigners.GenericQueryDesigner,
                       Microsoft.ReportingServices.QueryDesigners"/>
        </Designer> 
    </Extensions> 
</Configuration>

Security configuration

The security is held in the RSPreviewPolicy.config file, which is in the same folder as the configuration file (RSReportDesigner.config). This file provides the security policy for SQL Server Reporting Services and allows access to your DLL.

   <CodeGroup 
        class="UnionCodeGroup" 
        version="1" 
        PermissionSetName="FullTrust" 
        Name="Customer_DataSource"
            Description="Code group for my Custom DataSource for data processing extension">
            <IMembershipCondition 
            class="UrlMembershipCondition" 
            version="1" 
            Url="C:\Program Files\Microsoft Visual Studio 8\Common7\
                 IDE\PrivateAssemblies\DataSourceExtension.dll"
            /> 
   </CodeGroup>

Server Installation

To configure the Custom Data Extension on the server where your reports will be running, is a case of following the above, except you'll find the configuration files held in a different location, and depending on the installation, they appear in different places. On my installation file, they are all held in C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin and the configuration files are held in C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer, with the names of the configuration files being:

  • rsreportserver.config
  • rssrvpolicy.config

As this is a server install, you will not need to make or add the <Designer> tag.

Testing of the Custom Data Source Extension

In order to test the Custom Data Source Extension, you will need to generate either a report or a Shared Data Source that points to the new custom Data Source Extension.

Create a new project within Visual Studio 2005, select a "Business Intelligence Project", and select the "Report Server Project Wizard" from the list of templates. On the new data type list, you should see "Custom DataSource Extension". If, however, you don't, but you do see CUSTOM_DATASOURCE, this means that the configuration has not picked up your DataSourceExtension.dll, and you will need to check over the configuration files.

Custom Data Source Extension

Enter a connection string, like the following: "FileName=C:\bottles.xml", you'll find this file at the top in the download section. Click next, this will take you to the "Design a Query" screen. Enter the following query: "select * from company", as shown below:

query builder

Click Next, and then continue until you have finished creating a report. You should now be able to run the report with the new Custom DataSource Extension that you can customise and shape to your own needs.

Points of Interest

Now that you have it running and working, it is worth going over how to debug the Custom Data Extension. I was not intending to cover this in this article, and will reference you to Microsoft TechNet on Debugging Data Processing Extension Code, as this should provide you with sufficient information on how to setup debugging.

History

First release.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Bryan Avery

United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
QuestionUnable to preview the reports PinmemberMember 989950621-May-13 3:41 
QuestionUnable to locate the config files PinmemberMember 989950621-May-13 1:48 
QuestionData Processing Extension Pinmemberanismanoula4-Sep-12 1:40 
QuestionCan we use custom data processing extension for report model in ssrs2008R2 Pinmemberragachandrika27-Dec-11 0:54 
GeneralImplementing a Data Processing Extension for Report Model PinmemberSowjanya Gullapalli6-Jun-11 2:25 
GeneralImplementing a Data Processing Extension PinmemberSowjanya Gullapalli2-Jun-11 0:49 
GeneralEdit button - Select the Data Source PinmemberJay_Cee23-Feb-11 18:03 
GeneralConfiguring CDE for VS 2008 Pinmembersomagani18-Nov-09 6:03 
Questiontrainee Pinmembervinthashan24-Aug-08 23:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140905.1 | Last Updated 16 Jan 2008
Article Copyright 2008 by Bryan Avery
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid