Using Custom Code to Synchronize Different Datasets

Two Datasets RefrerencedOne of my associates asked for some help with a problem I’ve solved before.  Since I didn’t have the code handy, I worked up a new example.  Until SSRS 2008 R2, Reporting Services didn’t have any built-in support for coordinating or joining two different dataset result sets together. The Lookup functions, added in that product version, can be used to effectively do the same thing I’m doing here.  For more information on the SSRS Lookup functions, visit Teo Lachev’s blog.  I actually find the Lookup function syntax confusing and continue to use my own code technique.

There are 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.  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.

Here’s a copy of the report: Two Datasets Refrerenced

(per Lorna’s 6/14/15 comment, I’ve also added a ring chart using the same expression)