Perspective and MDX

Post Reply
mlorini
Posts: 1
Joined: Tue Apr 06, 2021 8:44 pm
OLAP Product: TM1
Version: 2.8
Excel Version: 365

Perspective and MDX

Post by mlorini » Wed Apr 07, 2021 12:54 pm

I was wondering if someone can help me solving the following issue:

1-I have a Active sheet in perspective; products are displayed on rows while Years are displayed on columns.
2018 2019 2020 2021 2022
Prod A $3 $2 $4 $2 $2
Prod B $2 $5 $2 $4
.......
Prod Z $13 $22 $34 $21 $2

2 - My goal is to write an MDX query that select product and year to get a final results like this when I select year 2020 and Prod B

2018 2019 2020 2021 2022

Prod B $2

3- The following MDX: {TM1DRILLDOWNMEMBER( {[Product].[" & C21 &"]}, ALL, RECURSIVE )} allows me to select a product however I have yet to figure out how to write an MDX that will select the Years as well.

Thanks

Wim Gielis
MVP
Posts: 2675
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Perspective and MDX

Post by Wim Gielis » Wed Apr 07, 2021 11:10 pm

I guess this is just a DBRW then, isn’t it ?
In a Perspectives active form columns are not dynamic.

Anyway, consider:
"{[Years].[" & C20 &"]}"

assuming C20 holds the year selection.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

Post Reply