Cognos Analytics

I am working on a report that asks the user for the month at a prompt. I can't find a way for the prompt to show the member of the current month and for each month to change dynamically.

Is there some workaround to achieve it.

Thanks

BR

Hi Javier,

which Cognos version do you use? If it's V12.x then there's a new section in reporting for parameters.

See this perfectly explained article from Danny Gargus:

https://pmsquare.com/analytics-blog/2023/6/13/cognos-12-parameters-page

If you don't use Cognos 12 already there are several way to achieve a dynamic default prompt value. I use the approach to overwrite the key for the default value with something that does not exists in the database (like "-1") and set this as default value for the prompt. 

If your model is dimensional then it's pretty easy to overwrite stuff depending on conditions. If it's relational you would have to create joins or functions to evaluate the default value. Here's a sample on IBM demo data where the default selection should be the retailer with the highest Gross Profit:

One query returns all retailers and one only the best one. Then join them with an outer join:

The column [Retailer code] then gets overwritten in the joined query with this expression:

IF ([Prompt Retailer].[Retailer Code] = [best retailer code])
THEN (-1)
ELSE ([Prompt Retailer].[Retailer Code])

That's the output:

Do the same with the main data query and set "-1" as default value for the prompt. Done.

Could you follow my short guide?

I am working on a report that asks the user for the month at a prompt. I can't find a way for the prompt to show the member of the current month and for each month to change dynamically.

Is there some workaround to achieve it.

Thanks

BR

Hi Robert,

thanks a lot for your answer. Unfortunately I Am using v11.2.4 with a dimensional model (transformer), and I can apply your explication. Anyway, thanks.

------------------------------
Javier Sendra

Original Message:
Sent: Fri June 14, 2024 01:47 AM
From: Robert Dostal
Subject: Set current month member as default in a prompt

Hi Javier,

which Cognos version do you use? If it's V12.x then there's a new section in reporting for parameters.

See this perfectly explained article from Danny Gargus:

https://pmsquare.com/analytics-blog/2023/6/13/cognos-12-parameters-page

If you don't use Cognos 12 already there are several way to achieve a dynamic default prompt value. I use the approach to overwrite the key for the default value with something that does not exists in the database (like "-1") and set this as default value for the prompt. 

If your model is dimensional then it's pretty easy to overwrite stuff depending on conditions. If it's relational you would have to create joins or functions to evaluate the default value. Here's a sample on IBM demo data where the default selection should be the retailer with the highest Gross Profit:

One query returns all retailers and one only the best one. Then join them with an outer join:

The column [Retailer code] then gets overwritten in the joined query with this expression:

IF ([Prompt Retailer].[Retailer Code] = [best retailer code])
THEN (-1)
ELSE ([Prompt Retailer].[Retailer Code])

That's the output:

Do the same with the main data query and set "-1" as default value for the prompt. Done.

Could you follow my short guide?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ Gebr. Müller Apparatebau GmbH & Co. KG
Ingelfingen

Original Message:
Sent: Thu June 13, 2024 12:18 PM
From: Javier Sendra
Subject: Set current month member as default in a prompt

I am working on a report that asks the user for the month at a prompt. I can't find a way for the prompt to show the member of the current month and for each month to change dynamically.

Is there some workaround to achieve it.

Thanks

BR

------------------------------
Javier Sendra
------------------------------

Hi Robert,

thanks a lot for your answer. Unfortunately I Am using v11.2.4 with a dimensional model (transformer), and I can apply your explication. Anyway, thanks.

Hi Javier,

which Cognos version do you use? If it's V12.x then there's a new section in reporting for parameters.

See this perfectly explained article from Danny Gargus:

https://pmsquare.com/analytics-blog/2023/6/13/cognos-12-parameters-page

If you don't use Cognos 12 already there are several way to achieve a dynamic default prompt value. I use the approach to overwrite the key for the default value with something that does not exists in the database (like "-1") and set this as default value for the prompt. 

If your model is dimensional then it's pretty easy to overwrite stuff depending on conditions. If it's relational you would have to create joins or functions to evaluate the default value. Here's a sample on IBM demo data where the default selection should be the retailer with the highest Gross Profit:

One query returns all retailers and one only the best one. Then join them with an outer join:

The column [Retailer code] then gets overwritten in the joined query with this expression:

IF ([Prompt Retailer].[Retailer Code] = [best retailer code])
THEN (-1)
ELSE ([Prompt Retailer].[Retailer Code])

That's the output:

Do the same with the main data query and set "-1" as default value for the prompt. Done.

Could you follow my short guide?

I am working on a report that asks the user for the month at a prompt. I can't find a way for the prompt to show the member of the current month and for each month to change dynamically.

Is there some workaround to achieve it.

Thanks

BR

Hi Javier,

If your data source is Transformer cube you could try a function called LinkMember (located under Dimensional Functions folder).  It is a very effective function and with prompt macro it is very easy  to get default values for time prompts.

Below Tips from report studio:

linkMember ( member , level|hierarchy )

Returns the corresponding member in "level" or "hierarchy" (of the same dimension). For level-based hierarchies, a level must be specified as the second argument, and for parent-child hierarchies, a hierarchy must be specified. An exception is thrown when the second parameter does not resolve to a hierarchy of the member's dimension. Note that calculated members are not supported as the first argument.

I made a simple example report from "Great Outdoos Sales Cube" (view attached report spec).
The idea is that you use cube relational time hierarchies to get e.g Current Month, Current Date etc. The function linkMember allows you to link relational time hierarchy member to your primary time hierarchy. After that you can use prompt macro and add default value for Optional prompt.
Because it's not possible to show default value in Prompt selection I added a singleton above prompt so that users can see what the current month is when they run the report.

BR

Jaakko

Hi Robert,

thanks a lot for your answer. Unfortunately I Am using v11.2.4 with a dimensional model (transformer), and I can apply your explication. Anyway, thanks.

Hi Javier,

which Cognos version do you use? If it's V12.x then there's a new section in reporting for parameters.

See this perfectly explained article from Danny Gargus:

https://pmsquare.com/analytics-blog/2023/6/13/cognos-12-parameters-page

If you don't use Cognos 12 already there are several way to achieve a dynamic default prompt value. I use the approach to overwrite the key for the default value with something that does not exists in the database (like "-1") and set this as default value for the prompt. 

If your model is dimensional then it's pretty easy to overwrite stuff depending on conditions. If it's relational you would have to create joins or functions to evaluate the default value. Here's a sample on IBM demo data where the default selection should be the retailer with the highest Gross Profit:

One query returns all retailers and one only the best one. Then join them with an outer join:

The column [Retailer code] then gets overwritten in the joined query with this expression:

IF ([Prompt Retailer].[Retailer Code] = [best retailer code])
THEN (-1)
ELSE ([Prompt Retailer].[Retailer Code])

That's the output:

Do the same with the main data query and set "-1" as default value for the prompt. Done.

Could you follow my short guide?

I am working on a report that asks the user for the month at a prompt. I can't find a way for the prompt to show the member of the current month and for each month to change dynamically.

Is there some workaround to achieve it.

Thanks

BR