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 FunctionFunction AddNameSalary(Name as String, Salary as Decimal) as Decimal
Try
PersSal.Add(Name, Salary)
Return Salary
Catch ex as Exception
End Try
End FunctionFunction 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 DecimalFunction SetHeatMapRange(LowValue As Decimal, HighValue As Decimal) As Object
LowVal = LowValue
HighVal = HighValue
End FunctionFunction HeatMapColor(ThisValue As Decimal) As String
Dim OutR As Byte, OutG As Byte, OutB As Byte
OutB = 0OutR = 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.
Download a simple example of this finished report: heat-map_rdl
Hi Paul. I simply want to stop execution of a report using a function preferably with a message and without adding or affecting textboxes if either my min balance parameter is zero or max balance parameter is zero. is this possible say without enabling remote errors?.
Hello, is it possible to use the same code in case of synchronizing two tables from different datasources? I’ve tried but couldn’t get the Salary values, just zeros instead.
Yes. If you can’t join tables in a single query, usually the best bbet might be to use the LOOKUP function or one of its variations. That would be much easier than writing your own code. Just be mindful about using this technique with small sets of data. There are usually performance trade-offs. Otherwise, look to a linked server query or some method to mashup the data before it reaches the report, such as SSAS.
Hi Paul,
I have a specific requirement in which i need the latest cube refresh date in my report dataset and show it on my report.However while running this report simultaneously when the cube is processing I get a error message saying rsProcessingAborted: Query execution failed for DataSet “DataSetName” etc. My question is can i hide this error and show any custom message when user tries to open the report while the cube is down for processing or not available.
Is it possible to write a custom try catch block to handle these error type properly from SSRS and show the custom message when someone tries to open the report at that time.
Appreciate your help to solve this.
Paul,
This is old, but until now I have been able to avoid the need. … When writing a cusom function in Report Code (VB), what is the name space for Report Objects themselves? That is if one wanted to check a report object properties to determine a conditional statement outcome in VB (ie like your System. namespace at the top of the article) I know it exists, because a couple of years ago, before I found a better way, I was accessing report objects in Custom Functions in Report Code, but i have not found where I archived those reports.
Thanks,
Tom
Hi, Can you please provide custom code for lookup function?
I’m not sure what you did here but it looks like your question was entered into the Name field in the comment form. Regardless, you ask a good question. There are a quite a few conditions that would cause an expression to throw an error, like using DATEADD with a Null value. Both branches of an IIF statement are executed whether the expression being evaluated is true or false. One way to deal with this is to encapsulate the expression logic into a custom function and then call the function using =Code.FunctionName(…)
Great article I have be going crazy trying to figure out how to combine list data for weeks now. I have one question though:
In the first example on this page both tables MUST be present on the page for this to work. If I hide the first table the Net Income field reads $0. Is there a way to show only the second table in the report and still have the calculation perform as expected?
Hi Paul,
I have a matrix report with only one row group. I need to calculate percentage between two rows in the row group. I have write the following expression but it do not work. “iif(Fields!Col.Value = “Discount % on Revenue”,iif(Fields!Col.Value = “Discount”,Sum(Fields!value.Value),0)/iif(Fields!Col.Value = “Turnover”,Sum(Fields!value.Value),0),Sum(Fields!value.Value))”. Can you please help. Thank you
I retyped the entire code and now I get this message
There is an error on line 8 of custom code: [BC30205] End of statement expected.
Hi, I’ve created an example of this report and added the RDL file after the last screen image. I copied and pasted the code right out of the post. Please take a look and let me know if you have any issues.
Thank you Paul, your example rdl file helped. I now have the feature working. Can you tell me how I can reverse it so it goes from lowest value = Green and Highest value = Red?
Just reverse the assignment of the OutR and OutG variables in the function. I’ve updated the code and uploaded a new example to the original post. The new code has a Direction parameter. You should be able to use the values of these three variables to output any color you want using some logic to do the color mixing math.
Function HeatMapColor(ThisValue As Decimal, Direction As String) As String
Dim OutR As Byte, OutG As Byte, OutB As Byte
OutB = 0
If Direction = “RtoG” Then
OutR = 255 – ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) * 255)
OutG = 255 * ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) )
Else ‘GtoR
OutG = 255 – ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) * 255)
OutR = 255 * ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) )
End If
Return “#” & Right(“0” & Hex(OutR), 2) & Right(“0” & Hex(OutG), 2) & Right(“0” & Hex(OutB), 2)
End Function
Paul I don’t see an attached example in the post now
>
Sorry. Something went awry with the page update. It’s there now.
this is what I have
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
It looks like the problem might be with the double quote characters. Text editors and word processors often replace quote characters with “smart quotes”. Try taking this into NotePad and retyping the quotes and then copy the code back into the SSRS code editor.
I get The is an error on line 8 of custom code: [BC30037] Character is not value
When implementing this I get error:
BC30289 Statement cannot appear within a method body. End of method assumed.
I have an report that extends your Heat map sample a little. Say for example you have a grouiping above region, say Country. Now within the Region you want all four cells with the same background/fill of the smallest number. Is there a way to do that?
How to block right click on SSRS Report by using javascript?
This isn’t something that Reporting Services was designed to support. Perhaps if you could explain what you are trying to accomplish, in greater detail, I can offer some suggestions.
I found this Article very useful. I have similar to this scenario problem that i’m facing. I have an assignment in the report that i have to bring data from 2 different datasets and each dataset from different database to display in the same data regions. here is example of my query
Dataset UnitProd: Select ID, Description, permitTypeID, OverallQouta from Units where permitTypeID = @PermitTypeID // This from Prod database
Dataset UnitTest: Select ID, Description, permitTypeID, OverallQouta from Units where permitTypeID = @PermitTypeID // This from Test Database
Here is my report will look like
ID | Description | OverallQouta | | ID | Description | OverallQouta |
54| AA Unit | 10 | | 54 | AA Unit | 15
*************************************************************
the requirement to compare between 2 database for OverallQouta if overallQouta is not the same will highlight that row.
for now i use Lookup to bind dataset to display data in the report but when i compare i use expression something like
=IIF(First(Fields!OverallQouta.Value, “UnitProd”) First(Fields!OverallQouta.Value, “UnitTest”),”Yellow”,”Transparent”)
but when it compare and check that overallqouta is not the same it will highlight very row , what i want i just want to highlight only row that different.
Do you have an advise for me to accomplish this requirement?
Thank you.
Can custom code be used in the navigation property of an report element?? If so how?
Sure. Use an expression to build the target path for a URL action. Expressions can call into an embedded VB function in the report or a custom assembly.
Very useful to me. Thanks a lot!
Nice Article. Many Thanks.
For those who are novice to SSRS, make PersSal as Public Shared instead of just Public