Edit MDX in PAX Exploration

Post Reply
Ranger
Posts: 2
Joined: Fri Feb 07, 2020 4:08 am
OLAP Product: PAX
Version: 11.0.37
Excel Version: 2016

Edit MDX in PAX Exploration

Post by Ranger » Mon Feb 10, 2020 12:04 am

Hi

I am trying to subtotal some individual accounts in the column header of my PAX exploration and would like to edit the column MDX.

The following works:
-Use Set Editor to select the individual accounts
-Once refreshed, on the report header I can select those accounts, right click & choose 'Insert Calculation' and choose the option to sum the accounts
-Result is my accounts individually listed across the columns, plus an automatic subtotal in the last column.
-I can also choose to 'Keep' just the calculated subtotal and the individual accounts disappear
-Whilst I cannot save the exploration as a view, the workbook & subtotal will refresh as expected when changes to the context filters are made.


Questions I have are:
1) Once I have used the 'insert calculation' method, the save button on the PAX toolbar for the exploration disappears. Is this a bug?
2) After I have the inserted the subtotal calculation and removed the individual accounts, if I view the column MDX it simply says something like [Account].[Account].[50000] + [Account].[Account].[60000] , however if I were to type/copy and paste that exact same thing into the column MDX directly instead of it being created by the 'insert calculation' method, i receive an 'Invalid MDX expression' error. Is this also a bug?? Am I not able to do basic additions via MDX in an exploration?

Thanks
RD

ascheevel
Posts: 109
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.8
Excel Version: 2016

Re: Edit MDX in PAX Exploration

Post by ascheevel » Mon Feb 10, 2020 3:50 pm

Ranger wrote:
Mon Feb 10, 2020 12:04 am
1) Once I have used the 'insert calculation' method, the save button on the PAX toolbar for the exploration disappears. Is this a bug?
The save view button in an exploration is used to save a view to the TM1 server. This is expected behavior once you add the custom calc since the native cube views on TM1 server cannot handle those. What version of PAX are you on? In the latest releases, you still have the option to save to the PAW content store but saving to the server will be grayed out.


Ranger wrote:
Mon Feb 10, 2020 12:04 am
2) After I have the inserted the subtotal calculation and removed the individual accounts, if I view the column MDX it simply says something like [Account].[Account].[50000] + [Account].[Account].[60000] , however if I were to type/copy and paste that exact same thing into the column MDX directly instead of it being created by the 'insert calculation' method, i receive an 'Invalid MDX expression' error. Is this also a bug?? Am I not able to do basic additions via MDX in an exploration?
Inserting a calculation will modify the underlying MDX of the exploration, not just the MDX statement for the set editor. Check the MDX of your exploration, you should see a WITH statement defining the calculated member that your set editor MDX is then able to reference. I could be wrong, but I suspect you cannot manually define a calculated member in the set editor MDX without first modifying the MDX query of the exploration.

Ranger
Posts: 2
Joined: Fri Feb 07, 2020 4:08 am
OLAP Product: PAX
Version: 11.0.37
Excel Version: 2016

Re: Edit MDX in PAX Exploration

Post by Ranger » Mon Feb 10, 2020 11:21 pm

Hi ascheevel

Thanks for the reply! I am on version 11.0.37.1-0.
you cannot manually define a calculated member in the set editor MDX without first modifying the MDX query of the exploration
This makes sense. I had tinkered with the MDX in the underlying expression too but it seemed to create a strange disconnect where the exploration MDX was referring to the calculated member, but the column MDX kept reverting back to just one account member.

To get it to work I had to delete the account dimension from the column, edit the underlying exploration MDX to create my calculated member & add the account dimension back to the column.

Thankyou!
RD

Post Reply