dynamic subset with new consolidation name

[telula]

Hello,
I have a list of contracts from 2006.I have an attribute called Active_Contract which is mark as Yes for contracts in 2009. I have created a dynamic subset using Active_Contract=Yes as a filter.I now want this dynamic subset to have a consolidation name as "All Active Contracts". How do I modify the code below to have a consolidation element of "All Active Contracts".

{TM1SORT( {FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Contract] )}, 0)}, [Contract].[Active_Contract] = "Yes")}, ASC)}

[lotsaram]

Hi Telula,

I think you might be confusing subsets and hierarchies. Although it is a "hidden feature" of TM1 that you can use a subset name in a DBRW formula which will act like a user defined consolidation this may or may not be what you are after. It's probably "dangerous" to use this feature anyway as it is undocumented so could be discontinued at any time and can have unpredictable results is subsets and dimension elements share the same name, and in some versions it can even cause stability problems. It is easy enough though to build a new hierarchy from your subset.

Assuming your subset is called "Active Contracts" this code will create a consolidation called "All Active Contracts." Just run it on the prolog (no data source necessary).

Code: Select all

sDim = 'Contract';
sPar = 'All Active Contracts';
DimensionElementInsert(sDim, '', sPar, 'C');
sSub = 'Active Contracts';
Count = 1;
MaxCount = SubsetGetSize(sDim, sSub);
WHILE(Count <= MaxCount);
  sEle = SubsetGetElementName(sDim, sSub, Count);
  DimensionElementComponentAdd(sDim, sPar, sEle, Count);
  Count = Count + 1;
END;

[lotsaram]

Telula,

Reading your post a 2nd time maybe your "All Active Contracts" consolidation already exists? In which case all you need to do to your MDX is:

{ TM1DRILLDOWNMEMBER( { [Contract].[All Active Contracts]}, ALL, RECURSIVE ) }