Last period prompt option in Cognos report Studio

Posted on Tuesday 10 November 2009, 20:42 - Décisionnel - Permalink

Article
|
Comments(0)
|
Attachments(0)

When you build a new report with some prompts, including a time dimension; you may want to add an option to automatically select the last Period. this way users can :

see their last data without chosing it in a time tree (but with ability to choose another one).
schedule a report witch will always select the last period available.

This can be done by using a set containing the #promptmany()# Cognos macro

1- create the set definition

First, we need a report with at least one query and a prompt on the time dimension. Following examples works on the Month level.

If your query already exists, just remove any filter on your Time dimension as we will replace it with a data item set.

In report's Query, we will add a new data item containing our set definition, named Chosen_Month.

The #promptmany()# Cognos macro allows create a set from parameters with ability to specify a default value :

#promptmany('[parameter name]','[Output Type]','[Default set definition]')#

We want to set the Date prompt MONTH parameter with last period as default value:

set( #promptmany('MONTH', 'MUN', 'closingPeriod([Invoice Sales].[Time Dimension].[YMD Hierarchy].[Month])')# ).

The Output Type MUN (Member Unique Name) format will give the following Result :

[Invoice Sales].[Time Dimension].[YMD Hierarchy].[Month]->[all].[2009].[Oct-2009]

2- Set prompts to reflect our new behaviour

Now the Time prompts is not required anymore so update the required flag to Optional

(optional) Another solution would be to create a prompt option Box named "Last Period" using a collection ('Y','N') ie with parameter named last_period.

Then create a render variable on the period prompt.

You cans also set a default value to make the parameters selection faster

set all other required prompts to cascading prompt on last_period.

Then you will see the Period list only if required.

I hope this will help you :).

Author: Jean-Baptiste Heren

Stay in touch with the latest news and subscribe to the RSS Feed about this category

Blog Pro de Jean-Baptiste HEREN