Reporting Services is one of those tools that just keeps on giving.  I’ve been using SSRS since the early beta versions were available and the more opportunities I have to create advanced reports, the more creative ways I find to use this product to do interesting things.  One of its most powerful capabilities is the ability to use custom code functions to extend report designs.  The purpose of this post is not so much to demonstrate a specific technique but to show a few examples to demonstrate the power of custom code functions in report design.

During Jes Borland’s session on report design at SQL Saturday #92 in Portland last week, one of the attendees asked if Reporting Services supported an event-driven programming model.  This is a question often posed by application developers.  My guess is that he had done some programming with Access reports or maybe some .NET forms programming.  The answer is that SSRS did not support event programming but it’s possible to meet most business requirements using a different approach and by working with it’s more linear rendering architecture.  The simple version of how SSRS supports custom programming is that almost every property of every object – the report, body, data regions (rows, columns, etc.) and individual report items – can be set using expressions.  Expressions are actually small bits of Visual Basic.NET code.  Any expression can call a custom VB function embedded into the report or a class method of an external assembly reference.  If you’re not a .NET programmer and all this sounds a little scary, it doesn’t have to be.  Writing and calling a simple custom function isn’t really tough.  Fact is that custom functions can be as simple or as complicated as you need them to be.

Following are two examples of how to use custom code functions in useful ways to solve some real report design problems.  Both of these examples have evolved from solutions and I have similar examples and related solutions posted on my blog: http://sqlserverbiblog.com

Synchronizing Two Tables From Different Datasets

The other day, one of my associates asked for some help with a problem.  He had two data regions that used data from different datasets.  A data region (like a table or matrix) gets its data from a single dataset which is fed from a single data source.  There may be circumstances where you may need to get a value from a corresponding row in a different dataset and use it to calculate or derive a matching value.  Using custom code in the report, you can cache values from one set of records to be used with another set of records returned from a different query.  This technique will work with relatively small sets of data but don’t do this with thousands of rows.  There are variations of this technique which include using the lookup functions introduced in SSRS 2008.  I like this technique because it’s lean and simple.  I chose to use a hash table to get some nice built-in capabilities that would otherwise require more code.

Say I have data in two databases.  One query returns names of people and their salaries and the other query – from the other database – returns the tax rate and the names.  I want to use the name as a key to get the salary for a person and then do some math with the tax rate.

We start with a set of VB.NET functions.  Open the Report Properties dialog, go to the Code page and enter this code:

‘*****************************************************
‘    Paul Turley, 9/29/11
‘    Adds Key/Value pairs to a hash table
‘    and then gets each Value using the Key
‘*****************************************************
Public PersSal as New System.Collections.HashTable()

Function ClearList(SomeValue as String) as String
    PersSal.Clear
    Return SomeValue
End Function

Function AddNameSalary(Name as String, Salary as Decimal) as Decimal
    Try
        PersSal.Add(Name, Salary)
        Return Salary
    Catch ex as Exception
    End Try
End Function

Function GetSalaryByName (ByVal Key As String) As Decimal
        For Each clsPair As System.Collections.DictionaryEntry In PersSal
            If clsPair.Key.ToString() = Key Then Return clsPair.Value
        Next
End Function

There are three functions: ClearList, AddNameSalary and GetSalaryByName

Custom functions have to be called in an expression and expressions are usually referenced on a some object property that needs to have a value.  In order to call the ClearList function to reset my hash table collection, I need this function to return a value that I can use for a property.  I doesn’t really matter what it is.  I just need to pass the value through the function to get the code to run.

My report has two datasets that return the following fields:

Dataset1:   Name, Salary

Dataset2:   Name, TaxRate

My mockup data contains the names of Flintstones characters.  Everyone loves the Flintstones!

The report has two tables, each bound to a respective dataset.

Three textboxes on the reports use expressions to pass values to three different functions and get back values.

The title textbox uses the expression:

=Code.ClearList("Flintstones Adjusted Net Income")

The Salary textbox in the detail row of the first table:

=Code.AddNameSalary(Fields!Name.Value, Fields!Salary.Value)

The NetIncome textbox in the second table:

=Code.GetSalaryByName(Fields!Name.Value) * (1 – Fields!TaxRate.Value)

Here are the results.  The NetIncome column in the second table on the right is the results of a calculation using the Salary from Dataset1 and the TaxRate in Dataset2 for the same Name.  Note that even with the records sorted differently, the results are consistent.

Creating a Scaled Heat Map

The problem of color selection in report design is fairly universal.  As IT professionals, most of us don’t normally concern ourselves with seemingly trivial matters like what coordinated colors to use on a report but nearly all reports need to be styled and dressed up.  Reports having multiple group level headers, footers and borders need to have some visual separation.  Most report designers will select from a short list of different colors and shades according to their own liking but the problem of conforming to corporate color standards, black and white printing and color blind users doesn’t make this a trivial task. 

The idea of mixing colors to created custom color palettes occurred to me on consulting assignments where every client had a different set of standard colors.  As a result, I developed a custom function to create different shades of a base color that are graduated to black or white.  This approach allows the designer to start with a color in the organization’s brand standard.  Different color shades translate to gray scale for printing and are distinguishable by color blind users.  This function and its use are detailed in an article titled “Let’s Get Visual; The Art of Report Design”.  I also demonstrate this technique in my PASS 2011 spotlight session titled “Visual Report Design: Bringing Sexy Back”

Using a similar approach, I’ve created a function that will mix different combinations of red and green.  There are a few heat map report examples that return one of a few static colors (i.e. red, orange, yellow, green, etc.) that may work well in some scenarios.  The following will actually mix the colors on a 256 value scale. 

Two functions are used.  The first is used to set the low and high value range and then the second function returns the right color from an input value within that range.

‘***********************************************************************
‘      Calculate background color for a heatmap report
‘      Returns variations of red and green with pure red returned for the
‘      lowest value and pure green returned for the highest value
‘      Paul Turley, Oct 9, 2011
‘***********************************************************************
Private LowVal As Decimal, HighVal As Decimal

Function SetHeatMapRange(LowValue As Decimal, HighValue As Decimal) As Object
        LowVal = LowValue
        HighVal = HighValue
End Function

Function HeatMapColor(ThisValue As Decimal) As String
         Dim OutR As Byte, OutG As Byte, OutB As Byte
         OutB = 0

        OutR = 255 – ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) * 255)
        OutG = 255 * ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) )
 
        Return "#" & Right("0" & Hex(OutR), 2) & Right("0" & Hex(OutG), 2) & Right("0" & Hex(OutB), 2)
End Function

To implement the solution, place a textbox in the report header area (anywhere before the data region you want to color).  Set the Value property of the textbox using an expression that calls the SetHeatMaprange() function, passing the minimum and maximum range values.  For the table or matrix cell that will serve as the heat map, set the BackgroundColor property using the HeatMapColor function, passing the appropriate field value.

The example I’ve provided is a bit simple but you should get the point.  The color for each cell represents a a point within the min and max range with red on the low end and green at the high end.

Filed under: SolidQ, SQL Syndication, SSRS Design Tagged: Custom Functions