Archive for March, 2012

Reporting from TM1 – Part5 : Promptmany Macro

March 29, 2012

But if you need a multi-select prompt; prompt macro command will not be enough for you. Then the command you will need is “promptmany”

 Here is an example how you can use it; suppose we again have two TM1 cubes with the Country dimension shared. If you want to create a multi-select prompt your macro will look like something like this:


set ( 


substitute (  ‘Profit and Loss’, ‘KPI’,   split(‘;’,   promptmany(‘Country’, ‘MUN’)  )

)   )# 



Main difference from the previous sample is this time promptmany command returns with more than one item. So you need split and join commands to modify the dataset.


Reporting from TM1 – Part4 : Prompt Macro

March 28, 2012

Let’s go on with the TM1 reporting;

You will definitely need some knowledge about Cognos macros while working with TM1 cubes. A full list of Cognos macro commands can be found in the official documentation, so I just want to focus on practical examples here.

When you need to use more than one TM1 cubes in your report and you have a prompt in your report then you already have a problem: TM1 cubes structurally use shared (conformed) dimensions but that is absolutely meaningless for Cognos BI. For Cognos BI they are just different dimensions.  

So if you have a month prompt in your report you have the problem of filtering two different TM1 cubes with one prompt. At this point you may get some help from Cognos macros; instead of a traditional prompt-filter definition try to  use a prompt macro in your report:

# substitute (  ‘Profit and Loss’ ,’KPI’, prompt (‘Month’, ‘MUN’))     #

In this case we based our macro on the time dimension of ‘Profit and Loss’ cube by the prompt macro. And use it in the related queries. On the other hand we can modify the returning result –MUN- in order to use it in the KPI cube related queries. Substitute command simply substitutes  ‘Profit and Loss’  with the word ‘KPI’.