Mdx in tm1-ui-element-list vs expression window gives a different result


#1

Hi all,

I am trying to get a list of elements (using a mdx statement) that have the “incident count” measure in a cube above 0. When checking the mdx in the expression window, I get the correct result whereas in Canvas, the result is completely different.

Expression window

Canvas

here is the mdx statement:
{FILTER({[SHEQ TOOCS Nature].[TOOCS Nature].children},[SHEQ TOOCS].([SHEQ Version].[Actual],[T Month].[Sep],[T Month Type].[Fiscal Month],[T Year].[2017],[SHEQ Time Analysis].[MAT],[SHEQ TOOCS Measure].[Incident Count]) >0)}

Do you know if I am doing something wrong?

Thanks in advance for your help.

Kind regards,
Veolia BI Team


#2

Hi @anz.fin.all.mailbox,

Any errors on the browser console?

Also, are you using the same TM1 account on Canvas and via Architect/Server Explorer?


Paul


#3

Hi Paul,

No errors detected on the browser console and I am using the same TM1 account in Canvas and Architect.

Kind regards,
Veolia BI Team


#4

Hi @anz.fin.all.mailbox,

Thanks for the updates.

How about if you save the above MDX into a Subset, and access the the subset instead via the tm1-subset attribute of the tm1-ui-element-list, does it give the elements as per Architect?


Paul


#5

Hi Paul,

Saving the MDX into a subset worked. I got the correct result as per Architect.

For now I will use this work around if it is the only possibility to get the correct result. However, I would prefer to use the MDX in Canvas directly. Please let me know if you find a solution.

Thank you for your help.

Kind regards,
Veolia BI Team


#6

Hi @anz.fin.all.mailbox,

Can you please contact your Cubewise Local office, send them the relevant TM1 objects if possible, so they can help create and submit a ticket to support for this.

Thanks!


Paul


#7

Hi @anz.fin.all.mailbox,

What is your TM1 version?

I was able to replicate your issue with TM1 10.2.2 FP5. There is a bug in the TM1 REST API 10.2.2 FP5 which has been fixed in 10.2.2 FP6.

My MDX is:

{ORDER( {FILTER( {FILTER( {TM1SUBSETALL( [Department] )}, [Department].[Color] <> "")}, [General Ledger].([Version].[Budget],[Year].[2016],[Currency].[Local], [Region].[1],[Account].[Operating Profit],[General Ledger Measure].[Amount])<>0)}, [General Ledger].([Version].[Budget],[Year].[2016],[Currency].[Local], [Region].[1],[Account].[Operating Profit],[General Ledger Measure].[Amount]),BDESC)}

When I run it in a Subset Editor I get the correct list:
image

When I run the MDX in FP5, the Accessories is missing:

If I ran it in FP6, I get the correct result: