Post reply

using IIF() in SSRS DataTable Filter

  • August 5, 2010 at 3:59 pm

    #227509

    I have a report that has dynamic groups. On the DataTable itself I have a filter like this:

    ================================

    (left side):

    iif(Parameters!dsSalesStatus_QUERYNUM.Value="4",Parameters!SelectedItemList.Value(0),parseItemId(Fields!ItemId.Value,Parameters!AX_CompanyName.Value))

    Operator: In

    (right side):

    Parameters!SelectedItemList.Value

    ==================================

    whenever the QUERYNUM value is not 4 this works fine. I have used an iif statement in the filter expression many many times but this one just doesn't work.

    Both options of the Iif statement work individually if I remove the iif() and hard code it to the value that should be selected.

    Any suggestions?

    Thanks,

    Don Shields

  • August 6, 2010 at 2:31 am

    #1203297

    Can you try enclosing the first statement in iif within the braces()

    IIF((condition),true,false)

  • August 6, 2010 at 9:30 am

    #1203612

    What is the datatype of the parameter used in the IIF? If it's an integer, you shouldn't have the "4" quoted right?

    Steve.

  • August 6, 2010 at 9:40 am

    #1203624

    It's a string but I have tried it both in and out of quotes as well as CStr(4). Same results.

    thx

  • August 6, 2010 at 9:59 am

    #1203642

    no difference.

  • August 6, 2010 at 10:17 am

    #1203655

    The param "SelectedItemList" is definitely a multivalue parm?

    Steve.

  • August 6, 2010 at 11:07 am

    #1203683

    After further testing it looks like what is happening is that the iif() evaluates the formula in the False portion of the function even if that is not what the logic is going to select. In the case where the test value ="4" the field Fields!ItemId.Value doesn't have anything in it, which seems to be causing a problem.

    Don

  • August 6, 2010 at 11:15 am

    #1203687

    Finally figured it out. Had to rewrite the datamethod parseItemId() to work even if the parameter that was passed to it is blank or nothing. Even though the logic will not select a value in an if statement it appears that the value must still be valid.

    Don

  • August 6, 2010 at 11:20 am

    #1203694

    Ugh, forgot about the crappy IIF. According to Bruce Loehle-Conger (post here) your best bet is to move the logic to code behind the report (I'm guessing this is embedded code).

    Steve.

  • August 6, 2010 at 11:21 am

    #1203696

    Sorry, missed your 'solved it' post. Glad you got it working. That IIF evaluation isn't nice.

    Steve.

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

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