SSRS Custom Code (Expressions, Embedded Code & External Assemblies)

/ romiller.com

Introduction

In this article we will look at the options for using custom .Net code in our Microsoft SQL Server Reporting Services (SSRS) reports. This post assumes you have a working knowledge of SSRS.

There are three main ways to use code to help generate our reports, in increasing order of complexity (and therefore flexibility) these are;

  • Expressions
  • Embedded Code
  • External Assemblies

The Sample Solution

To demonstrate the different options we will work with a simple report example based on the Adventure Works database
The Adventure Works database can be downloaded at http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

I’ve setup one dataset which returns the total number and revenue of sales for each territory;

I’ve placed a single table on the report that displays each row from our dataset;

When the report runs it looks something like this;

Expressions

Expressions in SSRS are a very similar concept to formulas in Excel, we are going to add an extra column to our report to display the Average Sale Revenue for each territory. In the detail cell we can type the following formula to display the result of dividing the revenue by the number of sales;

=Fields!SalesAmount.Value/Fields!SalesCount.Value

Note that if you right click on the cell you will get an ‘Expression…’ option in the context menu which will give you a nice little GUI for designing your expression. Now if we run the report it should look something like the following;

Expressions aren’t limited to cells in tables so if we add the following textbox to the page header of our report it will display the name of the report at the top of the report, this is really handy because if we change the name of a report we don’t need to worry about adjusting the header as well.

You will also notice that you can use expressions to adjust properties of report controls, for example the background colour of our table can be set using a property, in fact we can adjust any properties apart from the size and position of controls using expressions.

Embedded Code

Now obviously expressions have their limitations, they aren’t reusable throughout our report as we need to copy and paste them into each cell or property and they can’t really execute any logic.

What we can do is write report wide functions using .Net code and then call these functions from expressions. For example let’s add some colouring to our table based on the average sale, territories with an average sale of under $5,000 will be shaded red, under $10,000 will be shaded orange and $10,000+ will be shaded green.

We could achieve this using expressions (utilising nested IIF statements) but if we wanted to change one of the thresholds we would have to change it on every cell that was shaded. A much nicer solution would be to create a function that accepts an average sale amount and returns the appropriate colour.

To open the embedded code section select ‘Report->Report Properties…’ from the menu and open the ‘Code’ tab, below is the function I have created to calculate our background colour;

If we select the detail row of our table and enter the following expression into the BackgroundColor attribute;

=Code.GetColour(Fields!SalesAmount.Value/Fields!SalesCount.Value)

Now when we run the report we should get something like;

External Assemblies

Embedded code also has its limitations, we can’t reuse our functions between reports without copy/pasting and we don’t get intelli-sense and other niceties that we have all grown to depend on. To re-use functions between reports we need to create a class library and then reference the resulting compiled assembly from our report.

I’ve added a C# Class Library project to my solution and added a single ‘ReportUtils’ class as follows;

The code for ReportUtils.cs is as follows;

using System;
using System.Collections.Generic;
using System.Text;

namespace RowansCustomLibrary
{
    public sealed class ReportUtils
    {
       public static string ReportFooter()
        {
            return "Printed " +
                DateTime.Now.ToString("dd-MMM-yyyy HH:mm") +
                " by " + Environment.UserName;
        }
    }
}

Now to make our assembly easily available other .Net code I am going to load it into the Global Assembly Cache (GAC), to do this I need to “Strong Name” the assembly. Basically this involves versioning and signing the dll that is produced during build, to do this right click on the class library project and select properties, open the Signing tab and tick the Sign the assembly box, you can use an existing key if you have one or Visual Studio will create one for you if you select New….


Now build your class library (Ctrl+Shift+B), preferably in “Release” mode. To install your new assembly into the GAC just open up the bin\release folder of your project and drag the dll into the C:\WINDOW\assembly directory on your machine (you can also use the command line “gacutil” tool to install assemblies into the GAC).

To reference our new assembly from the SSRS project we need to;

  1. Select ‘Report->Report Properties…’ from the menu
  2. 2. Select the ‘References’ tab
  3. Click the ‘..’ button
  4. Select the ‘Browse’ tab
  5. Navigate to the dll in the bin\release folder of the class library project
  6. Click the ‘Add’ Button
  7. Click ‘OK’ and then ‘OK’

Note: Although we have selected the dll from our project file it will actually load the GAC version when the report runs.

Now we can add a textbox to the footer of our report that uses our assembly;

We will also need to deploy our assembly to the GAC on any machines where our report is executed, in most cases this would be your report server.