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.
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?
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
...
...
Do you want it in the query or in the output?
If the output:
Create a query with the following data item ParamLIDisplay:
'<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
'<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.
found it. create a new query with Cognos SQL and paste in the following code
SELECT
a
FROM
(
VALUES
#'('+join('),(',split(',',promptmany('test','string','ab')))+')'#
) query(a)
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.