Optional Promptmany filter in model query subject

[cognosbi]

Thanks for taking time to look at this issue.

Here is the situation:

Have created a model query subject.
Have a promptmany filter which should be Optional.
This is the expression for the promptmany.
[Logical View].[Query Subject].[PROD_CD] in (#promptmany('Select a Product Code','string',sq('Not Selected'))# ) OR ('Not Selected') in (#promptmany('Select a Product code','string',sq('Not Selected'))#)

And have determinants for all the query items as group by.

The issue is, when I test the query subject, it asks for the prompt value, I don't give any value input (because its optional), then the query subject gives the error for QE-DEF-0482 Cannot parse an empty expression. If I provide a value it is working fine.

Noticed that, when I don't use the determinants, I am not getting the error. But if I have even one determinant, it throws up the error.

Wanted to know whats the relation between the promptmany and the determinants. And if you have any suggestions to make this work.

Thanks,

[CognosPaul]

When you don't select anything the filter will look like:

[Logical View].[Query Subject].[PROD_CD] in ('Not Selected' ) OR ('Not Selected') in ('Not Selected')

The fact that [Logical View].[Query Subject].[PROD_CD] exists in the query means the join is being created (assuming it's not in the same table).

A better way to handle the prompt many is to use the leading and following text.
#prompt/promptmany(
'ParameterName'
,'DataType'
,'Default'
,'Leading Text'
,'Source'
,'Following Text')#

So you could do something like:
#promptmany('Select a Product Code','string','1=1','[Logical View].[Query Subject].[PROD_CD] in (','',')')#

If the user doesn't select anything the filter will be 1=1. If the user does select something the filter will then be something like:[Logical View].[Query Subject].[PROD_CD] in ('a','b','c')

[cognosbi]

Thanks Paul for your suggestion.

Found that, the way I was using promptmany and the way you proposed, both worked.
Although, your expression was much better in terms of readability and understanding.

The only problem was that filter USAGE needed to be "ALWAYS" rather then "OPTIONAL".

That way, because the filter is mandatory, so it WILL prompt, and because of the expression condition in the filter, the user can choose or leave the prompt blank and hit OK and get the data for either the selected PROD_CD's OR for all the PROD_CD's.

Thanks once again!!