CODEWITHSUNDEEP
reporting-services
tenmiles
tenmiles

Reputation: 2751

IIF won't evaluate for both numbers and strings

Given a sample set of inputs:

.5
1
1.333333
Over
N/A
0

I have tried to conditionally format the background color using an expression such as 

=IIF(ReportItems!Onhand_PCT.Value < 1 , "Yellow",
 IIF(ReportItems!Onhand_PCT.Value = "Over" , "Red",
 "Blue"))

However, no matter how I try to fix this it will only work for either the strings or the numbers, sometimes neither, but never both. Oddly, I can (by using a conversion function on the ReportItems!Onhand_PCT.Value to make it explicitly a string or a number) get it to work for some of the rows, leaving the others with a white background (where in my IIF is "White"?).

Upvotes: 1

Views: 81

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Try:

=IIF(
   IsNumeric(ReportItems!Onhand_PCT.Value),
   IIF(ReportItems!Onhand_PCT.Value < 1,"Yellow",Nothing),
   IIF(ReportItems!Onhand_PCT.Value = "Over" , "Red","Blue")
 )

UPDATE: It seems IsNumeric is not evaluated to false when the value is a string causing an error.

Go to Report menu / Report Properties / and under the Code tab paste this custom VB function.

Public Function GetColor(ByVal value as String) As String
  Dim color As String
  Dim numberValue As Double
  if IsNumeric(value) Then
     numberValue = Cdbl(Val(value))
     color = iif(numberValue < 1, "Yellow", "Transparent")
  else
     color = iif(value = "Over","Red","Blue")
  End if
  return color
End Function

Now in the background color property you can call this custom function passing the value for Onhand_PCT value as argument to the function.

=Code.GetColor(ReportItems!Onhand_PCT.Value)

enter image description here

Note the Onhand_PCT >= 1 will be transparent, you can customize that by changing the "Transparent" in the custom function.

Let me know if this helps.

Upvotes: 2

Related Questions