Layout calculation with multi-select prompt

[sthabinash]

I have a multiselect prompt(?p?) that displays a static value 'All' and the list items of a data Item (contain data like A,B,C...)

In the list I have a layout calculation with the expression : 

case
when (ParamDisplayValue('p') is null)
then 'All'
else
ParamDisplayValue('p')
end


Now in above case I have faced two problems:

1) When I select multiple values from the multiselect list ,i.e. A and B, then the layout calculation displays it as A,B instead of displaying it in different rows of the list. How can I display A and B in two different rows?

2) When I select the static value All by adding the following expression "when (ParamDisplayValue ('p')="All" ) then 'All'"  in the layout calculation as:

case
when (ParamDisplayValue('p') is null)
then 'All'
when (ParamDisplayValue ('p')="All" )
then 'All'
else
ParamDisplayValue('p')
end


then I get "No Data Available".  How can I solve this?

Thank you.

[jeff_rob]

Hi sthabinash,
In regard to 1), think about the fact that Cognos is concatenating all the values together to send to the database as an IN modifier of the WHERE clause. For example, if you selected check boxes A, B, and C, then Cognos sends something like WHERE field_name IN ('A','B','C') to the database.  So it's always going to concatenate the values together on one line, but it doesn't format it with the single quotes when you're just displaying the values.

On the second question, you can only solve that one by setting up a filter that evaluates the parameter and supplies the needed values.  You won't be able to combine multiple values in a single Use box of the Static Choices.

If you're using a filter do something like this:

Code Select Expand

?p? = 'All' and [your_field_name] in ('A','B','C','D')
OR
?p? <> 'All' and [your_field_name] in (?p?)

If you're using custom SQL then something like this:

Code Select Expand

AND (#prompt('p')# = 'All' and your_field_name in ('A','B','C','D')
                 OR
                 #prompt('p')# <> 'All' and your_field_name  in (#prompt('p')# )