Atif
A possible approach is as follows:
First of all, decide what you mean by <= 100. This will usually mean <= 100 in a particular cube, selecting base level elements in the dimension where you want to create the hierarchy, eg base level Departments, then selecting a single value in all the other dimensions, eg Version Actual, Period Current Month (The source View can be made dynamic by using a subset eg zCurrMth, which you just update each month), Total Accounts, etc, depending on the dimensions in the cube.
In the following MDX I wanted to select Departments using the HO_Expense Cube, where their Actuals in March 2008 across All Accounts, had a value <= 100.
An MDX approach is something like the following:
Code: Select all
{ FILTER( {TM1SUBSETALL( [Dept] )}, [HO_Expense].([Scenario].[Actual],[Trans_Mth].[T_2008-03],[HO_Expense_Account].[All Accounts],[HO_Expense_Meas].[Val]) <= 100) }
That will give you a Dynamic MDX subset. You can then read that in TI and update your dimension appropriately.
An alternative, without MDX is:
Create a View on the cube - Right click on the cube and select Export as ASCII data, then just save the View and ignore the Export to File option.
The View needs to be set up so that the operation is '<= a' and a value of 100.
I would guess that you would still want to exclude zeros so tick the appropriate box for that in the View. You will however need to keep consolidations if you selected consolidated elements such as Total Accounts.
Select the elements that you decided on above.
Save the View
Create a TI process and select the View that you created as its source (You need to select the cube first then the view).
Read from the View. Ignore all the data except the elements in the dimension that you want to update.
Whether you use an MDX subset or a View as the source, will then want something like the following.
For each element that meets the criteria, de-link it from existing parents.
Code: Select all
# Get the Number of Parents for this element
vNumParents = elparn(vDim,vElem ) ;
# For each parent, delete the link between
# this element and the parent
vCt = 1 ;
WHILE( vCt <= vNumParents );
vParent = elpar(vDim,vElem,vCt) ;
# Error trap for occasional TM1 abberations
IF(vParent @<> '' ) ;
dimensionelementcomponentdelete(vDim,vParent,vElem) ;
ENDIF ;
vCt = vCt + 1 ;
END ;
Code: Select all
dimensionelementinsert(vDim,'',vElem,'n') ;
dimensionelementcomponentadd(vDim,'Other Depts',vElem,1) ;
vDim is a variable you set in the Prolog to the name of the dimension.
The dimensionelementinsert may look odd, but adding an element that already exists does not cause an issue, and in some cases I have found that it is necessary to do this to get TM1 to update the dimension properly.
By the way, I would always call it something like 'Other Depts', instead of just 'Other' as things can get confusing if you have Other in several dimensions.
Regards
Paul Simon