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,460 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

713 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