Page Axis Override with Multi members in SAP BPC NW

Hi friends it has been observed that we are not able to override the page axis with multi members by using cell reference. Below is the sample VBA code to achieve the same.

Sample requirements:

1. Dimension:  Costcenter_test —> Page axis cell ” B1”
2. Dimension: Profitcenter–> Row axis
3. Dimension: Time–> Column axis

Assuming cell “C3” already have the dimension members separated by commas

Ex: 10101008, 10101009, 10101010

Before:

ALT + F11   “ open the VBA editor”

Insert >>new module

sample code is available in APPENDIX

After macro code is run, the default report  page axis  formula has been inserted with multi members.

please note that here we are explicitly refreshing the report through macro to recognize the newly inserted formula in the page axis.

As per the business requirement you can call the macro  through function BEFORE_REFRESH in this case it is not required to refresh the report explicitly

and the code “EPM.RefreshActiveSheet”  can be removed/commented

if we double click on the costcenter_test dimension in cell “B1” then we would be able to see the olapmultiplemembers that were recognized.

this means page axis has been override with multi members.

I am beginner in VBA , please provide your valuable comments or suggestions.. 🙂

Thanks,

Dinesh.V

APPENDIX

**********************************Start*****************************************************************************************************

Sub pageaxis()

Dim ARY() As String

Dim i As Integer

Dim comm As String

comm = Range(“C3”).Value     ‘CELL C3 contains dimension members seperated by commas

Set R1 = Range(“B1”)         ‘ cell B1 would be pageaxis of dimension Time

Dim EPM As New FPMXLClient.EPMAddInAutomation

ARY = Split(Range(“C3”), “,”)   ‘ splitting the dimension members in cell and passing to array

Final = “=EPMOlapMultiMember(“”” & comm & “””,””000″””     ‘ formulae  for defult report (i.e “000”)

For i = LBound(ARY) To UBound(ARY)

Final = Final & “,” & “””” & “[COSTCENTER_TEST].[PARENTH1].[” & ARY(i) & “]” & “”””

Next i

Final = Final & “)”

ThisWorkbook.Worksheets(“Sheet2”).Range(“B1”).Formula = Final     ‘ copying the final olapMultimember formula to page axis

EPM.RefreshActiveSheet ‘ refreshing the report

End Sub

**********************************End*****************************************************************************************************