A little help IIF

  • 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",


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



    Thanks in Advanced,



  • 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"),

  • 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"),


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


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



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



  • You'll need to use the InStr function eg.

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

    See docs here:


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

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



    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?


  • "=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,

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


  • "=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..


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


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

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

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