MDX query issue after switching filter

Apliqo UX
1

Hi teams,

I encountered a problem (as pic 1.) when I drilldown member (as pic 3.) from default structure (as pic 2.) then switching the filter “Instance”.

My guess is that before querying the new view, UX uses the original mdx first.
Has anyone encountered the same situation? Thanks.

Row MDX: (use hierarchy from filter “Instance”)
{Union( {Union( {[Sys TM1 Object Application].[<<Sync.Sys TM1 Instance.Sys TM1 Instance>>].[(Consol) Applications]},{[Sys TM1 Object Application].[<<Sync.Sys TM1 Instance.Sys TM1 Instance>>].[}Applications]} )}, {Descendants([Sys TM1 Object Application].[$<<Sync.Sys TM1 Instance.Sys TM1 Instance>>].[(Consol) Applications],2)} )}

pic 1.

image
image2878×1274 252 KB

pic 2.

image
image1886×952 265 KB

pic 3.

image
image1818×734 291 KB

2

Hi @jlai

I can’t really figure out what you are doing here. From the MDX it looks like the Sys TM1 Instance dimension is supposed to represent a hierarchy name in the Sys TM1 Object Application dimension. Is that correct?

If that’s the case and you are dynamically switching the hierarchy then it could be that when drilling down UX is attempting to fetch children from either the default hierarchy or origionally selected hierarchy. This is kind of what the error message indicates.

In the browser console could you find the batch with the error and post the MDX? Then we will know for sure.

3

Hi @cw-ch-scott,
I can’t really figure out what you are doing here. From the MDX it looks like the Sys TM1 Instance dimension is supposed to represent a hierarchy name in the Sys TM1 Object Application dimension. Is that correct?

Yes, sorry for my unclear description

In the browser console could you find the batch with the error and post the MDX? Then we will know for sure.

Is this what you mentioned? Please let me know if I missed any information.

image
image2880×1532 691 KB

image
image2880×1530 649 KB

Thanks,
Jacky

4

Kind of but not quite. Can you find the batch with the 400 error (from the pic 1) and get the actual MDX that UX is generating?

5

Hi @cw-ch-scott,

Is this ?

image
image2106×998 441 KB

mdx: “SELECT NON EMPTY {TM1Sort( {[Sys TM1 Object].[APMS_Admin].[(Consol) Cubes].Children}, ASC)} ON ROWS,↵ {{([Sys Security Definition Stage].[Sys Security Definition Stage].[New Definition],[M Sys Security Manage Object].[M Sys Security Manage Object].[Permission Level - Rule]),↵([Sys Security Definition Stage].[Sys Security Definition Stage].[New Definition],[M Sys Security Manage Object].[M Sys Security Manage Object].[Permission Level - Override]),↵([Sys Security Definition Stage].[Sys Security Definition Stage].[New Definition],[M Sys Security Manage Object].[M Sys Security Manage Object].[Permission Level]),↵([Sys Security Definition Stage].[Sys Security Definition Stage].[New Definition],[M Sys Security Manage Object].[M Sys Security Manage Object].[Count]),↵([Sys Security Definition Stage].[Sys Security Definition Stage].[Current Definition],[M Sys Security Manage Object].[M Sys Security Manage Object].[Permission Level]),↵([Sys Security Definition Stage].[Sys Security Definition Stage].[Current Definition],[M Sys Security Manage Object].[M Sys Security Manage Object].[Count])}} ON COLUMNS↵ FROM [Sys Security Manage Object]↵ ↵ WHERE ([Sys TM1 Instance].[Sys TM1 Instance].[APMS_Admin],[Sys Security Group].[Sys Security Group].[Functional Security:AAP_AFP_ADMIN],[Sys Security TM1 Object Type].[Sys Security TM1 Object Type].[Cube])”

6

Is that the MDX with the error? If you paste this into Arc MDX tool does it work? (you need to get rid of the line return symbols, a bit annoying how Chrome console does that).

Becasue that looks like the MDX which works, before you drill down on a consol which causes the error.

8

Hi @cw-ch-scott,

Sorry the previous mdx is wrong. Below is the MDX with error. (as pic 1.)
Then I try to paste it into Arc MDX tool. (as pic 2.) it looks like same as the fail message on UX?

Thanks,
Jacky

MDX: (removed line return symbols)
SELECT NON EMPTY {{TM1DRILLDOWNMEMBER({Union( {Union( {[Sys TM1 Object Application].[APMS].[(Consol) Applications]},{[Sys TM1 Object Application].[APMS].[}Applications]} )}, {Descendants([Sys TM1 Object Application].[APMS].[(Consol) Applications],2)} )}, {[Sys TM1 Object Application].[APMS_ADMIN].[(Consol) Applications^}Applications^00.Global]})}} ON ROWS,{{([Sys Security Definition Stage].[Sys Security Definition Stage].[New Definition],[M Sys Security Manage Application].[M Sys Security Manage Application].[Permission Level - Rule]),([Sys Security Definition Stage].[Sys Security Definition Stage].[New Definition],[M Sys Security Manage Application].[M Sys Security Manage Application].[Permission Level - Override]),([Sys Security Definition Stage].[Sys Security Definition Stage].[New Definition],[M Sys Security Manage Application].[M Sys Security Manage Application].[Permission Level]),([Sys Security Definition Stage].[Sys Security Definition Stage].[New Definition],[M Sys Security Manage Application].[M Sys Security Manage Application].[Count]),([Sys Security Definition Stage].[Sys Security Definition Stage].[Current Definition],[M Sys Security Manage Application].[M Sys Security Manage Application].[Permission Level]),([Sys Security Definition Stage].[Sys Security Definition Stage].[Current Definition],[M Sys Security Manage Application].[M Sys Security Manage Application].[Count])}} ON COLUMNS FROM [Sys Security Manage Application] WHERE ([Sys TM1 Instance].[Sys TM1 Instance].[APMS],[Sys Security Group].[Sys Security Group].[Functional Security:AAP_AFP_ADMIN])

pic 1.

image
image2084×982 447 KB

pic 2.

image
image1209×459 40.1 KB

9

OK that makes sense now. You can definately see in the MDX it is trying to do a Union between sets from 2 different hierarchies which obviously can’t work.

We should be able to create a test case and look into it.

10

Hi @cw-ch-scott,

Cool! Please let me know if I can provide additional information.

Thanks,
Jacky