using IIF() in SSRS DataTable Filter

Question

Post reply

using IIF() in SSRS DataTable Filter

Don Shields-368041

Ten Centuries

Points: 1189
More actions
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

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

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

dreamthebest Ten Centuries Points: 1324 More actions · Answer 1

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

IIF((condition),true,false)

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

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.

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 3

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

thx

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 4

Don Shields-368041

Ten Centuries

Points: 1189

August 6, 2010 at 9:59 am

#1203642

no difference.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

The param "SelectedItemList" is definitely a multivalue parm?

Steve.

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 6

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

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 7

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 8

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.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 9

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

Steve.