Zero Out Data from consolidation level down

Post Reply
rensley
Posts: 4
Joined: Fri Sep 17, 2010 1:13 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Zero Out Data from consolidation level down

Post by rensley »

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!
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Zero Out Data from consolidation level down

Post by tomok »

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
ajain86
Community Contributor
Posts: 132
Joined: Thu Oct 15, 2009 7:45 pm
OLAP Product: TM1
Version: 9.4.1 9.5 9.5.1
Excel Version: 2003 2007

Re: Zero Out Data from consolidation level down

Post by ajain86 »

You can also use this:

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