IIF gives error, but shouldn't

This one's driving me nuts.

I'm trying to show a "variance from revenue budget" column in an otherwise functioning report. The basic formula is:

(((Fields!budget.value-Fields!actual.value)/Fields!budget.value)*-1)

which displays as:

Agent

Variance

Budget

Sales

John

20%

$80

$106

Mary

#Error

$0

$50

I want Mary to display 0%, but division by 0 gives an error. Fair enough. Let's test for a 0 value:

IIF(Fields!budget.value = 0,0,5)

Agent

Variance

Budget

Sales

John

5%

$80

$106

Mary

0%

$0

$50

Okay, the test works. So this should work, right:

IIF(Fields!budget.value = 0,0,(((Fields!budget.value-Fields!actual.value)/Fields!budget.value)*-1))

Nope:

Agent

Variance

Budget

Sales

John

20%

$80

$106

Mary

#Error

$0

$50

How about reversing the terms:

IIF(Fields!budget.value > 0,(((Fields!budget.value-Fields!actual.value)/Fields!budget.value)*-1),0)

Nope:

Agent

Variance

Budget

Sales

John

20%

$80

$106

Mary

#Error

$0

$50

So I have an IIF statement where either terms works alone, the test works, and I still get #Error displayed in the cell. I've also tried going back to the SQL and putting the NULL values back to NULL and using IsNothing(Fields!budget.value) as my test--same result. Any other ideas?

IIF(Fields!budget.value > 0,(((Fields!budget.value-Fields!actual.value)/Fields!budget.value)*-1),0)

For the Fields!actual.value, is it null?

|||

IIF is a function call of the VB runtime, hence all arguments are evaluated before the function is invoked. In your case, the devision by Fields!budget.value is triggered a division by zero exception.

You can use the following expression:

= -1 * iif(Fields!budget.Value <=0, 0, (Fields!budget.Value-Fields!actual.Value) / iif(Fields!budget.Value > 0, Fields!budget.Value, 1))

Alternatively, you can write an IF - ELSE statement in a custom code function to avoid the division by zero and call it from the expression.

-- Robert

IIF driving crazy

This is driving me crazy…

=IIF(Fields!COUNTRY.Value = "United States","US",

IIF(Fields!COUNTRY.Value = "UNITED STATES", "US", Fields!COUNTRY.Value))

Is that not correct ?

=Iif(Fields!Country.Value = "United States", "US", Nothing)

=Iif(Fields!COUNTRY.Value = "United States", "US")

=Iif(Fields!COUNTRY.Value, "misb_db_prp1" = "United States", "US", Fields!COUNTRY.Value, "misb_db_prp1")

=Iif(Fields!COUNTRY.Value = "United States","US", Fields!Country)

Can you please correct what im doing wrong?

Thanks

Can you be more specific with your problem? What is happening when you try to use this expression? Thanks.|||

i want to display only "US" wherever = "United States" or "UNITED STATES"

i have tried but nothing is change still "United States" is displaying

=Iif(First(Fields!COUNTRY.Value, "db")

= "United States", "US", First(Fields!COUNTRY.Value, "db"))

|||

Try this:

=IIF(Fields!COUNTRY.Value.ToString().ToLowerInvariant().Equals("united states"),"US", Fields!COUNTRY.Value)

|||

getting this error:

'ToLowerInvariant' is not a member of 'String'.

|||

It's part of SSRS2005.... if you bring up an expression editor dialog, and type something like this:

=1.ToString().ToLowerInvariant()

the intellisense should popup as soon as you type the period after the ToString(), and you can scroll nearly to the bottom and see if it is there.

Also, instead of nesting several IIf statements, it can be easier to use a Switch statement.

sluggy

|||

i'm using sql server reporting 2000 so there is no intellisense

can you give me the samp of using switch statement ?

|||

try

=Iif(trim(Fields!Country.Value) = "United States", "US", "")

|||

i have other countries other then USA

=Iif(trim(Fields!Country.Value) = "United States", "US", "")

the above code will be blank, if i have countries other then US

is that not correct?

thanks

|||

What you need is this:

=Iif(trim(Fields!Country.Value) = "United States", "US", Fields!Country.Value)

The IIf is similar to the tenary "?" statement. The syntax for the IIf statement is:

IIf(<expression>, <val/expr to return if true>, <val/expr to return if false>)

All this is included in the books online if you have it installed.

sluggy