MDX query issue after switching filter

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.

pic 2.

pic 3.

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.

Hi @cw-ch,
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.


Thanks,
Jacky

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?

Hi @cw-ch,

Is this ?

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])”

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.

Hi @cw-ch,

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.

pic 2.

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.

Hi @cw-ch,

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

Thanks,
Jacky