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,480 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

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 I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

734 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