locked
How to handle #error in ssrs 2008 RRS feed

  • Question

  • Hi,

    I am working on a report and in one of the fields the value is

    ReportItems!Textbox1683.value/ReportItems!Textbox1682.value

    which is the division of previous 2 fields and if i have any values for these fields then i get the correct value,but if i dont have any values for the previous 2 fields i mean if they are empty then it returns me #ERROR.

    I tried using isnothing and isnumeric  but its not working.

    Any suggestions please?

    Thanks

    Friday, April 27, 2012 5:00 PM

Answers

  • It is my experience the expressions are case sensitive and what I see above is that you are using lower case.  Not sure if that is causing your problem or not.

    So if you were to change the expression to this

    =iif(ReportItems!Textbox195.Value < 0 and ReportItems!Textbox194.Value < 0 ,-1, 1)

    does the #error go away when 194 and 195 are empty? 

    if so then the problem is with the rest of the if expression in which case I would change the true behavior from -(field) to -1*field.

    if not then the problem is the condition in the iif statement.  In which case I would start by trying different ways of testing the value.  you could even use new columns so you can see what is going on.. for example =IIF(Len(ReportItems!Textbox195.Value) <= 0, -1, ReportItems!Textbox195.Value).

    I think at this point you need to identify what is causing #error - the condition or the result of the condition.

    • Marked as answer by Mike Yin Wednesday, May 2, 2012 1:46 AM
    Friday, April 27, 2012 6:35 PM

All replies

  • =IIF(ReportItems!Textbox1682.Value = 0, 0, ReportItems!Textbox1683.Value/ReportItems!Textbox1682.Value)

    unless its money then you need to do this

    =IIF(ReportItems!Textbox1682.Value = 0, 0, ReportItems!Textbox1683.Value/IIF(ReportItems!Textbox1682.Value = 0, 1, ReportItems!Textbox1682.Value))

    Friday, April 27, 2012 5:18 PM
  • Hi Bushfoot,

    i am using these calculations to get  %  for the previous 2 fields and the previous fields are not 0 they are empty just blank values

    Friday, April 27, 2012 5:21 PM
  • Can you test it for an empty string?

    =IIF(ReportItems!Textbox1682.Value = "", 0, ReportItems!Textbox1683.Value/ReportItems!Textbox1682.Value)

    OR test if lengt is 0

    =IIF(Len(ReportItems!Textbox1682.Value) <= 0, 0, ReportItems!Textbox1683.Value/ReportItems!Textbox1682.Value)

    OR (I think you said you tried this one already)

    =IIF(ReportItems!Textbox1682.Value Is Nothing, 0, ReportItems!Textbox1683.Value/ReportItems!Textbox1682.Value)

    Friday, April 27, 2012 5:33 PM
  • Hi bushfoot,


    Thanks for your help .i am sorry to bug you but the requirement changed just now and now it goes this way 

    iif(ReportItems!Textbox195.value and ReportItems!Textbox194.value <0 ,-(Fields!FP_Total_GM_Per.Value),
      Fields!FP_Total_GM_Per.Value)

    the problem here is for blank values of 194 and 195  i get same #ERROR

    Friday, April 27, 2012 6:11 PM
  • So you aren't required to do the division anymore?  Doesnt that remove the #Error then?

    THIS ReportItems!Textbox195.value NEEDS TO BE ReportItems!Textbox195.Value

    THIS ReportItems!Textbox195.value and ReportItems!Textbox194.value <0 NEEDS TO BE ReportItems!Textbox195.Value < 0 AND ReportItems!Textbox194.Value < 0 (when testing for 0)

    Have you tried just putting the values in the cell so you can see what the value actually is?

    Maybe this would work... check both textboxes for empty values

    =iif(Len(ReportItems!Textbox195.Value) <= 0 and Len(ReportItems!Textbox194.Value) <= 0 ,0-Fields!FP_Total_GM_Per.Value,
      Fields!FP_Total_GM_Per.Value)

    Friday, April 27, 2012 6:19 PM
  • Hi Bushfoot,

    I am not supposed to do the division anymore and the thing here is 

    if the values of 194 and 195 are negative then -Fields!FP_Total_GM_Per.Value else it must be Fields!FP_Total_GM_Per.Value

    So,what i did was

    iif(ReportItems!Textbox195.value and ReportItems!Textbox194.value <0 ,-(Fields!FP_Total_GM_Per.Value),

      Fields!FP_Total_GM_Per.Value)

    But again for the blank values i get the #ERROR thing

    Friday, April 27, 2012 6:23 PM
  • It is my experience the expressions are case sensitive and what I see above is that you are using lower case.  Not sure if that is causing your problem or not.

    So if you were to change the expression to this

    =iif(ReportItems!Textbox195.Value < 0 and ReportItems!Textbox194.Value < 0 ,-1, 1)

    does the #error go away when 194 and 195 are empty? 

    if so then the problem is with the rest of the if expression in which case I would change the true behavior from -(field) to -1*field.

    if not then the problem is the condition in the iif statement.  In which case I would start by trying different ways of testing the value.  you could even use new columns so you can see what is going on.. for example =IIF(Len(ReportItems!Textbox195.Value) <= 0, -1, ReportItems!Textbox195.Value).

    I think at this point you need to identify what is causing #error - the condition or the result of the condition.

    • Marked as answer by Mike Yin Wednesday, May 2, 2012 1:46 AM
    Friday, April 27, 2012 6:35 PM