Solved

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

Posted on 2010-08-23
15
1,492 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:louise001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +2
15 Comments
 
LVL 10

Accepted Solution

by:
itcouple earned 250 total points
ID: 33505565
Hi

You are missing .value :)

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

Hope that helps
Regards
Emil
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33505588
Change <>"" with Is Not Nothing
Change AND WIth AndAlso
0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33505707
@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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:louise001
ID: 33505754
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
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33505828
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
 

Author Comment

by:louise001
ID: 33505872
Hi Zhaolai,
Thanks for reply, still all black though.
0
 
LVL 27

Expert Comment

by:planocz
ID: 33505911
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
 
LVL 27

Expert Comment

by:planocz
ID: 33505923
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
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33505934
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
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33505942
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
 
LVL 17

Assisted Solution

by:Zhaolai
Zhaolai earned 250 total points
ID: 33507390
Try this simpler syntax:

=IIf(Val(Fields!AvgHours.Value) >= 8.4, "Red", IIf(Val(Fields!AvgHours.Value) < 8, "Black", "Blue"))
0
 

Author Comment

by:louise001
ID: 33508663
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
 

Author Comment

by:louise001
ID: 33508710
Hi Zhaolai,
Thanks for the suggestion, that's much simpler and it works.
0
 

Author Comment

by:louise001
ID: 33508715
Hi Planocz,
I've decided to use Zhaolai's simpler suggestion but still wanted to say thanks.
Louise
0
 

Author Closing Comment

by:louise001
ID: 33508720
Thanks very much
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question