Wednesday, August 17, 2011

Optional Prompt Macros

A common misconception that I have noticed people have with respect to optional prompt macros is with regards to Preceding / Trailing text in a prompt macro.

An optional prompt macro always has a default value specified, so if no prompt value is selected the default value is applied in the query but this will not cause the preceding / trailing text in the macro to get applied. The preceding / trailing text get applied only if a prompt value is selected.

A simple example to demonstrate this:

We have ProdID and Sales displayed in a report.

The report has an optional prompt macro for ProdId defined as:

[Presentation Layer].[REVENUE_FACT].[PRODUCTID]= #prompt('Prod Id', 'Integer' ,1, '', '', 'and [Presentation Layer].[REVENUE_FACT].[SALES] > 5000' )#

Notice that the prompt macro has a default ProdID value of 1 and a trailing text that will add an additional filter of Sales > 5000.

When no value is selected for the ProdId prompt, the output displays data for Prod Id 1. Notice that the additional Sales filter doesn't get applied.

And here is the output when a ProdId selection of 1 is made. The additional Sales filter gets applied and displays the sum total of Sales for ProdId where Sales > 5000.


Oi said...

Hi there, this is amazing support site of Cognos..please keeping posting. thanks

chiragnirmal said...

Hi Zephyr,

First of all I would like to thank you for creating this blog. Its a very useful resource for all cognos developers.

Now my question for you: Is it possible to provide a button or link in a cognos report that will schedule the current report view to be sent to them by email as excel attachment daily at a time entered by the user.

Currently its possible to do the same using a 24 step procedure which is very hard for users to remember. This procedure has been described very nicely at

I would like to make whole process automatic and allow users to use this functionality more easily.

Thanks for your help.

I am posting this again just to make sure that you see it. Please feel to delete once you see it.

Bose said...

Significant and precious information!
Sample cv

Rohit said...

Hi Zephyr,

Thanks for this wonderful blog.I have a question for you.Suppose i have 4 pages in my report, now I want such a functionality where user can select in prompt which page to display. please help.
thanks in advance

Zephyr said...

You need to create conditional variable and set the render variable property for each of the pages based on the condition.

Ana! said...

Hi Zephyr,
Can you please help in crearting a new pagination format in Cognos..

We usually have the pagination as Top, page up, page down and bottom links in cognos..

I would like to change this pagination in C8 something that looks in google web page as in

Thank you !

Zephyr said...

Hi Anal, Sorry for the delay in responding. This is a requirement that will require a lot of Javscript coding something that needs to be avoided as much as possible.

Check out the articles on Dynamic Rows Per Page and that will give you an idea on how to go about it.

I am hard pressed for time to be able to put together something on this. But let me know if the articles helped you get an idea on how to proceed on this.

Though I would advise against going with the javascript coding.

Diya said...

Hi Zephyr,
How to make this work for String data types.?
E.g. I want to display all states if not selected else display the selected state.
STATE_CD=#prompt('State','token','STATE_CD')# --> This displays all states if nothing is selected in prompt. But on selecting a specific, it is throwing an error as single quotes is missing in the string comparison.
STATE_CD=#sq(prompt('State','token','STATE_CD'))# --> This displays selected state. But is not selected anything it is displaying none. This might be coz it is applying single quotes on default value too instead of treating it as table col.
STATE_CD=#sq(prompt('State','token','STATE_CD','','','or STATE_CD=STATE_CD'))# --> Here also it was applying single quotes on trailing filter and is treating it as text but not as a filter condition.

Please suggest. Thank you.

Diya said...
This comment has been removed by the author.
Zephyr said...

Hi Diya,

Please modify it to STATE_CD=#prompt('State','String',sq('STATE_CD'))#

Let me know if this helps.

Zephyr said...

Oops. Sorry, I didn't realize that the State_CD in your macro was a table column. Try this:

#prompt('State','String',sq('All'))# = 'All' or STATE_CD=#prompt('State','String',sq('STATE_CD'))#

where the default selection is nothing is chosen is All or you can set that to anything of your choice.

Diya said...

Thank a lot, Zephyr, for a quick and simple solution. It worked.
Also, there is another way to achieve this. I used the below condition and passed the STATE_CD as use value to the prompt after enclosing it within single quotes.
This ensured that the value returned by prompt is already with quotes.