TM1 Forum

Posted: **Thu Oct 14, 2010 2:37 pm**

Hello All,

I am currently writing a process that is supposed to take the names of dimensions and elements within that dimension as parameters and zero out the data at their intersections. I can Zero out entire Dimensions no problem or even the entire cube. My question is I'm having trouble zeroing out Consolidations within the cube. What it should do is Zero out from the consolidation given down to the leaf level. I am having issues doing so and I dont know if i'm going about this wrong or not

For one of the parameters the approach im taking is to create a subset by MDX like below

Code: Select all

#Create the subsets
IF(~(dimension1Name @= ''));
	curLevel = ELLEV(dimension1Name,dimension1Value);
	If(dimension1Value @= '' % DTYPE (dimension1Name,dimension1Value)  @= 'N');
		MDX1 = '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | dimension1Name | '] )}, 0)}';
		SubsetCreatebyMDX('Subset1',MDX1);

	ELSEIF((~(dimension1Value @='')) % DTYPE(dimension1Name,dimension1Value) @= 'C');
		#get the elements 
		level = ELLEV(dimension1Name,dimension1Value);
		#ASCIIOutput('C:\LevelNumber.txt',NumberToString(level));
		MDX1 = '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | dimension1Name | '] )},level)}';	
		SubsetCreatebyMDX('Subset1',MDX1);		
	ENDIF;
	ViewSubsetAssign(cubeName,'zeroStream',dimension1Name,'Subset1');
ENDIF;

any help insight would be amazing, thank you!

Posted: **Thu Oct 14, 2010 7:12 pm**

I'm going to assume that your aim here is to create a view from your subsets so that you can issue a VIEWZEROOUT command in your TI process. That being said, were you aware that your view doesn't have to be just leaf-level (Level 0) elements? If you issue a VIEWZEROOUT on a view that has consolidated elements in it then they just get ignored and only the leaf elements specifically spelled out in your view get cleared. No need to do a TM1FILTERBYLEVEL to get leafs. Change your SubsetCreateByMDX code to use the DESCENDANTS MDX function which will create a subset of the element parameter you pass plus all it's descendants. If any of those happen to be consolidations they'll just be ignored in the VIEWZEROOUT routine.

Posted: **Fri Oct 15, 2010 2:44 pm**

You can also use this:

SubsetCreateByMDX(subset, '{TM1DrillDownMember({'[Dimension].[Element]'},ALL,Recursive)}');

TM1 Forum

Zero Out Data from consolidation level down

Zero Out Data from consolidation level down

Re: Zero Out Data from consolidation level down

Re: Zero Out Data from consolidation level down