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

Posted on July 9, 2008. Filed under: SSRS | Tags: , , , |

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.

About these ads

Make a Comment

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

3 Responses to “SSRS Custom Code (Expressions, Embedded Code & External Assemblies)”

RSS Feed for RoMiller.com Comments RSS Feed

hello Rowan, please tell me if this is possible using one of the methods (embedded code or custom assembly).
I have a parameter called service. I’m thinking of dropping 7 charts (with the Hidden property set to True) on the page that use the same shared dataset. then create an expression for each chart that: if parameter at index.1 is not null, use parameter.index1.value for Chart1, then “set chart1 to visible” , if parameter at index2 is not null, do this [etc]. then I would have create an expresssion for the name of the chart also. Then it would also need simalar expression for the go to report action of the chart. Sound doable?

Brilliant article. Was highly informative, and in a very presentable, structured way. Thank you.

There’s a full article on embedding code within SSRS 2008 at http://www.wiseowl.co.uk/blog/s288/embedded-code-ssrs.htm – hope this helps someone.


Where's The Comment Form?

    About

    Rowan works as a Program Manager for the ADO.NET Entity Framework team at Microsoft. He speaks at technical conferences and blogs at romiller.com. Rowan lives in Seattle, Washington with his wife Athalie. Prior to moving to the US he resided in the small state of Tasmania in Australia. Outside of technology Rowan's passions include snowboarding, mountain biking, horse riding, rock climbing and pretty much anything else that involves being active. The primary focus of his life, however, is to follow Jesus.

    RSS

    Subscribe Via RSS

    • Subscribe with Bloglines
    • Add your feed to Newsburst from CNET News.com
    • Subscribe in Google Reader
    • Add to My Yahoo!
    • Subscribe in NewsGator Online
    • The latest comments to all posts in RSS

    Meta

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

Join 168 other followers

%d bloggers like this: