MDX Issues with 2 row dimensions in Active form

Post Reply
sg9489
Posts: 2
Joined: Fri Jan 10, 2020 4:43 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: office 365

MDX Issues with 2 row dimensions in Active form

Post by sg9489 » Sun Jan 12, 2020 10:16 pm

Hi Folks,

Query: Unable to load MDX dimension if dependent on another MDX dimension in the same sheet.

Details:
Attached is the active form of Cube 2 with 2 dimensions in the title, 2 in rows and 1 in column.
Dimensions in rows are:
KPI
CAtegory

Row dimension KPI is calculated as MDX depending on the values of title dimensions(Centre & Period).
A Lookup cube has the information of the dependency of the above measures.
MDX: "{FILTER({TM1FILTERBYLEVEL({HIERARCHIZE({TM1SUBSETALL(KPI)})},0)},
[CUBE2.LOOKUP].([CENTRE].["&B10&"], [PERIOD].["&B11&"])='Y')}"

Above MDX is working fine and updates as the parameters are changed/

Row dimension Category will also be calculated as MDX depending on the values of title dimensions(Centre & Period) and KPI.
A Lookup cube has the information of the dependency of the above measures.
MDX:"{FILTER({TM1FILTERBYLEVEL({HIERARCHIZE({TM1SUBSETALL(KPI)})},0)},
[CAT.LOOKUP].([CENTRE].["&B10&"], [PERIOD].["&B11&"], [KPI].["&A15&"])='Y')}"

This MDX does not provide any elements, if the KPI is dynamically addressed, but works if it is hard coded.
I also tried using named range for the KPI lookup, but that does not work either.

Please advise if I am doing anything wrong and suggest solution.
Attachments
Capture.JPG
Capture.JPG (31.04 KiB) Viewed 1002 times

Mark RMBC
Community Contributor
Posts: 239
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: MDX Issues with 2 row dimensions in Active form

Post by Mark RMBC » Mon Jan 13, 2020 12:48 pm

Hi,

It would be nice to know which dimensions are in your 2 .lookup cubes.

Just looking at your category mdx statement (the second one), there is no reference to the category dimension in the mdx?
It would appear to be pointing at the KPI dimension?

Incidentally: I do not think what you are trying to do is possible with mdx anyway, as I presume your category mdx is within the TM1RPTROW and you expect the mdx will pick up the value of a15, a16, a17 etc and return multiple mdx expressions from the single TM1RPTROW.

Regards,
mark

sg9489
Posts: 2
Joined: Fri Jan 10, 2020 4:43 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: office 365

Re: MDX Issues with 2 row dimensions in Active form

Post by sg9489 » Mon Jan 13, 2020 10:55 pm

Hi Mark,
Thanks for the reply.

There are 2 lookup cubes:
1. CUBE2.LOOKUP
Dims: KPI, CENTRE, PERIOD
2. CAT.LOOKUP
Dims: CATEGORY, KPI, CENTRE, PERIOD

My bad, I have mentioned it wrong, but it is as below in the actual TM1 environment:
MDX:"{FILTER({TM1FILTERBYLEVEL({HIERARCHIZE({TM1SUBSETALL(CATEGORY)})},0)},
[CAT.LOOKUP].([CENTRE].["&B10&"], [PERIOD].["&B11&"], [KPI].["&A15&"])='Y')}"

Is there any workaround for this one?

Mark RMBC
Community Contributor
Posts: 239
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: MDX Issues with 2 row dimensions in Active form

Post by Mark RMBC » Tue Jan 14, 2020 11:35 am

Hi,

I can't think of an mdx workaround.

If these forms are NOT being consumed in TM1 web you could insert a column to DBRW to the category lookup cube and apply custom validation (Unfortunately custom validation does not appear to work in TM1web) on whether the DBRW returns Y, so only allow none zero where DBRW is Y. This would also depend on how many categories you had, because you would be basically returning every category for each KPI.

If they are being consumed in TM1web then apply some sort of conditional formatting.

The only other thing that springs to mind is concatenating KPI and Category in a dimension and using that.

Maybe others have a better suggestion?

regards,
Mark

Post Reply