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,428 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
 

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
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.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now