TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

SubsetElementInsert - all items under roll up

https://www.tm1forum.com/viewtopic.php?t=13237

Page 1 of 1

SubsetElementInsert - all items under roll up

Posted: Thu Apr 20, 2017 4:00 pm
by kenship
I'm using a SubsetElementInsert in a TI process that creates source view for data transfer.

For example: SubsetElementInsert(vDimName,sSubset,'(P&L)',1); where P&L represents a roll up of profit and loss GL accounts.

However what I actually want is to list all the level 0 items under "(P&L)" instead of the rollup.

How should I code this?

Thanks in advance.

Kenneth

Re: SubsetElementInsert - all items under roll up

Posted: Thu Apr 20, 2017 4:28 pm
by gtonkin
You would probably want to do this via MDX e.g.

Code: Select all

sMDX='{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( [<dim>].[(P&L)]}}, ALL, RECURSIVE )}, 0)}';
SubsetCreateByMDX(sSubset,sMdx);

Re: SubsetElementInsert - all items under roll up

Posted: Thu Apr 20, 2017 5:46 pm
by kenship
Unfortunately I got an error saying Syntax error at or near: "}, ALL, RECURSIVE )}, 0)}", character position 65 expression.

Below is how the view and subset are setup.

Moreover, I realize that in addition to N02PNL I also need to include N02BUD. Should I create sMDX2?

Many thanks.

Code: Select all

IF (vDimName @= 'FINDW_Business_Unit');
		SubsetElementInsert(vDimName,sSubset,'(ROY1+ROY2)',1);

	ELSEIF( vDimName @= 'FINDW_Account');
                                sMDX='{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( [FINDW_Account].[N02PNL])}, ALL, RECURSIVE )}, 0)}';
                                SubsetCreateByMDX(sSubset,sMdx);            

	# Creates all N level elements within each dimension (default case)
	ELSE;
		vIndex = 1;
		vIndexSub = 1;

		WHILE(vIndex <= DIMSIZ(vDimName));
			vElement = DIMNM(vDimName, vIndex);
			IF(DTYPE(vDimName, vElement)@='N');
				SubsetElementInsert(vDimName, sSubset,vElement, vIndexSub);
				vIndexSub = vIndexSub +1;
			ENDIF;
			vIndex = vIndex + 1;
		END;
	ENDIF;

Re: SubsetElementInsert - all items under roll up

Posted: Thu Apr 20, 2017 5:53 pm
by gtonkin
You can specify multiple items as a set and then drill them down.
Not always easy to get the syntax correct when you are typing straight into the forum but try this:

Code: Select all

sMDX='{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[FINDW_Account].[N02PNL],[FINDW_Account].[N02BUD]}, ALL, RECURSIVE )}, 0)}';
SubsetCreateByMDX(sSubset,sMdx);

Re: SubsetElementInsert - all items under roll up

Posted: Thu Apr 20, 2017 6:36 pm
by kenship
Unfortunately this time I've got into "Unable to register subset" error.

I began with the section with SubsetCreate (vDimName, sSubset) and add dimensions one by one using IF and ElseIf, maybe that's my problem and the dimension using MDX be treated separately?

I don't know...

Re: SubsetElementInsert - all items under roll up

Posted: Thu Apr 20, 2017 6:40 pm
by gtonkin
Make sure you cleanup temporary subsets-one probably exists and will need to be destroyed before creating via MDX

Re: SubsetElementInsert - all items under roll up

Posted: Thu Apr 20, 2017 7:01 pm
by kenship
It's clean. All temp. view and subsets are empty.

I don't know. Will take a break and revisit it.

Thanks!

Kenneth

Re: SubsetElementInsert - all items under roll up

Posted: Thu Apr 20, 2017 8:54 pm
by PavoGa
Just a hint to make the code a little cleaner to view: the functions that return a set do not require the {} to work properly. The {} are redundant. Only around members are those required in order to return a set when a set is needed.

For instance:

Code: Select all

TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER( {[FINDW_Account].[N02PNL],[FINDW_Account].[N02BUD]}, ALL, RECURSIVE ), 0)
Works as well as:

Code: Select all

{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[FINDW_Account].[N02PNL],[FINDW_Account].[N02BUD]}, ALL, RECURSIVE )}, 0)}
With regards to the code that is failing:

Code: Select all

sMDX='{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( [FINDW_Account].[N02PNL])}, ALL, RECURSIVE )}, 0)}';
Here is the correction:

Code: Select all

sMDX='TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER( {[FINDW_Account].[N02PNL]}, ALL, RECURSIVE), 0)';
The {} needed to surround [FINDW_Account] and the first ) after [N02PNL] was removed. Hope this solves it for you.

Re: SubsetElementInsert - all items under roll up

Posted: Fri Apr 21, 2017 1:34 pm
by kenship
Thanks, the line works. But I still can't register the subset. After some testing I realize

SubsetCreateByMDX(sSubset, sMDX);

doesn't work when it being placed in a sequence of creating subset for each dimension in the cube to create a source view.

I tried using sSubset1 but not being able to assign it to the source view.

BTW, temporary view and subsets are all deleted before testing.

Still trying to get around it.

But thanks!

