How to Extend SSRS Functionalities

SQL Server 2008

SQL Server Reporting Services is one of the best Reporting Services which is available today; it has lots of functionalities and is easy to use. Today I’ll show you a way to extend SSRS functionalities.

—————————————————————-

GNU Logo

‪Title : How to Extend SSRS Functionalities

‪Publish Date : 26/04/2010

‪Version : 1.0

‪Author : Nasser Hadjloo

‪Author Mail : n.hajloo@gmail.com

‪Copyright (c) 2010 Nasser Hadjloo.

‪Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation;

‪with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license is included in the section entitled “GNU Free Documentation License”.

—————————————————————-

There are sometimes which you cannot do, your desire work with SSRS default methods and Functions. For example CDate, IIF and etc are some of default SSRS commands. But what if you like to add your custom method to it? For example you need a method which take a Georgian date and return Persian Shamsi date. What can you do in these cases? Fortunately SSRS is a flexible service and you can easily extend it.

To extend SSRS, you need to create a class with desire methods, for example a class with a ConvertToPersiandate(dateTime georgiandate).

After that you have to build the project and Copy your DLL into following paths.

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Bin

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

These paths changed to following path in 2008 version

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Bin

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

The final step is to use it as a built-in SSRS command.
Just note that, you have to use complete Address for your method for example if you have these configurations

Name Space > Hadjloo.Web.Helpers.SSRS

Class Name > Converters

Methods in Class > ConvertToPersiandate(dateTime georgiandate) , ConvertToShortPersian (dateTime georgiandate) and etc

After That you have to add this DLL to your Report. To do this you should right click on the report and select Properties, then you should go to References tab and add this dll as a reference to your report.

SSRS 2008 Design Environment

Then in SSRS you have to use it like

=Hadjloo.Web.Helpers.SSRS. Converters. ConvertToPersiandate( First(MyGeorgianDate.value))

Note that in above case, First is a built-in command to get the first row value, and MyGeorgianDate is the DateTime field from the DataSet.

And also note that ConvertToPersiandate will have a red underline which is not important.