Search code examples
reporting-servicesexpressionfield

How to write an expression for SSRS where the average is taken of a column or field that is first being filtered by another field value?

I am new to SSRS and I'm needing to add a field with an expression that will take the average of another field. This field being added will be in the header of the report for general info. The tricky part is that this column is shared between two different record types being returned and in the header these record types will be represented separately as averages.

The types are Stage and activities. and the shared column is the turn around time for days taken to complete the stage or activity.

This is far from correct but hopefully someone will be able to send me the correct expression.

=AVG(Fields!SATURNAROUNDTIME.Value, "rpt_CaseTurnAroundTime" WHERE First(Fields!LEVEL.Value, "rpt_CaseTurnAroundTime") = 1)

The field that has both values for stage and activity is SATURNAROUNDTIME and the field that I'm thinking I can use as a filter is level which has stage represented as 1 and activity as 2.

The expression above will be going on a field I'm adding to the header. I will end up having 2 one for stage average and activity average.

Solution

  • To filter inside an Aggregate function, the IIF can be used. NOTHING is used when false so that it isn't included in the Average calculation (while 0 would).

    =AVG(IIF(Fields!LEVEL.Value = 1, Fields!SATURNAROUNDTIME.Value, NOTHING))