Search code examples
visual-studioreporting-servicesexpressionrdl

#Error when using SUM(IIF( in one Report but not the other?

I am having a SUM(IIF( issue with my AR report where it is producing #ERROR and an incorrect aggregate of my data. What I want to do is have the Report calculate my Amounts across the rows and then give me a simple Grand Total with the Aging Grand Totals included.

When I click "Add Totals" to my Amount column, I receive the correct $ amount. The 4 proceeding rows are AR Aging expressions:

=SUM(iif(DateDiff("d",Fields!DateDue.Value, now())>=1 and DateDiff("d",Fields!DateDue.Value,Now())<=30,Fields!Amount.value,0))

=SUM(CDbl(iif(DateDiff("d",Fields!DateDue.Value, now())>=31 and DateDiff("d",Fields!DateDue.Value,Now())<=60,Fields!Amount.value,0)))

and so on and so forth... For one of my reports, this Aging equation is working fine (same data set and data). For this one, I receive incorrect numbers and #Errors even though the data is the same.

As you see below my 61-90 days Column with ~$600 and $400 gives me a GT of $50k which is incorrect. The image below (trust me that all other rows are $0.00) represents this.

Any ideas why this could be happening behind the report? The data is validated; it does not appear to be a DB/Data issue.

enter image description here I am guessing it is a rookie grouping issue? Any advice is appreciated.

enter image description here

Solution

  • I was able to resolve my issue:

    My Sums/Aggregate was OUTSIDE of the the row group SOURCE BU.

    Therefore, I needed to add columns to the left, INSIDE the grouping so that my totals would match for each company.