Aggregated Lookups in SSRS 2008R2

Aggregating over Lookups in SSRS

SSRS 2008 R2 has a feature to do a lookup to another
dataset, but no way to trivially aggregate the value.

It’s a fairly simple solution: Use a small piece of code to do
the summing.

Now, you may have various LookupSets, so you don’t want to
code a function for each, so we store the values in a dictionary lookup.

Then when you query at the sum level, you just query the
dictionary

Dim dValueList As New
System.Collections.Generic.Dictionary(Of String, Decimal)

Function AddValue(ByVal sValueName
As String, ByVal dValue As Decimal)

If (dValueList.ContainsKey(
sValueName ) ) Then

                dValueList.Item(
sValueName) = dValueList.Item( sValueName) + dValue

Else

                dValueList.Add(
sValueName, dValue )

End If

End Function

Function GetSum(ByVal sValueName As
String ) As Decimal

GetSum =
dValueList.Item( sValueName)

End Function

Then the inline code in the Matrix is :

=Code.AddValue( "Code",
Lookup(Fields!Code.Value,Fields!Code.Value,Fields!LValue.Value,
"dsLookup") )

And the code for the total is :

=Code.GetSum("Code")

Obviously you’d use your own value to key the dictionary.

Easy stuff.

Download the Report.rdl and a script for the two tables I
used for testing over here:

 http://www.bidn.com/Assets/Uploaded-CMS-Files/RSLookups-6b6f7233-14a6-405d-9ecc-4bb1d412fb6f.zip