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.
Posts
13 comments:
Hi there, this is amazing support site of Cognos..please keeping posting. thanks
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 http://cafe.northwestern.edu/documents/jobaids/JobAid-SavePromptsDelivered.pdf
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.
Significant and precious information!
Sample cv
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
You need to create conditional variable and set the render variable property for each of the pages based on the condition.
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 !
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.
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.
Hi Diya,
Please modify it to STATE_CD=#prompt('State','String',sq('STATE_CD'))#
Let me know if this helps.
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.
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.
STATE_CD=#prompt('State','token','STATE_CD')#
This ensured that the value returned by prompt is already with quotes.
Superb blog entry, Instruction offers you the talking capacities to correct your fantasies and suppositions despite what might be expected people's simpleton can't play out the consistent action due to the insufficiency of assurance he won't prepared to go up against the social occasion of individuals. You can http://www.resumesamples2017.com/find-out-how-resume-should-look-like-2017/ and get numerous more data. A debt of gratitude is in order for sharing such information.
Post a Comment