Search code examples
reporting-servicesssrs-2008iif

Expression with IIF conditional showing only "ERROR" in corresponding textboxes

I am trying to run a conditional function in an SSRS expression, that conditionally renders a "-" if any of the fields are empty, and otherwise performs some arithmetic.

The problem is that the control doesn't seem to go to the second condition at all. I am getting the fields in the Report filled with "ERROR". 

=IIf(IsNothing(Fields!sales_overall.Value Or Fields!current.Value) = false, 
     "-" , 
     Fields!sales_ overall.Value/Fields!current.Value)

Can anyone point out what I'am doing wrong here?

Solution

  • There are two issues, I am seeing.

    1. You don't need to compare isNothing output to false.

    2. SSRS gives #Error because of data type mismatch.

    Make sure Sales_Overall and Current value doesn't have commas or anything that makes them text instead of a number.

    Try this first

    =IIf(IsNothing(Fields!sales_overall.Value) Or ISNothing(Fields!current.Value), 
     "-" , 
     Fields!sales_overall.Value/Fields!current.Value)

    If this doesn't work, do the explicit conversion

    =IIf(IsNothing(Fields!sales_overall.Value) Or ISNothing(Fields!current.Value) , 
     "-" , 
     CDBL(Fields!sales_overall.Value)/CDBL(Fields!current.Value))

    OR

    =IIf(IsNothing(Fields!sales_overall.Value) Or ISNothing(Fields!current.Value), 
     "-" , 
     CDEC(Fields!sales_overall.Value)/CDEC(Fields!current.Value))