Skip to Content
Author's profile photo Badrish Shriniwas

How to loop through dimension members (for report automation) in BPC 10 using EPM Add-in API?

Business Case

The business case is to create a report for each entity (without using distribution option) using EPM 10 add-in API and save the reports in directory.

Technical Steps

  • Get the list of members in entity dimension
  • Loop through each member
    • Change the context member
    • Refresh report
    • Save the copy of the report


Step 1: Create an excel report using EPM 10 add-in


Step 2: Go Visual Basis development window (Press Alt + F11)

Step 3: Create a new module


Step 4: Set reference to FPMXLClient



Step 5: Use the following code


Public Sub subCreateReport()

‘Created by: Badrish Shriniwas Date: 26/12/2013

‘Error handling script

On Error GoTo errHand

‘Declare Variables

Dim EPMObj As New FPMXLClient.EPMAddInAutomation

Dim varEntityID() As String

Dim strEntityId As String

‘Get the list of members in hierarchy H1 of Entity dimension

varEntityID = EPMObj.GetHierarchyMembers(EPMObj.GetActiveConnection(ActiveSheet), “H1”, “Entity”)

‘Loop through each member in varEntityID

For lLoop = 0 To UBound(varEntityID)

    ‘Change the current view

    EPMObj.SetContextMember EPMObj.GetActiveConnection(ActiveSheet), “Entity”, varEntityID(lLoop)


    ‘Refresh the current report


    ‘Save copy of report into a folder with different name

    ActiveWorkbook.SaveCopyAs Filename:=”D:\Technical\SAP BPC\Blogs\List of members\OutFiles\” & varEntityID(lLoop) & “.xls”

Next lLoop

‘Error handling script


If Err.Number <> 0 Then

    MsgBox “Error in processing. ” & Err.Description

End If

End Sub

Step 6: Run procedure/macro (ALT + F8)

Step 7: Check the folder for list of files…


Additional Notes

  • Refer to EPM Add-in for BPC 10 guide for further details
  • Add appropriate error handling in the VB code
  • Provide proper buttons for the users to execute the VBA procedure (macro)

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi, Badrish

      Hope you are ok 🙂

      I try to use your macro, it's good, but I have one issue:Is it possible to loop only through the part of hierarchy?

      I would use this macro for dimension time and I need only one year for report automation.

      Author's profile photo Badrish Shriniwas
      Badrish Shriniwas
      Blog Post Author

      You can get the property value using EPMObj.GetPropertyValue() by passing the appropriate parameters. Then using a IF statement you can filter to the members you need.

      Hope that helps.

      Author's profile photo Former Member
      Former Member



      I am trying to use this macro in dimension "COSTCENTRE" and look at hierarchy of H1 which top level is known as "Total_CC" and i need the list of Cost centres. However it keeps looking at the field OP_CO.


      Are you able to help at all?