SSRS SUM IIF Returning 0 at higher group level if 0 is in calculation

Question

I have a report which aggregates data from a detail report. Basically, the Area of a building. Those are grouped into two categories, top, and bottom.

For the bottom group, if a parameter is met, they are included in the sum.

For the top group, it does the same thing, just groups at a higher level.

There are a few grouped in the bottom group which sum is 0, which is fine, but the issue is the top level then returns a 0 as well for some reason, instead of sum of the rest of the values.

The expression is =iif(Fields!Included.Value = 1,sum(Fields!Area.Value),0)

I can't figure out why it works in all but two of the top groups.

Answer 1

The issue is that the IIF is only being evaluated for the first record and not each record.

I think it will work as you expect if you put the SUM outside of the IIF:

=SUM(IIF(Fields!Included.Value = 1, Fields!Area.Value, 0))

This way it evaluates the Included value for each record and then SUMs the various Area values and zeroes.