SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Sum Result of SSRS LookupSet Function

SSRS provides several built-in Lookup functions:  Lookup, MultiLookup and LookupSet.  Lookup returns a single value while MultiLookup and LookupSet return a set of values. If you want to learn more about the SSRS lookup functions check out this blog post

In some situations, we may want to sum the set of values returned by MultiLookup and LookupSet.  The code shown below seems like a logical approach to accomplish this:

=Sum(LookupSet(Fields!Parent_Number.Value, Fields!Parent_Number.Value,Fields!StoreCount.Value, “StoreCount”))

Unfortunately, the built-in Sum function doesn’t work in this scenario since LookupSet returns an array of objects.  To sum the result of the LookupSet function, we need to add custom code to the SSRS report.

To add code to an SSRS report, right-click on the report background and select ‘Report Properties’. 


In the ‘Report Properties’ menu, click ‘Code’


Paste the following code in ‘Custom code’ field:

Function SumLookup(ByVal items As Object()) As Decimal
If items Is Nothing Then
Return Nothing
End If
Dim suma As Decimal = New Decimal()
Dim ct as Integer = New Integer()
suma = 0
ct = 0
For Each item As Object In items
suma += Convert.ToDecimal(item)
ct += 1
If (ct = 0) Then return 0 else return suma
End Function


We can now use the SumLookup function to sum the results from LookupSet.

=Code.SumLookup(LookupSet(Fields!Parent_Number,Fields!Parent_Number.Value,Fields!StoreCount.Value, “StoreCount”))

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.


Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...