Kenneth

Code: Select all

#  cube info
sCube = 'FINDW_Ledger';
sSubset = 'source_transfer_ledger_to_obm_actuals_subset';
sView = 'source_transfer_ledger_to_obm_view';

#Recreate the Source View
IF(ViewExists(sCube, sView) = 1);
	ViewDestroy(sCube,sView);

ENDIF;
ViewCreate(sCube, sView);

vSOldLogChanges = CubeGetLogChanges(sCube);
vTOldLogChanges = CubeGetLogChanges(tCube);
CubeSetLogChanges(sCube, 0);
CubeSetLogChanges(tCube, 0);

######## Create other subsets and Source View#########
vIndexDim = 1;
WHILE(TABDIM(sCube, vIndexDim) @<> '');
	vDimName = TABDIM(sCube, vIndexDim);

	#Subset Creation
	IF(SubsetExists(vDimName, sSubset) = 1);
		#  if the subset already exists, destroy it for creation later
		SubsetDestroy(vDimName,sSubset);
	ENDIF;

	#  Create and add elements
	SubsetCreate(vDimName, sSubset);
                
	# Adding boundaries onto subsets
	IF (vDimName @= 'FINDW_Account');
        sMDX='{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER( {[FINDW_Account].[N02PNL], [FINDW_Account].[N02BUD]}, ALL, 
        RECURSIVE), 0)}';
        SubsetCreateByMDX(sSubset, sMDX);

	#Creates all N level elements within each dimension (default case)
	ELSE;
		vIndex = 1;
		vIndexSub = 1;

		WHILE(vIndex <= DIMSIZ(vDimName));
			vElement = DIMNM(vDimName, vIndex);
			IF(DTYPE(vDimName, vElement)@='N');
				SubsetElementInsert(vDimName, sSubset,vElement, vIndexSub);
				vIndexSub = vIndexSub +1;
			ENDIF;
			vIndex = vIndex + 1;
		END;
	ENDIF;

	ViewSubsetAssign(sCube, sView, vDimName, sSubset);
	vIndexDim = vIndexDim +1;
END;

Re: SubsetElementInsert - all items under roll up

Posted: Fri Apr 21, 2017 2:26 pm
by Wim Gielis
If you do SubsetCreate in the code followed by SubsetCreateByMDX (on the same dimension and subset), it will fail.

Re: SubsetElementInsert - all items under roll up

Posted: Fri Apr 21, 2017 2:31 pm
by Wim Gielis
Revised coding:

Code: Select all

#  cube info
sCube = 'FINDW_Ledger';
sSubset = 'source_transfer_ledger_to_obm_actuals_subset';
sView = 'source_transfer_ledger_to_obm_view';

#Recreate the Source View
ViewDestroy(sCube,sView);
ViewCreate(sCube, sView);

CubeSetLogChanges(tCube, 0);

######## Create other subsets and Source View#########
vIndexDim = 1;
WHILE(TABDIM(sCube, vIndexDim) @<> '');
   vDimName = TABDIM(sCube, vIndexDim);

   SubsetDestroy(vDimName,sSubset);
             
   #  Create and add elements
   # Adding boundaries onto subsets
   IF (vDimName @= 'FINDW_Account');
      sMDX = '{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER( {[' | vDimName | '].[N02PNL], [' | vDimName | '].[N02BUD]}, ALL, RECURSIVE), 0)}';
   ELSE;
	  sMDX = '{TM1FILTERBYLEVEL( TM1SUBSETALL( [' | vDimName | ']), 0)}';
   ENDIF;
   SubsetCreateByMDX(sSubset, sMDX);

   ViewSubsetAssign(sCube, sView, vDimName, sSubset);
   vIndexDim = vIndexDim + 1;
END;

However, if you are unfamiliar with writing TI code, please consider Bedrock.
Also, adding dimensions at N-level is not needed strictly speaking, so the loop could be greatly simplified and even removed.

Re: SubsetElementInsert - all items under roll up

Posted: Fri Apr 21, 2017 2:48 pm
by kenship
Dear Wim,

Thanks for the revised code.

Can I ask a simple question?

Does it mean that if I'm using MDX in a loop to add subsets, I should use MDX for other dimensions (if I want to add specific group) as well instead of subsetelementinsert?

Kenneth

Re: SubsetElementInsert - all items under roll up

Posted: Fri Apr 21, 2017 2:56 pm
by Wim Gielis
You can decide subset by subset if you use SubsetCreateByMDX or SubsetCreate/SubsetElementInsert.

Re: SubsetElementInsert - all items under roll up

Posted: Fri Apr 21, 2017 3:16 pm
by kenship
OK. I'll trying implementing it.

Thanks!

Re: SubsetElementInsert - all items under roll up

Posted: Wed Apr 26, 2017 12:47 pm
by kenship
Hi Wim,

I started working on it yesterday and yes it's working now!

Thanks a lot!

Kenneth

Re: SubsetElementInsert - all items under roll up

Posted: Wed Apr 26, 2017 1:17 pm
by Wim Gielis
kenship wrote:Hi Wim,

I started working on it yesterday and yes it's working now!

Thanks a lot!

Kenneth
Good news, well done !
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited