SSRS: Catch/Replace #ERROR value in cell

• mario17

  SSCertifiable

  Points: 6079

  More actions

  August 13, 2013 at 1:58 pm

  #281850

  Hi, all

  I have tricky tablix, and when I'm doing final TOTAL = sum all cells, it's failes because "SSRS can not deal with multiple types of values".

  SO in result I have on my cell value <#ERROR>, how I can erase/replace with spaces, or zeros.

  The original formula inside this cell is:

  =Sum(Fields!AmtX.Value * 12)

  I tried something like:

  =IIF(IsNumeric(Sum(Fields!AmtX.Value * 12)),Sum(Fields!AmtX.Value * 12),nothing)

  Hopind that

  1. It will run =Sum(Fields!AmtX.Value * 12) first

  2. Then check if #ERROR

  but it doesn't work, I think I doesn't execute original command first to evaluate status of execution.

  Thanks all

  Mario

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 13, 2013 at 2:20 pm

  #1641123

  You could try something like this:

  =SUM(IIF(IsNumeric(Fields!AmtX.Value),Fields!AmtX.Value * 12,0))

• mario17

  SSCertifiable

  Points: 6079

  More actions

  August 13, 2013 at 3:11 pm

  #1641152

  Thanks, Lauie

  It works but it doesn't fit my logic, I need to implement logic:

  if any cell in this column is empty/not numeric then don't do any calculation, in your case it's add zero.

  Is there any way to check last cell in given column for "empty", how to refer to it in tablix?

  Best

  M

• erika.butera

  Valued Member

  Points: 65

  More actions

  August 20, 2015 at 4:14 pm

  #1821560

  I have something similar but no matter what I do I keep getting the #Error can anyone help me?

  I need to find out if the string has a "[" and if it does give me that second part and if not to give me nothing or a ""

  the true condition comes fine but when is false it give me the error BUT if I replace the TRUE part with an "X" it works

  =IIF(IsNumeric(InStr(Fields!cpt_main.Value, "["))

  ,"["&Split(Fields!cpt_main.Value.ToString(),"[").GetValue(1).ToString()

  ,Nothing)