MDX (Hear the Shudders)

[jim wood]

Guys,

If I have one area of weakness it is MDX. I've never really spent much time with it. Normally I use record in the subset editor but this doesn't work for what I'm trying to do.

What I am trying to do is create an mdx subset within TI. I need to pass the MDX a consolidation name and use the MDX to create a subset containing all the level 0 elements for that consolidation. Could anybody help me out with this?

Many Thanks,

Jim.

[tomok]

If you are creating the subset in TI then you need to use the SubsetCreateByMDX function. You need to pass a string to that function that represents the MDX query you want to execute. What you want is the Descendants keyword. Like this:

{TM1FILTERBYLEVEL({DESCENDANTS(Dimension.[YourConsolidatedNode]) }, 0)}

[lotsaram]

Guys,

If I have one area of weakness it is MDX. I've never really spent much time with it. Normally I use record in the subset editor but this doesn't work for what I'm trying to do.

What I am trying to do is create an mdx subset within TI. I need to pass the MDX a consolidation name and use the MDX to create a subset containing all the level 0 elements for that consolidation. Could anybody help me out with this?

Many Thanks,

Jim.

Jim the subset editor MDX recorder will work perfectly well for this.
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1SubsetBasis()}, ALL, RECURSIVE )}, 0)}
Just replace TM1SubsetBasis() with [dimension].[consolidation]
E.g.
sSub = 'mySub';
sDim = 'myDim';
sHier = 'myConsol';
sMDX = '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[' | sDim | '].[' | sHier | ']}, ALL, RECURSIVE )}, 0)}';
SubsetCreateByMDX(sSub, sMDX);

MDX is pretty powerful, especially with active forms and despite the quirkiness of TM1's implementation of it. If you haven't gotten at least a little up to speed then its high time you started.

[jim wood]

Thanks for the responses guys.