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:
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…
The attached text file can be found here: https://answers.sap.com/storage/attachments/3793-bpc-nw-10-vba.txt