Hi,
I have a Cognos report that runs on native sql with the columns that I want to use for prompts available only in the where clause. The two columns for example, Item_No and Room_no. The requirement is to create a prompt macro (optional) on Item_No in the sql so that a textbox prompt at the report level can use the parameter to filter the records but if the user does not enter anything in the prompt then all records should be included in the output.
Secondly, a second prompt macro (optional) on 'Room_no' column which allows the user to select a value or select an 'All' option in a value prompt at the report level. Please be aware that 'All' is a dummy value which we usually use for the regular value prompts that we ceate on query items at the report level. In this case I am trying to do it with a prompt macro. Would like to mention again that I have these two columns only in the where clause of the sql query and therefore do not have the option to apply the prompts using drag and drop at the report level directly (without prompt macros).
Please provide code for the prompt macros that I can use for the use case.
Best Regards,
.