Two Optional Prompts - To create conditional filter

[imts]

I have a issue in my report where I need a filter condition as described below.

Issue:

I have two OPTIONAL prompts. Say- Option1 and Option2. Both of them filter one column – say "Column1".

Option1 is listbox containing multiple values.
Option2 is single check-box static choice which has only one hard-coded value – say- "Value2".

User can select any one of them or both of them. The filter should work like this –

If ONLY Option1 prompt values are chosen-
Filter: [COLUMN1] in ?paramOption1?

If ONLY Option2 prompt  is checked-
Filter: [COLUMN1] in ('Value2')

If BOTH listbox and checkbox value is chosen-
Filter: [COLUMN1] in (?paramOption1? , 'Value2')


I have tried CASE statements/AND OR clauses/creating 3 optional filters , etc... but its not working for me.!!!.
Any help in this regard will be greatly appreciated.

[tjohnson3050]

One filter:

([COLUMN1] in ?paramOption1? and ?Value2? is null)
or
([COLUMN1] in ?Value2? and ?paramOption1? is null)
or
([COLUMN1] in ?Value2? or [COLUMN1] in ?paramOption1?)

[imts]

hi John,

I used the same filter but somehow it doesnt work for the first two conditions (i.e when either of the parameters are NULL).

When either ParamOption1 or Value2 is NULL - it doesnt filter anything.

Can u suggest what can go wrong in this ??? I am struggling since many days on this now...

-Tarun

[tjohnson3050]

Try this:

([COLUMN1] in ?paramOption1? and ?Value2? is null)
or
([COLUMN1] in ?Value2? and ?paramOption1? is null)
or
(?paramOption1? is not null and ?Value2? is not null
and
([COLUMN1] in ?Value2? or [COLUMN1] in ?paramOption1?))

[imts]

Still the same result.  :(

I have the feel that when either of the parameter is NULL ... then OR'ing it is somehow is turning the whole expression in NULL.

ONLY when we select BOTH of them - this filter works !

I am pasting the actual filter with actual names - just in-case I m not doing anything stupid.

"(([STUDY_SITE_GROUP] in ?pStudySiteGroup?) and (?pMultiStudySiteGrp? IS NULL))
or
(([STUDY_SITE_GROUP] in ('Multi-Site')) and (?pStudySiteGroup? IS NULL))
or
(?pMultiStudySiteGrp? IS NOT NULL AND ?pStudySiteGroup? IS NOT NULL
AND
([STUDY_SITE_GROUP] in ('Multi-Site')  or [STUDY_SITE_GROUP] in ?pStudySiteGroup? ))"

[tjohnson3050]

It looks like your are not using the parameter name for multistudysitegrp correctly everywhere. 


You said:

"(([STUDY_SITE_GROUP] in ?pStudySiteGroup?) and (?pMultiStudySiteGrp? IS NULL))
or
(([STUDY_SITE_GROUP] in ('Multi-Site')) and (?pStudySiteGroup? IS NULL))
or
(?pMultiStudySiteGrp? IS NOT NULL AND ?pStudySiteGroup? IS NOT NULL
AND
([STUDY_SITE_GROUP] in ('Multi-Site')  or [STUDY_SITE_GROUP] in ?pStudySiteGroup? ))"

Try:

"(([STUDY_SITE_GROUP] in ?pStudySiteGroup?) and (?pMultiStudySiteGrp? IS NULL))
or
(([STUDY_SITE_GROUP] in (?pMultiStudySiteGrp?)) and (?pStudySiteGroup? IS NULL))
or
(?pMultiStudySiteGrp? IS NOT NULL AND ?pStudySiteGroup? IS NOT NULL
AND
([STUDY_SITE_GROUP] in (?pMultiStudySiteGrp?)  or [STUDY_SITE_GROUP] in ?pStudySiteGroup? ))"

[Bark]

Another approach:

[STUDY_SITE_GROUP] in (#promptmany('pStudySiteGroup','string','dummyValue')#,#prompt('pMultiStudySiteGrp','string','dummyValue')#)

Where "string" is the data type of the values returned by the prompt and dummyValue is a value that will never be in [STUDY_SITE_GROUP].

Hope it helps.

Regards,

Bark

[imts]

John: Nope mate ... still not working .. I had replaced the ?pMultiStudySiteGrp? with direct string 'Multi-Site' because Checkbox static-choice "USE VALUE" was 'Multi-Site' .. So anyways it was the same thing - pass it via parameter or directly.

Bark: This logic fails because when there is no selection for either of the parameter then we pass 'dummyValue' .. So on NO selection the filter is formed like  [STUDY_SITE_GROUP] in ('dummyValue') and the report comes as Blank.

More suggestions please ??  ???

-Tarun

[Bark]

You said the MultiSet is a yes/no question, isn't it?

You could make it a 2 options prompt (Yes/No) with No as default and UseValue "dummyValue'. Then use the expression:

IF (?pMultiStudySiteGrp? = 'dummyValue')
THEN ( [STUDY_SITE_GROUP] in (#promptmany('pStudySiteGroup','string','[STUDY_SITE_GROUP]')#) )
ELSE ( [STUDY_SITE_GROUP] in (#promptmany('pStudySiteGroup','string',''dummyValue')#,?pMultiStudySiteGrp?) )

I'll keep thinking though, I might come with something easier...

Regards,

Bark