Link to home
Start Free TrialLog in
Avatar of FamousMortimer
FamousMortimerFlag for United States of America

asked on

SSRS Report Builder Sum If #Error

I am driving myself crazy with this.  I cannot get sum if to work.  I have two datasets in the report and the fields for the sum if are both in DataSet1.

Originally I attempted this:
=sum(iif(month((Fields!ScheduledShipDate.Value, "DataSet1"))=month(today()),(Fields!ExtendedPrice.Value, "DataSet1"),0))

Open in new window

That was giving me  a "The Value expression for the textbox 'TextBox1' uses an aggregate expression without a scope.  Then I found that you can specify the scope within the Sum function so I changed it to this:
=sum(iif(month(CDate(Fields!ScheduledShipDate.Value))=month(today()),CDec(Fields!ExtendedPrice.Value),0),"DataSet1")

Open in new window

Now the report will run but the textbox just displays "#Error".  I have no clue what the error is or how to debug it.  It seems to me that this should surely be working.

FYI, i removed and year(..)=year(today()) just to keep it as simple as possible while i was testing it.
ASKER CERTIFIED SOLUTION
Avatar of FamousMortimer
FamousMortimer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of FamousMortimer

ASKER

thank god thats over