Value Prompt Converting to MultiSelect from SingleSelect when enabling a filter.

[Cognos100]

I wanted to achieve a requirement for below scenario ::

Value Prompt Static Choice Collection -
Display Value      USE Value
Product              1
Customer           2

Database Records
Col1Product    Col2Customer  Col3
Null                C1                  Row1
P2                  Null                Row2
Null                Null                Row3


If in the Prompt, Value "Product" is selected, Report should show Row1 and Row3. If Value "Customer" is selected, Report should show Row2 & Row3.
If both are selected, all three rows should be shown.
It means whatever attribute is selected, rows having that attribute as NULL in the database should appear as a result set.
There are so many attributes in real requirements but just to explain the scenario, i have shown only two attribute Product and Customer.

Looks Simple so i created two new dataitems for each of the attribute value in Prompt -
DataItem1 for Product = If (Col1Product is Null and ?Prompt? In ('1')) Then (1) Else (0)
DataItem2 for Customer = If (Col2Customer is Null and ?Prompt? In ('2') Then (1) Else (0)

And Added a filter
DataItem1 In (1) or DataItem2 In (1)

The Surprise or the issue is after enabling the filter, Prompt Automatically goes to Single Select from MultiSelect. If filter is disabled, the prompt is still multiselect having both the data items.
Tried to troublshoot but seems like issue is with clause ?Prompt? inside data item itself but it starts to appear when filter is enabled.

Any suggestions...Appreciate your help

[BigChris]

I don't know if this will be any diferent, but it's worth a try. You could make your filter to be something more like:

Code Select Expand

([Col1Product] is not null and ?Prompt? contains '1') or ([Col2Customer] is not null and ?Prompt? contains '2')

You might need to tweak it a bit...that was just off the top of my head

[Cognos100]

Thanks BigChris.

I also tried that but still the MultiSelect ValuePrompt was getting converted to SingleSelect.
The solution to stop this converstion - we added a dummy filter ?Prompt? in (?Prompt?) and now we are able to have and select Multiple Values from ValuePrompt in prompt page.

Now next issue is - Even though we select multiple items, At the backend query, (?Prompt?) holds only first value and not all which were selected.
Seems solution would be to pass the multiple selections from ValuePrompt to TextBoxPrompt via PromptAPI or HTML java scripts.
If anybody has implemented the the same and could share the script, would be a great help.

[CognosPaul]

I recommend adapting this to a promptmany macro. The prompt alias just has too many places it can go wrong. In fact the only time that I actually recommend using a prompt alias is for an in_range predicate.

replace all instances of ?Prompt? with #promptmany('Prompt','string')#

The next problem is  that

Code Select Expand

?Prompt? contains '2' is implicitly a single select. If it was multi select it would look something like. ('ABC','BCA') contains '2' which simply doesn't work. Since it's a contains filter, how about:

#sq(promptmany('Prompt','token'))# contains '2' which would then become 'ABC,BCA' contains '2'

[Cognos100]

Thanks a lot CognosPaul.
It worked successfully this time.