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:
Collapse<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:
Collapse <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:
Collapse <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.
Collapse <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 inC:\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.
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:
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.