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

Steps

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)

    Application.Calculate

    ‘Refresh the current report

    EPMObj.RefreshActiveReport

    ‘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

errHand:

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)