How to setup EPM reports and forms in SAP BPC for easy editing

[former_member204026]

Introduction

As part of BPC development where there are multiple reports/forms to be delivered, the major time is spent on editing and updating excel reports/forms. The tasks involved in editing one report could potentially be.

• Un-hiding the sections of reports which were reserved for editing
• Un-freeze panes
• Enabling the row and column heading of the work sheet
• Unprotecting the sheet (using EPM options)

Once these updates are completed, the above tasks will have to be repeated in reverse order. Most of the time; the changes to the reports/forms takes lesser time than performing these activities. Not to mention that most of these changes come up during time and resource critical UAT phase (User Acceptance Test) of the project and there is a possibility of human error in these repetitive tasks.

Overall Business Case

Establish an approach to reduce the number of steps involved in making a report/form change. The aim of this approach is to

• Reduce the time for each change
• Reduce the manual errors and mistakes
• Allow the developers to focus on the change

Session – Steps

Each report/form should be developed based on the following approach in the project.

Step 1:  Create an environment level variable for keeping the password in the developer’s machine.

Go to My Computer >> Properties >> Change Settings >> Go to Advanced Tab >> Click on Environment Variable and add a new variable named “EPMPASS” and give it a value.  Note that the value specified in this environment variable will be used as the password to lock your EPM Report or form.  This should be done only in the BPC forms developers machine, this need not to be done for the end users.

Step 2:  Go to your report and create a named space called “rngSheetConfig”

  .

Step 3:  Maintain the settings and values in the range as shown above

Setting	Description
CurrentState	Denotes the current status of the form. This cell is set by VBA method. Enter the initial value of OPEN.
HideRow	Denotes the rows that you would like to hide. The column references are ignored by the procedure EPMCellRanges can be used to mention the range.
HideCol	Denotes the columns that you would like to hide. The row references in the address are ignored by the procedure EPMCellRanges can be used to mention the range.
FreezePane	Denotes the cell from which to apply the freeze panes option
DisplayHeading	Denotes whether the headings should be hidden. The valid values are FALSE or TRUE

Step 4: Go to VBA editor in your report (press Alt + F11). Add a new module and then add reference to FPMXLClient.

Step 5: Copy the following VBA code to you project.

Dim EPMObj As New FPMXLClient.EPMAddInAutomation

Sub ShowHidePanes()

On Error Resume Next

Dim rngCel As Range, rngCurrentState As Range, rngHideRow As Range, rngHideCol As Range, strSheetPass As String, bitDisplayHeading As Boolean, rngFreezePane As Range

'Gets the password from the environment variable

strSheetPass = Environ("EPMPASS")

If strSheetPass <> "" Then

    'Gather the information needed from the "rngSheetConfig" range

    For Each rngCel In Range("rngSheetConfig").Cells

        If rngCel.Value = "CurrentState" Then

            Set rngCurrentState = rngCel.Offset(0, 1)

        ElseIf rngCel.Value = "HideRow" Then

            Set rngHideRow = Range(rngCel.Offset(0, 1).Value)

        ElseIf rngCel.Value = "HideCol" Then

            Set rngHideCol = Range(rngCel.Offset(0, 1).Value)

        ElseIf rngCel.Value = "DisplayHeading" Then

            bitDisplayHeading = rngCel.Offset(0, 1).Value

        ElseIf rngCel.Value = "FreezePane" Then

            Set rngFreezePane = Range(rngCel.Offset(0, 1).Value)

        End If

    Next

    If rngCurrentState.Value = "OPEN" Then

               'Closes the work area and locks the sheet

        rngHideRow.EntireRow.Hidden = True

        rngHideCol.EntireColumn.Hidden = True

        rngFreezePane.Select

        ActiveWindow.FreezePanes = True

        ActiveWindow.DisplayHeadings = bitDisplayHeading

        rngCurrentState.Value = "CLOSED"

        EPMObj.SetSheetOption ActiveSheet, 300, True, strSheetPass

    Else

               'Unlocks the sheet and opens the work area

        EPMObj.SetSheetOption ActiveSheet, 300, False, strSheetPass

        rngHideRow.EntireRow.Hidden = False

        rngHideCol.EntireColumn.Hidden = False

        ActiveWindow.FreezePanes = False

        ActiveWindow.DisplayHeadings = True

        rngCurrentState.Value = "OPEN"

    End If

End If

End Sub

Step 6: Assign the method (macro) ShowHidePanes to the logo. You can assign this to a button too.

Video

Additional Info

• Refer to latest EPM Add-in guide for BPC 10 for further details
• Add appropriate error handling in the VB code