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
This entry was posted in SQL Server, SSRS. Bookmark the permalink.

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

  1. You make some good, valid points, but I can’t say that I agree with you completely on this one.

  2. sam says:

    Hi munish,

    I tried custom data extension with vs2005. It worked fine.

    After i installed vs2008 and sqlserver2008, i got an issue.

    Now even after i added extension details to the config files,

    In visual studio2005 with ssrs2005

    I m getting an error as “An attempt has been made to use the dataextension “DataSet” that is not registered for this report server”
    When selecting type for datasource, i can see the DATASET in the drop down list but not my dataextension name.

    In visual studio2008,
    when i tried, i can see my dataextension name in the datasource type dropdown.

    Pls help me.

    Regards,
    Sam

  3. Hi Sam,

    For 2005 & 2008 you need to make an entry in both of the config files (2005 & 2008 separately)…i,e. rsReportServer.config (ReportManager) or rsReportDesigner.config (for BIDS)..

  4. pradeep says:

    Hi munish,

    I need one quick help from you..

    Actually I want to create a report like below

    Input
    Field1
    A
    A
    A
    B
    B
    C

    Output

    Field1,Field2
    A,3/3
    B,2/3
    C,1/3

    Sort by field1
    group by field1
    Field2 Calculation is : countRows()/Top1 CountRows()

    CountRows() indicate each group count

    Top1 CountRows() indicate after sorting which data coming in the top that countrows. for example here “A” is the first record after sorting the dataset but sometime “A” is not there then “B” is the Top record so at the time B countrows should come in this place.

    For that I used custom code and variable to achive the output. I can able to get the output when I run the report but when I export the report into pdf then I am getting #error in the field2 column. I think the custom code doesn’t support for export.

    Note: I am using SSRS 2008. (Not 2008 R2)

    I hope u can able give some tips for the same.

  5. Ed G says:

    Hi,

    I have built a CDE to add a custom data section to my cube connection, to pass in the user that is logged in, since we are using forms authentication. The Cube needs to know who is logged in so it can filter the data based on user access. This is working without any problems and have logging to trace that.

    Now, when I create a subscription, the report runs as expected when creating the subscription. But when the report is scheduled to run the subscription does not hit any of the custom data extension. Am I missing something here? Not sure, but I was expecting that it would go through my extension and possibly have a different user than who has requested the report, but I am not seeing any hits to my code.

    I would appreciate any assistance or guidance,
    Thanks,
    Ed

  6. zoki says:

    Can I simply say what a relief to search out someone who really knows what theyre speaking about on the internet. You definitely know methods to bring an issue to light and make it important. Extra folks must read this and understand this facet of the story. I cant imagine youre no more standard since you positively have the gift.

  7. cialis says:

    I was very pleased to search out this internet-site.I wished to thanks in your time for this excellent read!! I definitely having fun with every little little bit of it and I’ve you bookmarked to take a look at new stuff you weblog post.

  8. Sateesh says:

    Hi,
    I have custome data source set up and is working fine in Prodcution. asbolutely no issuse.

    Now we are upgrading code to .net 4.0 and here is the issue.

    The custom dll required for custom data source is delpoyed in GAC 2010.
    RS2005 is not picking up dll from GAC 2010.
    Where as it is always looking in c:\windows\assembly.

    I cant deploy .net 4.0 dll to assembly folder.

    Is there any settings to configure to RS2005 to pick dll from GAC 2010.

    Please help.

    Thanks

  9. Kumar says:

    Could you please help me on this:

    One of our webpages has a grid control which has no. of routines from which users prints the reports daily.
    When run button is pressed it adds a job to the queue table. A windows service listens to this queue for every 10 seconds or so and this job prints the reports to the printer.

    We are migrating to ReportingServices from crystal reports
    Earlier crystalreports were part of the solution and the crystal reports are designed in such a way that they accept dataset and the data is displayed through the reports to the end users. A count is maintained on the webpage after the job is ran which displays the count of rows from the dataset table.

    For example if the dataset has 1 table the count is nothing but the total no. of records/ rows in the table. This count will be shown on a web page screen after the job is ran/ report is printed.

    Now, our Reporting services have a seperate solution on its own and a seperate build script. We pass in a stored proc to the rdl file to display the data and deploy the report to the report server.

    Passing Dataset to CrystalReport looks like this

    DataSet dataSet = Class.GetData(Param1, param2);
    _recordsFound = theData.Tables[0].Rows.Count;
    PrintReport(dataSet, rptName, rptPath, parameters);

    Our SSRS code:

    DataSet dataSet = Class.GetData(Param1, param2);
    _recordsFound = theData.Tables[0].Rows.Count;
    PrintReport(rptName, parameters);

    If you look into the ssrs code eventhough the ssrs report doesn’t need a dataset, for managing count we are loading the dataset in the code as well as in the report (which uses the same dataset)

    please help me on to get the count without loading the dataset twice in the code as well as in the ssrs report. Or if you could get me a better solution that would be great.

  10. shimon says:

    Hi

    Could these extensions be used for building components that can perform auto-refresh. like in a gauge that monitors the CPU and needs to refresh every 5 sec. I don’t want to refresh the whole report, just the gauge

    Regards

  11. har says:

    Hi can you please share some sample code for Custom Email delivery extension. I am trying to do one and want to know the details of how to get the user’s email id and the right report in the right rendered format?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s