reporting-servicesssrs-expression

how do I get the average of lookup value from ssrs?


=Lookup(dsc.Value,Dsc.value,Fields!Fmotion.Value,"Stage")

I want to get average of that lookup value. How can i write an expression that shows average of that lookup value?


Solution

  • Lookup(x,x,x,x) only returns a single value; the first match in the dataset that it finds and the average of a single value is obviously that value. If you want the average of all values that match your Lookup condition, use the LookupSet function to return multiple values. Then you can use the code behind ability to embed VB code and return an average for the LookupSet:

    Function AvgLookup(ByVal items As Object()) As Decimal
    If items Is Nothing Then
        Return Nothing
    End If
    
    
    Dim suma As Decimal = 0
    Dim avga As Decimal = 0
    Dim counta As Integer = 0
    
    For Each item As Object In items
        If Not item Is Nothing Then
            counta += 1
            suma += Convert.ToDecimal(item)
        End If
    Next
    
    If counta > 0 Then
        avga = suma / counta
    Else
        avga = 0
    End If
    Return avga
    End Function
    

    which you call in an expression on your report like:

    =Code.AvgLookup( Lookupset(dsc.Value,Dsc.value,Fields!Fmotion.Value,"Stage") )