[Deb Silverman, Report Coach]

I can’t edit the thread.. adding snapshots that didn’t come through here….

[Deb Silverman, Report Coach]

VBA has become an integral part of EPM solutions, for without we wouldn’t be able to fully harness some truly powerful API’s that come with the tool. Automation has become far more accessible than ever before!

I really like how you handled the validations – we don’t want to waste time or lose our input if something was overlooked. I sometimes use the standard BPC function BEFORE_SAVE() so the data doesn’t get wiped out if there are any events following the save. If i were to use your code it would look something like this for both MS and NW versions (you can copy paste this code and it will work as it is – the object declares go at the top of the module):

Option Explicit

Public api As Object
Public z As Object

Sub SaveAndRefreshData()

On Error Resume Next

If Range(“SAVE_VAL”) = “OK” Then

Set api = Application.COMAddIns(“SapExcelAddIn”).Object

If Not api Is Nothing Then

Set z = api.GetPlugin(“com.sap.epm.FPMXLClient”)

Else

Set z = Application.COMAddIns(“FPMXLClient.Connect”).Object

End If

z.SaveAndRefreshWorksheetData

Else

MsgBox “Save validations are not met, please correct your input”

Exit Sub

End If

‘…. may have more code here??

End Sub

[Deb Silverman, Report Coach]

DRILL-THROUGH: A Drill-Through is a functionality that enables us to access data outside the current BPC report (SQL Server, Oracle, Access, BW, ECC, etc..). the process triggers a query with defined filters containing the current view information necessary to run the query. We do need to know what the data looks like on the other end, so we pass the correct parameters. Why bother? You can use drill-through to get data faster than to switch tools to ‘go get’ the data.

Say we want to drill-through a specific account to another data source, we would assign a “DRILLKEY” property to the account to enable it. There are several options for Drill-Through: URL (any data source), Table in Web, table in Excel. When triggered the parameters are passed with the query. When the query is complete a report will be displayed.

DRILL-DOWN: When we Drill-Down on our data, it is assumed we are within the EPM report and the member we want to ‘drilldown’ on is a parent-level member. Base-level members are the lowest members in the hierarchy. When we have a dynamic EPM report active we can double-click on the parent-level member (a.k.a. calculated member) and the hierarchy will ‘expand’ revealing the children of that parent. This drill-down functionality is a User Option in EPM as shown in the snapshot.

[Deb Silverman, Report Coach]

In event the graphic is missing…

NumID.png 27 KB Image File - Click to view

Options

• Copy Download Link

[Deb Silverman, Report Coach]

Most definitely flaky. The larger the dimension the more flakier. Never-mind that the Export to CSV doesn’t always work. A couple of tricks for starters… before making any BIG changes create a backup of the dimension (if you can’t export it).

1) When copy-pasting cells within the master data sheet, do not enter into the cell with your cursor and grab the text. Click the cell once then right-click copy. The problem most people have is when the cursor goes into the cell, copying just doesn’t work as expected.

2) It is faster to paste data (values only) from an external excel file. I set up the master data and then set up an excel spreadsheet in alignment (columns must match!). build your data externally, then i copy cells (hundreds) and seamlessly copy-paste into Admin. If we inadvertently click on the wrong cell or things go awry just back out without saving and try again.