TM1 Forum

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

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.

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