SSRS - Returning the average where date is 2015

Question

I have been trying to figure this out for a while now and just couldn't find the answer anywhere.

I have a report in SSRS with a column group assigned to "Year", this column expands depending on what parameter the user enters into StartYear. If the user enters "2013" the report will extract all data from 2013 to 2015, this means that there are then 3 columns with the same name ("Cost").

My report looks something like this when entering StartYear as "2013" the value beneath "Year" displays the "Cost" column :

Area | 2013 ("Year") | 2014 ("Year") | 2015 ("Year")
A    |     20        |      50       |      25
B    |     15        |      65       |      35
C    |     40        |      70       |      20

Before the report get built, the reports looks something like this:

Area     |     [Year]
[Area]   |     [Cost]

I want to add a column to this report which displays the Average but only for the Year 2015.

This is what I have tried sofar but it brings back the Average for one row and all the year : 20, 50 and 25 instead of 25, 35 and 20:

=Sum(IIF(Fields!Year.Value = 2015, Avg(Fields!Cost.Value), 0))

Any help would be greatly appreciated.

Ian Preston Ian Preston · Accepted Answer · 2015-02-16T09:32:11

You need an expression like:

=Avg(IIF(Fields!Year.Value = 2015, Fields!Cost.Value, Nothing))

If you are using an IIf expression to get a subset from a DataSet, you must specify Nothing as the False part of the expression, otherwise you just get a bunch of zeroes included in the aggregate, skewing the results.

This assumes the aggregate is running outside any particular Group Scope - you can always add a Scope to the expression to make sure you are checking the entire DataSet:

=Avg(IIF(Fields!Year.Value = 2015, Fields!Cost.Value, Nothing), "DataSet1")

Edit after comment

To expand on the Scope comment above, you can specify the aggregate to run at the Row Group level by adding the Row Group name as a parameter to the expression:

=Avg(IIF(Fields!Year.Value = 2015, Fields!Cost.Value, Nothing), "Area")

Assuming Area is the name of the Row Group.

SSRS - Returning the average where date is 2015

1 Answers