Year (relative) prompt to filter TM1 Cube data.

[klingiertr]

Hello,

I need some help with creating a prompt/build elements which shows the following and the previous year for the selected in prompt year.



What i need to achieve:
a) A report in IBM CA 11.1 with IBM PA 2.0.6 cubes as a source data
b) I need to create three (3) columns:
  b.1 - tuple ([Version1]; [Selected Year -1])
  b.2 - tuple ([Version2]; [Selected Year])
  b.3 - tuple ([Version1]; [Selected Year +1])

There is one Cube [C_Sales] with many dimensions including:
1. V_Version - contains elements [Version1], [Version2]...
2. T_Time - contains a time dimension with levels : Total,Years,Quarters, Months. Each element have addtional text attributes:
  Element    Caption   Next_Year   Prev_Year
  [2021]      '2021'     '2022'        '2020'
  [2022]      '2022'     '2023'        '2021'
  [2023]      '2023'     '2024'        '2022'

I have created two elements in Report Studio query

SelectedVersion = [C_Sales].[V_Version].[V_Version]->?pVersion?
SelectedYear = [C_Sales].[T_Time].[T_Time].[Years]->?pYear?

No I have tried to create previous year element as:
[Selected Year -1]=PrevMember(SelectedYear);

But it does not work in the tuple mentioned at the beggining.

Can you suggest any other way to achieve the expected result?

[MFGF]

Hello,

I need some help with creating a prompt/build elements which shows the following and the previous year for the selected in prompt year.



What i need to achieve:
a) A report in IBM CA 11.1 with IBM PA 2.0.6 cubes as a source data
b) I need to create three (3) columns:
  b.1 - tuple ([Version1]; [Selected Year -1])
  b.2 - tuple ([Version2]; [Selected Year])
  b.3 - tuple ([Version1]; [Selected Year +1])

There is one Cube [C_Sales] with many dimensions including:
1. V_Version - contains elements [Version1], [Version2]...
2. T_Time - contains a time dimension with levels : Total,Years,Quarters, Months. Each element have addtional text attributes:
  Element    Caption   Next_Year   Prev_Year
  [2021]      '2021'     '2022'        '2020'
  [2022]      '2022'     '2023'        '2021'
  [2023]      '2023'     '2024'        '2022'

I have created two elements in Report Studio query

SelectedVersion = [C_Sales].[V_Version].[V_Version]->?pVersion?
SelectedYear = [C_Sales].[T_Time].[T_Time].[Years]->?pYear?

No I have tried to create previous year element as:
[Selected Year -1]=PrevMember(SelectedYear);

But it does not work in the tuple mentioned at the beggining.

Can you suggest any other way to achieve the expected result?

Hi,

Can you be specific about what you mean when you say "it does not work in the tuple"? Do you get an error? A wrong result? No result? Something else?

What do you see if you include the calculated members on their own in the edge of a crosstab? Are they deriving the correct year members?

Some of the syntax you posted looks a bit odd, too - were you accurate in what you posted? For example

PrevMember(SelectedYear)

I would expect this expression to be prevMember([Selected Year])

I'm not seeing anything generically wrong in the approach you are using here. Can you be more specific in the areas highlighted above?

Cheers!

MF.