SSRS Lookup() in case of Row Groups should execute for Distinct IDs

Question

My .RDL file contains:- 1.Dataset1 -> Tablix 1 (ID, Name) 2.Dataset2 -> Tablix 2 (ID, Time)

Tablix1 is Row Grouped for Id column and I want to display Totals row for Tablix1. This Total row will contain Sum(Time-> Tablix2). I have used lookup for doing this. In this scenario Lookup is performed for every row in Tablix1, whereas my requirement is Lookup should work only for Distinct IDs from Tablix1

How can I just provide the unique values to Lookup?

Is time an int, datetime, or string field?. Share sample data of both tablix and expected results. — alejandro zuleta

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-03-16T14:07:07

I've handled before this requeriment by using custom code and LookupSet function.

I recreated your scenario and created two tables similar to the description you put in the question.

An additional table is created to join both tables and calculate the sum of time as you can see below:

In the highlighted cell <<Expr>> use this code:

=Code.SumLookup(
LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Time.Value,"DataSetName")
)

Replace DataSetName by the actual name of you dataset (I think you named it as DataSet2)

Finally go to Report menu / Report Properties... and select the Code tab. In the text area put the following code:

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
    Next
    If (ct = 0) Then return 0 else return suma 
End Function

I will produce the following tablix:

EDIT: If you need the Total of time sum.

Use this expression in the cell where you need it:

=Sum(Fields!Time.Value,"DataSet2")

Let me know if this helps.

SSRS Lookup() in case of Row Groups should execute for Distinct IDs

1 Answers