RESOLVED: How to create a list/query of values from a Multi-Select Prompt

[adam_mc]

I need to create a query that returns all the values from a Multi-Select Prompt.
There may be no Cognos and/or Oracle data values that represent these parameters.

Any thoughts would be greatly appreciated.
Thanks in advance,
Adam.

[RichardP]

Hi

Wondering if you provide provide some additional info...

Are you interested in the values  available for selection in the prompt OR the values selected?
Is the prompt based on a Query ?   
But that seems too easy as you could just create a List from the same query used for your Prompt - so I assume I am missing something.

Can to clarify?

[adam_mc]

I just want to make a query that returns every one of the parameter values with a single row for each.

For example...

A prompt that allows a user to enter values 1, 2, 3 etc. (as many values or whatever values the user wants)
and, then return those values in query as:

1
2
3
...
...


 

[CognosPaul]

Do you want it in the query or in the output?

If the output:

Create a query with the following data item ParamLIDisplay:

Code Select Expand

'<li>'
+#sq(join('</li><li>',split(';',promptmany('test','token'))))#
+'</li>'

Replacing 'test' with your parameter name.

Drag in a singleton set to that query, and add the data item in the properties.
Drag a rich text item into the singleton, set it to report expression, and use the following

Code Select Expand

'<ul style="list-style-type:none">'
+[Query1].[ParamLIDisplay]
+'</ul>'

Note the inline style setting the list type.


If you want a query that does it, there's another way that involves a bit more work.

[CognosPaul]

found it. create a new query with Cognos SQL and paste in the following code

Code Select Expand

SELECT
    a
FROM
    (
    VALUES
#'('+join('),(',split(',',promptmany('test','string','ab')))+')'#
    ) query(a)

[adam_mc]

Thanks CognosPaul - I appreciate your help (and your help in resolving other users issues - I always find it instructive).

I wanted to get the values into a query.
I eventually found another resolution that I'd used previously in an old job - I've just started a new position and didn't have my Cognos Tips and Techniques readily available.
Anyway, similar to your solution, I created a Cognos SQL  containing the following statement:

with test as (select replace(#csv(array(promptmany('parmList','Token')))#,'''')  as col from dual)
select regexp_substr(col, '[^,]+', 1, level) as result from test
connect by level <= length(regexp_replace(col, '[^,]+')) + 1 

Note:
This is someone else's solution not mine! 
I would credit them, but I don't have their details available to me.

Thanks,
Adam.