Page 1 of 1
Dynamic Subset using MDX
Posted: Mon Apr 21, 2014 6:04 am
by sathishh.mk
Hi,
I am trying to create dynamic subset using below MDX,but it is not working.
Need suggestions on this.
SubsetCreateByMDX('WaferSize_Item',ELPAR('Product_Dim','{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product_Dim] )}, 0)}, [Product_Dim].[SIZE] = "200")}'), 1));
Here "200" is Attribute value of products and wants replace with user input variable.
Regards,
Sathish
Re: Dynamic Subset using MDX
Posted: Mon Apr 21, 2014 10:44 am
by declanr
sathishh.mk wrote:Hi,
I am trying to create dynamic subset using below MDX,but it is not working.
Need suggestions on this.
SubsetCreateByMDX('WaferSize_Item',ELPAR('Product_Dim','{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product_Dim] )}, 0)}, [Product_Dim].[SIZE] = "200")}'), 1));
Here "200" is Attribute value of products and wants replace with user input variable.
Regards,
Sathish
The ELPAR section of your code isn't MDX and the subsetcreatebymdx function only accepts an mdx statement so that won't work. I can't remember off the top of my head how you get element parents in MDX but the following should work.
Code: Select all
SubsetCreateByMDX ( 'WaferSize_Item_temp', sMDX, sDimName );
SubsetCreate ( sDimName, 'WaferSize_Item' );
iCount = 1;
iMax = SubsetGetSize ( sDimName, 'WaferSize_Item_Temp' );
While ( iCount <= iMax );
sElement = SubsetGetElementName ( sDimName, 'WaferSize_Item_Temp', iCount );
sParent = ElPar ( sDimName, sElement, 1 );
If ( sParent @<> '' );
SubsetElementInsert ( sDimName, 'WaferSize_Item', sParent, SubsetGetSize ( sDimName, 'WaferSize_Item' ) + 1 );
EndIf;
iCount = iCount + 1;
End;
SubsetDestroy ( sDimName, 'WaferSize_Item' );
Note that the third MDX statement parameter is only applicable in versions 10.1 and above. And sMDX is your MDX statement, make the 200 part parameter dependent and get rid of the elpar.
Re: Dynamic Subset using MDX
Posted: Mon Apr 21, 2014 10:46 am
by mvaspal
Hi
I'm not sure ELPAR works in an MDX statement. Try "parent" instead.
Second comment: what if the FILTER returns more than one element as result? I think the 'Parent' function needs one element of which you want to query the parent.
UPDATE: just reading through the post of declanr - this is a fine solution as it also resolves the problem if in the filter you receive elements of which parents are not the same.
Re: Dynamic Subset using MDX
Posted: Mon Apr 21, 2014 8:53 pm
by declanr
mvaspal wrote:Hi
I'm not sure ELPAR works in an MDX statement. Try "parent" instead.
That's the one I was trying to think of (and it couldn't have been a more obviously named one
), the benefit of MDX of course as opposed to my while loop method is that it will constantly update when extra elements appear etc.