SAP EPM - VBA Logic after Expand/Collapse

[ChristianSass]

Hi,

We would like to place some VBA after Expand/Collapse in EPM Client. The suggested functions in the EPM guide work for 7.5 but we use 10.x. I've tried to place coding after "AFTER_REFRESH" but this function is not triggered on expand/collapse.

Any hint on that?

Thanx

Chris

[former_member186338 · Accepted solution]

HI Christian,

Are you talking about expand on the parent member? Then there is no special event triggered in this case.

Vadim

[ChristianSass · Accepted solution]

Yes, I think so. - This is bad news

Thx Vadim

[Former Member · Accepted solution]

I've toyed with the following alternative to an after_drilldown event.

If you leverage the before doubleclick event in excel you could write a macro to read the target member and insert the appropriate children members via vba instead of the native addin's drilldown functionality.  Just be sure to cancel the double click event and call a refresh after adding the members.

Its heavily reliant on vba code which is the only drawback. here is a document to identify children of a member in an array which can be used to insert into your report:

Let me know if you have questions regarding this alternative.

edit:

code to get you started, but note the user option change, consider the impacts this may have and turn it back on in an AUTO_CLOSE macro so when the report is closed it resets to on.:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)



    Dim oEPM As Object
    Set oEPM = CreateObject("FPMXLClient.EPMAddInAutomation")
    oEPM.SetUserOption "DoubleClick", False

    'Code to insert members into the report

     oEPM.RefreshActiveSheet
    Cancel = True
End Sub