BPC 10 Dynamic Reporting – MemberFormula Elements with VBA

Hello everyone 🙂 in this example I am showing you how to set up a report which displays a calculated KPI and its elements (KPI A + KPI B = KPI C) determined by using VBA.

First open Excel and connect to BPC. By logging in keep the name of your connection in mind. We will need it later on.

After this fill the calculated KPI you want to be in the report in cell A1 (you can choose every cell you want, you only have to change the VBA code at line 19).

Now we create a new report with the organizational units dimension on the row axis and the KPI dimension on the column axis. Choose Context Member for the KPI dimension.

Now it is getting interesting. Go to VBA editor (Alt+F11) and create a new module. Copy and paste the following code in that module.

1. Set the context member (KPI) to the value in cell A1
2. Get numerator and denominator
3. Add KPI’s to column axis

PrivateConst con As String = "TestConnection"
Sub Test()
Dim relkpi As Variant
Dim numerator, denominator, context_kpi As String
Dim epm_func As New EPMAddInAutomation
'set context KPI
EPM_SetContext_Dimension      '1
'get numerator + denominator
relkpi = EPM_Get_Formula_Elements '2
numerator = relkpi(1)
denominator = relkpi(2)
'numerator + denominator – kpis which will be added to report
EPM_Add_Member_ColumnAxis numerator, denominator '3
epm_func.Refresh
End Sub
Function EPM_SetContext_Dimension()
Dim client As Object
'fill in KPI at cell A1
set_con_kpis = ThisWorkbook.Worksheets("TEST").Cells(1, 1) 
Set client = Application.COMAddIns("FPMXLClient.Connect").Object
'context member x-axis (ColumnAxis)
client.SetContextMember con, "KPIS", set_con_kpis     'connection, dimension name, dimension member
Set client = Nothing
End Function
Function EPM_Get_Formula_Elements() As Variant
Dim epm_func As New EPMAddInAutomation
Dim context_kpi, formula As String
Dim elements(1 To 2) As String
context_kpi = epm_func.GetContextMember(con, "KPIS")     'context member (kpi)
formula = epm_func.GetPropertyValue(con, context_kpi, "FORMULA")
elements(1) = Right(Left(formula, InStr(1, formula, "/", vbTextCompare) - 2), Len(Left(formula, InStr(1, formula, "/", vbTextCompare) - 2)) - 8)     'numerator
elements(2) = Right(Left(formula, Len(formula) - 1), Len(formula) - InStr(1, formula, "/", vbTextCompare) - 9)     'denominator 
EPM_Get_Formula_Elements = elements
End Function
Function EPM_Add_Member_ColumnAxis(ByVal numerator As String, ByVal denominator As String)
Dim client As Object
Set client = Application.COMAddIns("FPMXLClient.Connect").Object
client.AddMemberToColumAxis ThisWorkbook.Sheets("TEST"), "000", numerator, 1     'worksheet, report id, member, dynamic relation
client.AddMemberToColumAxis ThisWorkbook.Sheets("TEST"), "000", denominator, 1
Set client = Nothing
End Function

List of supported dynamic relations values:

• Member = 1
• Children = 2
• Member and Children = 3
• Descendants = 4
• Member and Descendants = 5
• Leaves = 6
• Same Level = 7
• Siblings = 8
• Ascendants = 9
• Member and Ascendants = 10
• Member and Leaves = 11

For more information please have a look at
http://help.sap.com/businessobject/product_guides/boeo10/en/EPMofc_10_user_en.pdf

That’s it.You now have a dynamically changing report for your calculated KPIs. For more complex calculation then C = A/B you need to change the string clipping in the EPM_Get_Formula_Elements function.

Besides don’t forget to remove your KPIs before adding new ones otherwise your report never stops growing.

You can remove them manually by editing the report or you can do it in VBA (same logic as adding them):

Function EPM_Remove_Member_ColumnAxis(ByVal numerator As String, ByVal denominator As String)
Dim client As Object
Set client = Application.COMAddIns("FPMXLClient.Connect").Object
client.RemoveMemberFromColumnAxis ThisWorkbook.Sheets("TEST"), "000", numerator, 1
client.RemoveMemberFromColumnAxis ThisWorkbook.Sheets("TEST"), "000", denominator, 1
Set client = Nothing
End Function

 
Have fun coding! 😉