IIF in SSRS2005 will not accept greater than or less than any given number as condition for which to test

Hi,
Using iif to set the colour in an SSRS2005 report (code below) I get this error: [rsCompilerErrorInExpression] The Color expression for the textbox ‘AvgHours’ contains an error: [BC30452] Operator '<=' is not defined for types 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Field' and 'Double'. I assume RS is inferring the type double as entering other values results in the same error but with integer or string as the type.
Thanks for any help,
Louise
=iif(Fields!Group.Value <> "" and Fields!AvgHours.Value >= 8.4,
"Red",
iif(Fields!Group.Value <> "" and Fields!AvgHours.Value >= 8 and Fields!AvgHours <= 8.3,
"Blue",
iif(Fields!Group.Value <> "" and Fields!AvgHours.Value <= 8,
"Black", "Black")))

Open in new window

louise001Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
itcoupleConnect With a Mentor Commented:
Hi

You are missing .value :)

also <> "" might require cstr(Fields!Group.Value) <> ""

Hope that helps
Regards
Emil
0
 
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
Change <>"" with Is Not Nothing
Change AND WIth AndAlso
0
 
ZhaolaiCommented:
@itcouple is right. You are missing a ".Value" on line 3. Correct code:


=iif(Fields!Group.Value <> "" and Fields!AvgHours.Value >= 8.4,
"Red",
iif(Fields!Group.Value <> "" and Fields!AvgHours.Value >= 8 and Fields!AvgHours.Value <= 8.3,
"Blue",
iif(Fields!Group.Value <> "" and Fields!AvgHours.Value <= 8,
"Black", "Black")))

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
louise001Author Commented:
Doh. Thanks very much for noticing the missing .Value. The thing still doesn't work as intended though, as the forecolour is always the value specified for the final false part, black in the snippet I posted (say I change black to lime, lime is used not red or blue).

I know that's straying from the original question so I can post another.
0
 
ZhaolaiCommented:
I do not think you need the third iif. Try this:


=iif(Fields!Group.Value <> "" and Fields!AvgHours.Value >= 8.4,
"Red",
iif(Fields!Group.Value <> "" and Fields!AvgHours.Value >= 8 and Fields!AvgHours.Value <= 8.3,
"Blue", "Black"))

Open in new window

0
 
louise001Author Commented:
Hi Zhaolai,
Thanks for reply, still all black though.
0
 
planoczCommented:
Are you placing this code in the textbox properities color box?
You need to.
Try  this.
=IIF(Trim(Fields!Group.Value) <> "" AND CSTR(Fields!AvgHours.Value) >= 8.4,"Red",
 IIF(Trim(Fields!Group.Value) <> "" AND CSTR(Fields!AvgHours.Value) = 8 AND CSTR(Fields!AvgHours.Value)
0
 
planoczCommented:
Forgot to add the " " to the numbers
=IIF(Trim(Fields!Group.Value) <> "" AND CSTR(Fields!AvgHours.Value) >= "8.4","Red",
 IIF(Trim(Fields!Group.Value) <> "" AND CSTR(Fields!AvgHours.Value) = "8" AND CSTR(Fields!AvgHours.Value) <= "8.3", "Blue",
 IIF(Trim(Fields!Group.Value) <> "" AND CSTR(Fields!AvgHours.Value) <= "8","Black", "Black")))
0
 
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
try this
IIF(Fields!Group.Value IsNot Nothing AndAlso Fields!AvgHours.Value >= 8.4 = 0,"Red",
	IIF(Fields!Group.Value IsNot Nothing AndAlso Fields!AvgHours.Value >= 8, "Blue",
		IIf(Fields!Group.Value IsNot Nothing AndAlso Fields!AvgHours.Value <= 8, "Black", "Black")

Open in new window

0
 
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
sorry....
IF(Fields!Group.Value IsNot Nothing AndAlso Fields!AvgHours.Value >= 8.4 ,"Red",
	IIF(Fields!Group.Value IsNot Nothing AndAlso Fields!AvgHours.Value >= 8, "Blue",
		IIf(Fields!Group.Value IsNot Nothing AndAlso Fields!AvgHours.Value <= 8, "Black", "Black")

Open in new window

0
 
ZhaolaiConnect With a Mentor Commented:
Try this simpler syntax:

=IIf(Val(Fields!AvgHours.Value) >= 8.4, "Red", IIf(Val(Fields!AvgHours.Value) < 8, "Black", "Blue"))
0
 
louise001Author Commented:
Hi itoutou,
Thanks for the suggestion, however I get this error: [rsCompilerErrorInExpression] The Color expression for the textbox ... contains an error: [BC30020] 'Is' requires operands that have reference types, but this operand has the value type 'Integer'.
Build complete -- 1 errors, 0 warnings
0
 
louise001Author Commented:
Hi Zhaolai,
Thanks for the suggestion, that's much simpler and it works.
0
 
louise001Author Commented:
Hi Planocz,
I've decided to use Zhaolai's simpler suggestion but still wanted to say thanks.
Louise
0
 
louise001Author Commented:
Thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.