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

Question

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

Answer 1

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

Answer 2

In addition to the provided solution.

To use a custom function you need first to add your code to the report. You can do it the following way:

1. In Design view, right-click the design surface outside the border of the report and click Report Properties.
2. Click Code.
3. In Custom code, type the code. Errors in the code produce warnings when the report runs.

More details here.

Answer 3

Daniel's answer fully addresses this question and has also been very helpful to me. I'd like to complement with two other functions beside AvgLookup that are commonly related and might help other people in the future.

One of them is SumLookup and has been provided by someone else in another question. The other one is StDevLookup, i.e. the standard deviation of a LookupSet. I couldn't find it anywhere in Stack Overflow or elsewhere, so I wrote my own function which uses AvgLookup from Daniel's answer:

Function StDevLookup(ByVal items As Object()) As Decimal

If items Is Nothing Then Return Nothing

Dim stdeva  As Decimal = New Decimal()
Dim avga As Decimal = New Decimal()
Dim suma As Decimal = New Decimal()
Dim ct As Integer = New Integer()
Dim variancea As Decimal = New Decimal()

avga = 0 
suma = 0
ct = 0
variancea = 0

avga = AvgLookup(items) 

For Each item As Object In items
  suma += (item - avga)^2
  ct += 1
 Next

variancea = suma / ct
stdeva = Sqrt(variancea)

Return stdeva

End Function

And you call it similarly:

=Code.StDevLookup(LookupSet("RouteExample", Fields!Route.Value, Fields!SomeValueToBeCalculated.Value, "MyDataset"))