August 16, 2022 at 7:29 pm
I am getting the #error when trying to sum a calculated field in ssrs text box. I have seen posts regarding division by zero but this has none of that.
The calculated field is appearing correctly in the detail row and I get the error when I try to sum it my equation is
=SUM(IIF(Fields!strType.Value="CreditsAndDeposits",Fields!intDT.Value,0))
+SUM(IIF(Fields!strType.Value="Refunds",Fields!intDT.Value,0))
-SUM(IF(Fields!strType.Value="Rent_Tax",Fields!intDT.Value,0))
-SUM(IIF(Fields!strType.Value="Sales_Tax",Fields!intDT.Value,0))
-SUM(IIF(Fields!strType.Value="Cost_of_Insurance",Fields!intDT.Value,0))
August 16, 2022 at 9:44 pm
I suspect this line should use IIF like the others, shouldn't it?
-SUM(IF(Fields!strType.Value="Rent_Tax",Fields!intDT.Value,0))
August 16, 2022 at 11:08 pm
Thank you for the good eye but that did not fix the error.
August 17, 2022 at 6:51 am
So the equation is working inside a tablix?
The same equation is not working inside a separate text box?
As the text box is outside of the tablix and therefore dataset, you need to tell the equation the data set to where to find the field.
I forget the syntax as not used SSRS in a while but create a new text box and drag in one of the fields and it should show how to reference the field with the dataset name which you then need to apply to the rest of the fields in the equation.
August 17, 2022 at 5:17 pm
I had a similar problem in a few instances where I found that I needed to change the zero value to "Nothing" to get it to work. I never really figured out why, but you might try it...
=SUM(IIF(Fields!strType.Value="CreditsAndDeposits",Fields!intDT.Value,Nothing))
"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me
August 17, 2022 at 9:20 pm
I was able to fixthe error if anyone else has the issue. I had ti put CINT infront of the called fields.
=SUM(IIF(Fields!strType.Value="CreditsAndDeposits",CInt(Fields!intDT.Value),CInt(0)))
+
SUM(IIF(Fields!strType.Value="Refunds", CInt(Fields!intDT.Value),CInt(0)))
-
SUM(IIF(Fields!strType.Value="Rent Tax",CInt(Fields!intDT.Value),CInt(0)))
-
SUM(IIF(Fields!strType.Value="Sales Tax",CInt(Fields!intDT.Value),CInt(0)))
-
SUM(IIF(Fields!strType.Value="Cost of Insurance",CInt(Fields!intDT.Value),
CInt(0)))
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply