Blog Pro de Jean-Baptiste HEREN

Notes d'un consultant Freelance en Informatique

To content | To menu | To search

Last period prompt option in Cognos report Studio

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 :).

Jean-Baptiste Heren

Author: Jean-Baptiste Heren

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

Comments are closed

no attachment

You Might Also Like


Cognos Report : Display a different measure in row for the aggregated column

Following tip is when you use a Relational Datamodel (DMR). In a Cognos Report crosstab, If You need to Display different measure in the aggregated column, you can do it using a simple if-then-else...

Continue reading

Cognos 8 - convert a member unique name from one hierarchy to another

If you need to build any report or Event studio Query using the same data from different packages; and you want to prompt just once. You can convert the member unique name from one hierarchy to...

Continue reading