#Error for calculation in a textbox

  • 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))

  • I suspect this line should use IIF like the others, shouldn't it?

    -SUM(IF(Fields!strType.Value="Rent_Tax",Fields!intDT.Value,0))

     

  • Thank you for the good eye but that did not fix the error.

  • 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.

  • 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))

    "The Bible tells us to love our neighbors, and also to love our enemies; probably because they are generally the same people." G. K. Chesterton

  • 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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply