BPC NW 10: VBA to get dimension members list and properties

EPM Excel Add-In provides number of methods to get members and properties. I decided to write some procedure to get all members of the specified dimension with all properties including hierarchies parents. The result is written to the selected Excel sheet the same way as in Administration client of BPC NW 7.5.

Methods of FPMXLClient.EPMAddInAutomation used:

GetPropertyList(strConn, strDim)

to get all properties of the dimension strDim

GetHierarchyMembers(strConn, "", strDim)

to get all members of all hierarchies (“”) of the dimension strDim (base members in different hierarchies will be duplicated!)

GetMemberCaption(strConn, strDim & ":" & strMemShortName)

to get member strMemShortName description of the dimension strDim

GetPropertyValue(strConn, strMemFullName, strProperty)

to get member strMemFullName ([…].[…].[…]) property strProperty

There is some specifics of GetPropertyValue – full member name have to be in the proper hierarchy to get properies like PARENTH1…

The code of the procedure is attached in the text file.

The result of the procedure execution:

You can modify this code to create custom member selection pop-ups, etc…

B.R. Vadim

The attached text file can be found here: https://answers.sap.com/storage/attachments/3793-bpc-nw-10-vba.txt