Search code examples
reporting-servicesexpressiongroupingaverageiif

Grouping Within a SSRS Expression

I have a SSRS expression where I'm calculating and an average setup time, but because of multiple, and different, postings against each order I'm needing to only take the average setup times for unique orders. Because of the need for other parts of the report to include ALL postings I cannot group or filter out the multiple order postings anywhere but this single expression. So I have the following expression:

=AVG(iif(IsNumeric(Fields!Setup.Value), CDbl(Fields!Setup.Value), 0))

And I'm essentially needing the proper syntax for the above expression rewritten to...psuedocode incoming...

=IIf((Fields!Order_Number.Value) is unique), AVG(iif(IsNumeric(Fields!Setup.Value), CDbl(Fields!Setup.Value), 0), else nothing)

I'm pretty sure a CountDistinct clause will resolve this issue, but I'm having difficulty finding the proper syntax.

Solution

  • Add a column to identify a unique row for each order:

    ROW_NUMBER() OVER (PARTITION BY YourOrderID ORDER BY YourOrderID) as RowNumber

    Add a calculated field to your dataset. Let’s call it “FilteredSetup”:

    =IIf(Fields!RowNumber.Value = 1, Fields!Setup.Value, Nothing)

    Now you can refer to this new column in your report and it will aggregate correctly:

    =Sum(Fields!FilteredSetup.Value)