A little help IIF

Question

Post reply

A little help IIF

Kerrie Sorrell

SSC Eights!

Points: 861
More actions
November 21, 2005 at 7:08 am

#188601

Hi all,
I have to rewrite a few reports from Cognos to Reporting Services. I was hoping someone could help me with some of it. Does the "IN" work in reporting services? The error code I get is [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
I can not figure out what else it could be besides the IN. Is there something else I can use?
=IIF( Fields!COST_ELEM_CTGY.Value ="G", "2G&AC",
(IIF( Fields!COST_ELEM_CTGY.Value IN "C", "P", "R", "V", "X"), "3OTHERS"),
(IIF( Parameters!WBSPrompt.Value = "W",
"1WBS")),
(IIF( Fields!COST_ELEM_CTGY.Value = "O" ,
"1OTHER",
"1FUNCTION")))
Thanks in Advanced,
Kerrie

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Tony Shanks SSCommitted Points: 1654 More actions · Answer 1

Missed the parentheses around your in clause list.

(IIF( Fields!COST_ELEM_CTGY.Value IN "C", "P", "R", "V", "X"), "3OTHERS"),

should be

(IIF( Fields!COST_ELEM_CTGY.Value IN ("C", "P", "R", "V", "X")), "3OTHERS"),

Kerrie Sorrell SSC Eights! Points: 861 More actions · Answer 2

Thanks for your help, I learned something new today. I did change it like you said and got the same error. So I changed it around a little more. Still getting the dame error. I can not see what I am doing wrong.

=IIF(Fields!COST_ELEM_CTGY.Value = "G", "2G&AC",

IIF( Fields!COST_ELEM_CTGY.Value IN ("C", "P", "R", "V", "X"),

"3OTHERS"),

IIF( Fields!COST_ELEM_CTGY.Value = "O" ,

"1OTHER"),

IIF( Parameters!WBSPrompt.Value = "W",

"1WBS"),

"1FUNCTION")

This is going to drive me crazy. And i have quite a few calculated fields like thid.

Thanks,

Kerrie

nigel. SSChampion Points: 11636 More actions · Answer 3

You'll need to use the InStr function eg.

Iif( InStr("CPRVX", Fields!COST_ELEM_CTGY.Value)>0 , ...

See docs here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctinstr.asp

Nigel
How to post questions

Kerrie Sorrell SSC Eights! Points: 861 More actions · Answer 4

Thanks, that did it!!!!!!!!! Learned something new also!!!!

RSCERO SSC-Addicted Points: 406 More actions · Answer 5

I'm having a strange problem with IIF and InStr. Below is the actual statement being used:

=IIF(InStr(Fields!DivisionName.Value,

"Division")=0,Fields!DivisionName.Value,Left(Fields!DivisionName.Value,InStr(Fields!DivisionName.Value,"Division")-1))

I'm checking the DivisionName.Value in order to strip off "Division" when added to the end of a division name in the data. For example "NorthEast Division", "Southern Division", etc. It works fine except for one value we recieve. We get "-" as a division when the division is unknown. (Don't ask me why, I didn't set it up) When this data element occurs, I get a #Error returned, and the error warning of:

[rsRuntimeErrorInExpression] The Value expression for the textbox ‘DivisionName’ contains an error: Argument 'Length' must be greater or equal to zero.

It appears to be executing the "false" section of the IIF clause regardless of the expression evaluation. I checked the return value of InStr(Fields!DivisionName.Value) when the value is "-" and it is always 0, so the evaluation should work.

Any ideas out there?

Kerrie Sorrell SSC Eights! Points: 861 More actions · Answer 6

"=IIF(InStr(Fields!DivisionName.Value,"Division")=0,Fields!DivisionName.Value,Left(Fields!DivisionName.Value,InStr(Fields!DivisionName.Value,"Division")-1))"

I think it should be...

Left(Fields!DivisionName.Value, 'some positive number')

I do not think it can be a negative number. Maybe I'm wrong. Just a guess.

Thanks and have a great day,

RSCERO SSC-Addicted Points: 406 More actions · Answer 7

It is a positive number if the evaluation condition is met. If the evaluation condition is not met, it should just display the DivisionName.Value and not attempt to do the Left function in the false section of the IIF clause.

Kerrie Sorrell SSC Eights! Points: 861 More actions · Answer 8

"=IIF(InStr(Fields!DivisionName.Value,"Division")=0,Fields!DivisionName.Value,Left(Fields!DivisionName.Value,InStr(Fields!DivisionName.Value,"Division")-1))"

I would take it a part....

Try something like..

=IIF(InStr(Fields!DivisionName.Value,"Division")=0,Fields!DivisionName.Value,NOTHING)

Or something like that.

Again, I am guessing, but i usually figure it out when I take the expression or SQL a part.

Also I am assuming you are using 2005?

Thanks,

RSCERO SSC-Addicted Points: 406 More actions · Answer 9

Thanks Kerrie. I've tried taking it apart, changing the evaluation, changing the true/false order etc. Each piece works fine independantly, but when put together, the IIF returns an error. I even dummied up the DivisionName.value so it wasn't a '-' and it worked fine. I don't know if there's an error in the InStr function when the string being searched for is larger than the string being searched, or if there's something else going on here. Maybe it's the "-" that's causing the problem?

And yes, I'm using 2005

Edit: Ok, it's not the "-". I tried dumming up the value to be "x" and get the same error.

Strange goings on here. It only works if the word "Divsion" actually exists in the DivisionName.Value, It appers that the IIF statement is ALWAYS executing the false section of the statement.

Test 1: InStr(Fields!DivisionName.Value,"Division")=0

I tried changing the divison name to 'xxxxxxxxxxxxx' (13 x's so the length is greater than the length of the searched for string "Division". Result: Error.

Test 2: InStr(Fields!DivisionName.Value,"Division")=3

I tried "X Division" Result: Success (X displayed).

Test 3:InStr(Fields!DivisionName.Value,"Division")=3

I tried "- Division", result: Success - displayed.

Test 4:InStr(Fields!DivisionName.Value,"Division")=0

I tried "abcdefg", result: Error

Statement:

=IIF(InStr(Fields!DivisionName.Value,"Division")=0,Fields!DivisionName.Value,Left(Fields!DivisionName.Value,InStr(Fields!DivisionName.Value,"Division")-1 ) )