Extending SQL Server Reporting Services 2005 with Custom Data Extensions (SSRS)

Custom Data Processing 

 There are many components of SQL Server reporting services which can be customized as per the requirements & one of them is how data is being fetched/processed. Though, SSRS provides quite a good number of inbuilt data providers to connect to various types of data sources (inbuilt data processing extensions), there may be a need to do some custom processing before the data actually gets delivered to SSRS report. 

Also sometimes one may have requirement to merge/join data from multiple data sources (say from a RDMS database & from a cube), which at the first sight looks like a limitation of SSRS. But there is what it offers the facility to extend the services by implementing a Custom Data Extension. 

 Well by CDE, it means you can write your own custom code (in .net) which will take the dataset query as its input and will give you the result-set as per the custom logic implemented in the CDE code. So basically doing so we are extending/customizing the ‘Data Processing Extension’ 

 For creating the custom libarary for it there are certain interfaces which need to be implementing. 

These include:   

• IDbConnection
• IExtension
• IDbCommand
• IDataParameter
• IDataParameterCollection
• IDataReader
• IDbTransaction

Details about Required Extensions: 
http://msdn.microsoft.com/en-us/library/ms153967.aspx
 

Data Extension Process Flow  

Before developing your custom data extension, you should understand how the report server uses data extensions to process data. You should also understand the constructors and methods that are called by the report server. 

 The step-by-step process flow of a data extension that is called by the report server 

 

Also there are certain dll references to be added in the library solution: 

• Microsoft.ReportingServices.DataExtension
• Microsoft.ReportingServices.Interfaces

Implemention of various Interfaces and their methods/properties: 

Class	Implements Interface	Description
Connection class	IDbConnection, IExtension	This is the entry point of the data extenson.
	Methods – IExtension
	SetConfiguration(string config)	For getting the connections defined in RSReportDesigner.config and using them in the code. The whole xml string gets passed from SSRS.
	Methods – IDbConnection
	Open()	Called at the beginning to open the required db connections.
	CreateCommand()	Called to create an object of IDbCommand to form the command to be executed.
	Close()	To close the connections at the end.
Command Class	Implements Interface
	IDbCommand, IDbCommandAnalysis (optional)	This class takes care of preparing the command based upon the commandtext/query passed from the report dataset & retruns the resultset in the form of DataReader.
	Methods – IDbCommand
	CreateParameter()	Creates a new instance of an IDbDataParameter which takes care of parameters valuations.
	ExecuteReader()	To prepare the resultset based upon the parameters and the query passed.
	CommandText : Property	This gives the command/query passed from report dataset.
	Methods – IDbCommandAnalysis
	IDataParameterCollection GetParameters()	This method creates a collection (IDataParameterCollection) of IDataParameters. We can also write our own logic here to prepare the IDataParamCollection and return.
Parameter Class	Implements Interface
	IDataParameter	This contains some functions like ‘Value’ to get the value of a parameter.
Parameter Collection	Implements Interface
	IDataParameterCollection	This contains the function like ‘Add’ to add the parameters to a collection and contains properties like ‘IndexOf’ to get/set the index in the collection.
Data Reader Class	Implements Interface
	IDataReader	This class comes into picture when ‘ExecuteReader’ method is being executed. This method works in conjection with the DataReader class to form a resultset.
	Methods – GetName(), GetOrdinal(), GetValue() & Read() etc
Transaction Class	Implements Interface
	IDbTransaction	To handle transactions.

Once the data extension library (.dll) is compiled, it has to be placed into foler at: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies 

Registering the CDE with report designer & making SSRS report datasource able to access the CDE (RSReportDesigner.Config & RSReportServer.config) 

 (Generic names are used here)

<Extension Name=“MyExtension“ Type=“NameSpacename.ClassImplementingIDbConnection, AssemblyName“>

<Configuration>

<connections>

<description><environment>MyCustomExtension </environment></description>

<connectionstring>

    <name>connectionname1</name>

    <value>Data Source=servername;Initial  Catalog=DBName;Provider=MSOLAP.2;</value>

</connectionstring>

</connections>

</Configuration>

</Extension>

There can be n number of connection defined, all wil be passed to CDE as a XML string.

 Now when creating a Data Source in SSRS, this extension will be available to be selected as a type of connection:

About these ads