Delete a consolidated element and all of its children
- Feb. 14, 2010
 | Example files with this article: |
Introduction
Ever wanted a TI process to delete a consolidated element and all of its children - down to the leaf level? Now, it appears to be easy but I was scratching my head once on how to do this.
You can easily loop through the children of a certain element. Just use ELCOMPN to count the number of immediate children, after which you loop through the children using ELCOMP. Should not be too hard. However, if you do not know the number of levels below the consolidated level, you cannot extend this approach (taken for granted that you do not want to add and remove loops each time a level is inserted).
The next thing that comes to mind, is using a Dimension subset as the data source. Take the subset called ALL as your data source. In the Metadata you then use the ELISANC function to test the relationship between the consolidated element, and any element being processed. Decide based on that to delete element or not. However, you cannot delete the consolidated element itself in the loop, since then the parent-child relationship is destroyed. Moreover, you cannot delete an element in the Data tab or the Epilog tab. Adding an extra process, called upon, for just one statement seems superfluous.
MDX again
The best method, yet rather simple, is to use an MDX statement to create a subset. The subset will contain the consolidated element and all of its children. After that, we loop through the subset elements, and delete them one after the other. In fact, this would be analogous to looping through the dimension in the Prolog tab. Yet, looping through a large dimension is not too efficient.
Sample code
Here is some example code to be put in the Prolog tab:
# Wim Gielis # https://www.wimgielis.com##### # TI code to delete a consolidated element # and all of its children # 24/12/09 #####IF(DIMIX('MyDimension','MyConsolidation')>0); SUBSETCREATEBYMDX('MySubset','{Descendants(MyDimension.[MyConsolidation])}'); i=SUBSETGETSIZE('MyDimension','MySubset'); WHILE(i>0); DIMENSIONELEMENTDELETE('MyDimension',SUBSETGETELEMENTNAME('MyDimension','MySubset',i)); i=i-1; END; SUBSETDESTROY('MyDimension','MySubset'); ENDIF;
Now you
To obtain all the below code in a process, right-click the file above and Save it on the hard drive. As you can see above, we use the Descendants function in the MDX statement to yield a list of elements below one element - here it is called MyConsolidation but change it to suit your purposes. In order not to be thrown up an error in case this element would not exist, I expressly test for its existence. Otherwise, the MDX cannot return a valid list and will bug out.
