=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?
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") )