Quickie – Prompt parameters into rows

Creating rows from a prompt

Here’s an interesting request. A developer wants to populate a query set using a prompt. The values don’t exist in a table, so a simple filter won’t work. How can we get the query to have the rows? Turns out there’s a really easy way.

Cognos SQL is based on the SQL-92 standard, which gives us a few very nice features. One of them is the values() keyword.

values() let’s us define a set, sort of like a union query. Plenty of info here: https://modern-sql.com/feature/values.

An example query might be:

SELECT
    parameterValue
FROM (VALUES 
    ('Scenario 1')
  , ('Scenario 2')
  , ('Scenario 2')
  )  query(parameterValue)

That would create a list with 3 rows, Scenario 1-3.

So how can we use this in Cognos? A prompt macro with a split and join works wonders.

SELECT
    parameterValue
FROM (VALUES 
  (#join('),(',split(',',promptmany('Scenarios','string',sq('N/A'))))#)
    ) query(parameterValue)

The values passed to the parameter will appear as ‘Scenario 1′;’Scenario 2′;’Scenario 3’, the join/split functions will effectively replace the semicolons with ),(. This ultimately makes the SQL match the standard.

Check out the report here: params-to-rows.txt (5028 downloads)