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

/wp-content/uploads/2013/12/image002_350473.jpg

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

Step 3: Create a new module

/wp-content/uploads/2013/12/image004_350609.jpg

Step 4: Set reference to FPMXLClient

/wp-content/uploads/2013/12/image006_350610.jpg


/wp-content/uploads/2013/12/image008_350611.jpg

Step 5: Use the following code

/wp-content/uploads/2013/12/image010_350612.jpg

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…

/wp-content/uploads/2013/12/image012_350613.jpg

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)
To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Elena Kochegarova

    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.

    (0) 
    1. Badrish Shriniwas 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.

      (0) 

Leave a Reply