MDX statement within TI code

[Ajay]

Hi Guys

I'm new to MDX and wondered how to incorporate the MDX code within some existing TI code that I have.

My current code adds elements of a "productline" dimension into a subset called:

#Create ProductLine subset
#-----------------------------------------
SubsetCreate('ProductLine', sSubsetName);

nNoOfElements = DIMSIZ('ProductLine');
nCounter = 1;
WHILE(nCounter <= nNoOfElements);
IF(ELLEV('ProductLine', DIMNM('ProductLine', nCounter)) = 0);
SubsetElementInsert('ProductLine', sSubsetName, DIMNM('ProductLine', nCounter), 1);
ENDIF;
nCounter = nCounter + 1;
END;

I have noticed that this can take sometime as it rattles through the dimension.

What i would like to know is how I incorporate the following MDX code into the TI:

{TM1FILTERBYLEVEL( {TM1SUBSETALL( [ProductLine] )}, 0)}

Any helps would be great

Thanks
Ajay

[lotsaram]

Simple, all you need to do is use SubsetCreateByMDX !

[Ajay]

Thanks Lotsaram.

My code is part of a chore which creates a view which is then zero'd out before loading data.

Using the my code I hash the first line which creates the subset and let the others run to always bring all elements in. The command in your post would generate the subset in the first place, but would it on execution not update the subset with new elements added to the dimension ?

Or do I need to adjust my view builds and subset builds to delete themselves completely before running this with the command you suggest ?

Ajay

[Martin Ryan]

MDX subsets will update when they are used, whether that be in a view or by being cycled through or whatever. The only exception is when they use attributes, when the behaviour can be a little unpredictable (see here and here).

Martin

[lotsaram]

My code is part of a chore which creates a view which is then zero'd out before loading data....

If that's the case then you probably don't need that code at all!

If you are creating a view for the purpose of zeroing out data then there is no need to create a subset of N level elements and assign the subset to the view. for a newly created view the implicit and default subset assignment is the system "ALL" subset for each dimension. As long as you are skipping calculated values, rules and blanks in the view then you only need to specify subsets and subset members for the area of the cube to be narrowed down for the zero out.

[gkoscs]

Does anyone know the syntax to take this one step farther? That is to be able to pass the dimension name as a parm to a process that would create a dynamic subset using the MDX code.

For example, I have the following code:

SubsetCreateByMDX('Level0','{TM1FilterByLevel({TM1SubsetAll([GR_ActuarialSegment])},0)}');

I would like to substitute a variable name for "GR_ActuarialSegment". Usually, you can use the "EXPAND" function to make such a substitution, but I can't seem to get the syntax correct. The ultimate goal is to write a process that cycles through a list of dimensions, creating dynamic, level 0 subsets for each.

[Wim Gielis]

Hi there,

Untested, but this should work:

you need to split the string into pieces, | is the concatenation operator.

Step 1 - Still hard-coded:

SubsetCreateByMDX('Level0','{TM1FilterByLevel({TM1SubsetAll([' | 'GR_ActuarialSegment' | '])},0)}');

Step 2 - a parameter:

SubsetCreateByMDX('Level0','{TM1FilterByLevel({TM1SubsetAll([' | pParam | '])},0)}');

pParam is the parameter here, give it a meaningful name and mark it as String.

Wigi