How to design Cognos interactive reports 15 - Make columns flexible with TM1 cube

1. Check list of all posts

   Context

Only relational samples are described in all ex posts. There is one very topic sample with TM1 cube. As known, TM1 is mostly used for planning, budgeting and forecasting. One of major report is variance report between different measures, such as current forecast and budget. There are different requests to any measure to any measure. However, the combination is huge. The best solution is to allow business users to choose any measure to any measure based on their needs. This document presents a practical sample.

2. Sample

Please note the current forecast, prior forecast and Current forecast – Prior forecast are dynamic specified. You can get all combinations for any measure to any measure.

Measure list are in this sample

• Current Forecast
• Prior Forecast
• Budget
• CY Actual
• PY Actual
• 2Yr PY Actual

3. Prompt

Both measures are listed as below

[Sales Budget].[Version].[Version]->:[TM].[Version].[Version].[@MEMBER].[Current Forecast]  => Current Forecast

[Sales Budget].[Version].[Version]->:[TM].[Version].[Version].[@MEMBER].[Prior Forecast] => Prior Forecast

[Sales Budget].[Version].[Version]->:[TM].[Version].[Version].[@MEMBER].[Budget]  => Budget

[Sales Budget].[Version].[Version]->:[TM].[Version].[Version].[@MEMBER].[CY Actual]  => CY Actual

[Sales Budget].[Version].[Version]->:[TM].[Version].[Version].[@MEMBER].[Actual]  => PY Actual

[Sales Budget].[Version].[Version]->:[TM].[Version].[Version].[@MEMBER].[2Yr PY Actual]   => 2Yr PY Actual

4. Query

Measure A:

#prompt ('MeasureA', 'memberuniquename', '[Sales Budget].[Version].[Version]->:[TM].[Version].[Version].[@MEMBER].[CY Actual]')#

Measure B:

#prompt ('MeasureB', 'memberuniquename', '[Sales Budget].[Version].[Version]->:[TM].[Version].[Version].[@MEMBER].[Actual]')#

Measure A - Measure B: Measure A - Measure B

5. Layout

In order to show the real name for Measure A – Measure B, the text will be override  as  ParamDisplayValue('MeasureB') -  ParamDisplayValue('MeasureB')