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)
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.
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.
Hi,
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?