SubsetElementInsert - all items under roll up

[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

[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);

[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;

[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);

[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...

[gtonkin]

Make sure you cleanup temporary subsets-one probably exists and will need to be destroyed before creating via MDX

[kenship]

It's clean. All temp. view and subsets are empty.

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

Thanks!

Kenneth

[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.

[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;

[Wim Gielis]

If you do SubsetCreate in the code followed by SubsetCreateByMDX (on the same dimension and subset), it will fail.

[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.

[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

[Wim Gielis]

You can decide subset by subset if you use SubsetCreateByMDX or SubsetCreate/SubsetElementInsert.

[kenship]

OK. I'll trying implementing it.

Thanks!

[kenship]

Hi Wim,

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

Thanks a lot!

Kenneth

[Wim Gielis]

Hi Wim,

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

Thanks a lot!

Kenneth

Good news, well done !