New to MDX Questions

[George Regateiro]

I am getting into the use of MDX through TI for the first time and have encountered some behavior that seems strange to and could use some assistance.

First a little background ont the model

I have 2 dimensions that are very similar the FcstJob and FcstJobD. The only difference is that every n level node in the FcstJob is a parent in the FcstJobD dimension.

I have this line of code in my TI

Code: Select all

SubsetCreatebyMDX('ztempcopyprocessD', '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [FcstJobD] )}, '| JobD|')}, ALL, RECURSIVE )}, 0)}');

Where the JobD variable is being passed into the TI. When I run the process I get the following error

Error: Prolog procedure line (43): Could not create dynamic subset: Level or member name "MA_A0100" ambiguous: found in dimensions "FcstJobD", "FcstJob"

I understand that this is because the same node exists in both, but the subset I am trying to create is only on the FcstJobD and that is all the function references. If I paste the code into the subset editor is works.

What am I doing wrong? I have tried reading the docs, but that has not gotten me far.

[ScottW]

Hi George,

It looks like your TI expression started out its life being recorded in the subset editor (not that there's anything wrong with that, I'm no MDX expert and I do that myself all the time.)

You are using TM1FILTERBYPATTERN to pick the element by a string match after a SUBSETALL. You don't need to do this as you can use the MDX to select the element directly.
ie you can replace {TM1FILTERBYPATTERN( {TM1SUBSETALL( [FcstJobD] )}, '| JobD|')}
with {[FcstJobD].['| JobD |']}

This shouldn't be a problem as you know the identity of "JobD". This has the advantage of simpler and less code and the dimension is specified so the ambiguous name shouldn't cause an error (I haven't tested as I don't have a suitable db open but this should work for you.)

[George Regateiro]

Thanks Scott that worked.