Hello All,
I got a sql query which I will be assigning to a query subject in Report studio. I got a required filter(date). I need to insert a macro for run time prompting.
The sql query is like this,
SELECT *
FROM ABCD
WHERE XYZ <> 'B'
AND DATE(PQR_TSTMP) >= <FROM DT>
AND DATE(PQR_TSTMP) <= <TO DT>
AND C_I_C IN (<CIC>)
So where should I need to insert the date promt(d_prompt) in the above sql query, So that when I run the report , It has to prompt for date.
Thanks.
Jyo....
..assigning to a query subject in Report studio..
Query subject in Report Studio or Framework Manager ?
Cognos has a prompt macro which can be used for this and you can use that in Framwork Manager. Any report using that table/query subject will inherit that prompt. This is usually done when you are dealing with the HR module.
SELECT *
FROM ABCD
WHERE XYZ <> 'B'
AND DATE(PQR_TSTMP) >= #prompt('Name of the Prompt ','date','put default value here')#
AND DATE(PQR_TSTMP) <= #prompt('Name of the Prompt ','date','put default value here')#
AND C_I_C IN (<CIC>)
I think you need to stay within Cognos syntax to work with prompts this way:
SELECT *
FROM ABCD
WHERE XYZ <> 'B'
AND CAST_TIMESTAMP(PQR_TSTMP) >= #prompt('Name of the Prompt ','date','put default value here')#
AND CAST_TIMESTAMP(PQR_TSTMP) <= #prompt('Name of the Prompt ','date','put default value here')#
AND C_I_C IN (<CIC>)