TM1 Forum

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.

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)}

jim wood wrote: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.

Thanks for the responses guys.