Subset creation by MDX

[JFerguson]

I have managed to create a dynamic subset by MDX based on one attribute. Is it possible to create a subset based on more than one attribute? Is it as simple as using "And"/"Or" ???

many thanks.

[JFerguson]

I have created a dynamic subset in subset editor by recording the expression and filtering on one attribute and then another, so copied it over to TI...

SubsetCreatebyMDX(pRegName,'{FILTER ({FILTER( {TM1SUBSETALL( [Cap_Ref] )}, [Cap_Ref].[Region] = "%Attribute_Name%")}, [Cap_Ref].[Fund_Code] = "%Fund%")}');

But get a process failure & the error log states: Error: Prolog procedure line (13): Could not create dynamic subset:

I'm obviously overlooking something - any ideas???

[Wim Gielis]

Hello there

Regarding And/Or, look at Intersect and Union. Here's an excellent tutorial: http://www.bihints.com/book/export/html/68

[Michel Zijlema]

I have created a dynamic subset in subset editor by recording the expression and filtering on one attribute and then another, so copied it over to TI...

SubsetCreatebyMDX(pRegName,'{FILTER ({FILTER( {TM1SUBSETALL( [Cap_Ref] )}, [Cap_Ref].[Region] = "%Attribute_Name%")}, [Cap_Ref].[Fund_Code] = "%Fund%")}');

But get a process failure & the error log states: Error: Prolog procedure line (13): Could not create dynamic subset:

I'm obviously overlooking something - any ideas???

Hi,

I think there are a few things wrong in your MDX string: if 'Attribute_Name' is a TI process parameter, you can't use the '%parname% notation on the advanced tabs - you should use parname (without the %, so: Attribute_Name) instead. Another thing is that the string includes a double quote, which I think should be a single quote. But if you simply replace the double quote with a single quote the syntax checker will think your string ends there, so you need to write something like
'{FILTER ({FILTER( {TM1SUBSETALL( [Cap_Ref] )}, [Cap_Ref].[Region] = ' | Char(n) | 'Attribute_Name' | Char(n) | ')}, [Cap_Ref].[Fund_Code] = ' | Char(n) | 'Fund' | Char(n) | ')}'
where n is the character code for a single quote.

Michel

[PXB]

Actually the place to get the current updated version of the MDX Primer is http://www.pxbconsulting.com/downloads.html.

[JFerguson]

Thank you for all of your help. I finally ended up with a working solution:

Attribute_Name = pRegion;
Fund_Name = pFund;
SubsetCreatebyMDX(pName,Expand('{FILTER ({FILTER( {TM1SUBSETALL( [Cap_Ref] )}, [Cap_Ref].[Region] = "%Attribute_Name%")}, [Cap_Ref].[Fund_Code] = "%Fund_Name%")}'));

[rollo19]

Newbie question - simple subset creation in TI.
I'm trying to create subsets of level 0 'Units' for each 'Unit Group' level 1. I need the subsets for cascading pick lists - pick a group, pick a unit.

My simplistic expression creates the first dynamic subset 'UNK' then aborts with an error: Unable to register subset
In the data-tab:
SubsetName = UNIT_GROUP_CD;
MDXQ = '{[Unit].['|SubsetName|'].Children}';
SubsetCreatebyMDX(SubsetName, MDXQ);
Data source is the subset of Unit Groups from the Unit dimension I am creating the Unit Group subsets in.. conflict?

This seems to do the same but I felt it was verbose; SubsetCreatebyMDX(SubsetName, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [unit].['|SubsetName|')}, 0)}, ASC)} ' );

[rollo19]

Solved it (talking to to self) - it didn't like to use the same name so I suffixed the subset name with ' subset'

SubsetName = UNIT_GROUP_CD;
MDXQ = '{[Unit].['|SubsetName|'].Children}';
SubsetCreatebyMDX(SubsetName|' subset', MDXQ);