Adding Custom Code to the Reporting Services 2008 R2

There are numerous circumstances when we wish to add a custom function to a SSRS report in order to cater for needs of the customer which exceeds the capability of the built-in functions in SSRS. In these scenarios we will have to write our own functions. In this article I will demonstrate how to add custom code to SSRS.

Using SQL Server 2008 R2 Business Intelligence Studio

 Using Custom Code inside a report:  In this article we will consider a scenario where we want to design a KPI using custom code inside a report using the code tab of the report properties. For this we will have to write the below VB code as VB is the only language currently supported for the custom code in SSRS. Create a new SSRS project in BIDS studio and add new item as a report from the templates.

1. Create a dataset using AdventureWorks as datasource the following query:

SELECT  top 1   Name,  StandardCost, ListPrice, ListPrice - StandardCost AS ProductProfit
FROM    Production.Product
WHERE   (StandardCost >= 1000)

2. Select Report-> Report Properties from the menu.

3. Select Code tab from the report property window

4. At present only VB  supported for writing  custom code inside the report. Copy and paste the below code in the code window and click ok:

 Public Shared Function Test(ByVal profit As Decimal) As String
        Dim st As String
        If profit >= 1000 Then
            st = "High Profit"
        ElseIf profit >= 500 Then
            st = "Moderate Profit"
        Else
            st = "Average Profit"
        End If
        Return st
   End Function

5. Right-click on the textbox  and go to expressions.

6. To call the function written in the custom code window you will have to enter  Code.FunctionName . In the expression in our example it would be something like this:

=Code.Test(Fields!ProductProfit.Value)

7. The final Output should be something like this.

 Using Custom Assemblies:

The custom assemblies can be created using a class library project to create more advanced functionality for your reporting solution. The code reference for function to be used in the reporting services can be given in the references section in the report properties. To do this create a custom assembly and create a class library project called TestClass and add the below code to it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace TestClass
{
    public class Class1
    {
        public static int Cal(int a, int b)
        {
            return a + b;
        }
    }
}

Pages: 1 2




Related Articles :

5 Responses to “Adding Custom Code to the Reporting Services 2008 R2”

  1. I agree with your main point about the extensibilty features of the custom assembly (CS) but it is not without its drawbacks.

    Interesting example. What happens if the definition of profitability changes? Recompile and redistribution of the DLL?

    Couldn’t/shouldn’t the server backend deliver these business metrics through a Profit level Case statement? Or potentially the business tier of a multi-tiered application?

    Any functionality added to the CS should be carefully scrutinized for its potential benefit/applicability versus its costs.

    CS is a powerful tool but it should be used for functionality not delivered directly by any of the components within the applications framework. For example, automating the printing reports without first having to press the print button.

    Thanks for your efforts and my comments from the cheap seats are from my perspective use or lose them as is seen fit for the situation.

    Cheers

  2. HI,

    NICE ARTICLE.

  3. Question: What is the benefit of doing this instead of perhaps just including a new column returned by the query/sproc/data source that would perform the evaluation there?

    Thanks!

    • Hi Josh,

      Thank you for reply. Well this same functionality can be achieved in various ways like by writing cases in SQL or expressions in SSRS. But the focus of this article was to make users aware that custom code and custom Assembly like features can levarage the SSRS functionality.

      For eg:-Custom code can be used to have a common color for your charts.

      I hope this answers your question.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |