Using cognos report studio prompts in pass through SQL

Question

I have created a report in Cognos Report Studio using pass through SQL syntax. My query includes a couple of common table expressions. How can i pass prompts to my query? I would like to use one optional date filter, which then is used in two cte:s. Then another required and multi choice text-filter that will be used in the final select statement.

Below is a simplified version of my query:

WITH in_date AS
    (SELECT * FROM in_dates WHERE in_date > optional_date_prompt),

out_date AS
    (SELECT * FROM out_dates WHERE out_date > optional_date_prompt),

organisation AS
    (SELECT * FROM organisation)

-- some joins and unions later i end up with this table
SELECT * FROM final_table
WHERE organisation_name = 'required_text_prompt' OR
    organisation_name = 'optional_text_prompt_value'

To use the prompts as regular cognos filters applied on the report page is not an option as the report would take hours to run.

Answer 1

To pass in parameters directly to SQL use a macro. A prompt macro for a string prompt called org would look like this:

#prompt('org','string')#

The first parameter is the prompt name and the second the type. This is the minimum amount of parameters that are needed to be specified. There are other optional parameters, such as default value that can be specified as well. You can consult the Cognos documention for more options.

You put the prompt macro in your WHERE clause:

WHERE organisation_name =  #prompt('org','string')#

Cognos will see the macro and resolve it before sending the SQL on to the data source.