This Add-In for Excel was developed by me for free use, without any warranty!

Functions: Test Hierarchy data of the BPC Dimension before processing Dimension.

Usage: Install Add-In, on the Add-Ins Ribbon you will see one button for this Add-In.

When you have Dimension template opened (in BPC Client or as a standalone file) – press this button.

All Hierarchies will be tested and the resulted TreeView will be presented. In case of error the message will describe the error, the TreeView form will not be shown and the cell with error will be selected.

Enforced rule: Non base members of one Hierarchy cannot have parent in another Hierarchy! (It’s not mandatory in BPC but to my mind, linking nodes of one Hierarchy to parents in another Hierarchy will make the dimension unmanageable)

Additional feature: when you see a form with TreeView there is a combobox in the left down corner of the form. This combobox is populated with the list of member properties. If you select some property it will be shown in the TreeView right to each member.

Number of members is limited to 10 000, but it can be changed in the code.

How to create the Add-In:

In the new Excel file open VBA editor and go to References:

Create a new user form: frmTree with the following controls:

trvTest (TreeView3)

cmbProp (ComboBox)

btnClose (CommandButton) – Caption: Close

Insert code from the attached file frmTree_Code.txt in the Code window of this user form

Create a new module: modTestTree

Insert code from the attached file modTestTree_Code.txt in the Code window of this user form.

Open the Code window of ThisWorkbook

Insert code from the attached file ThisWorkbook_Code.txt in the Code window of ThisWorkbook.

Save the file as checkdim.xla and install it.

B.R. Vadim

Links to code:

To report this post you need to login first.


You must be Logged on to comment or reply to a post.

Leave a Reply