Dynamic Subset using MDX

[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

[declanr]

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.

[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.

[declanr]

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.