Skip to Content
Author's profile photo Badrish Shriniwas
Badrish Shriniwas
December 14, 2014 4 minute read

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

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.

/wp-content/uploads/2014/12/image001_607861.png

/wp-content/uploads/2014/12/image002_607884.png

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

/wp-content/uploads/2014/12/image003_607885.png  .

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.

/wp-content/uploads/2014/12/image004_607886.png

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.

/wp-content/uploads/2014/12/image005_607887.png

 

Video

Additional Info

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

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Badrish, this is wonderful, highly useful !!

      This will save a lot of time and would help to lay down a strutured technique of maintaining these settings.

      Continue writing !! šŸ™‚

      Cheers, Rohit

      Author's profile photo Former Member
      Former Member

      Very interesting. I used a similar approach to solve the same problem.

      My solution (code below) was to create two macros, one that "opens" a published worksheet for editing or debugging and second that "closes" the worksheet by restoring the settingsĀ to their prior state.

      Our developers add these two macros to their PERSONAL.xlsb file in Excel and assign them to the hot keys Ctrl-Shift-O for OPEN and Ctrl-Shift-C for close.

      This approach is tailored to a specific client that publishes reports in a specific way, but I think it would be widely applicable and could easily be modified to address site-specific issues.

      The "open"Ā routine:

      + Creates a hidden temp sheet to store the current settings, similar to the rngConfigSheet

      + finds all hidden rows and columns, records their location and unhides them

      + records the settings for Grid Lines, Headings, FormulaBar and Zoom and turns on Grid Lines, Headings and the Formula bar

      + records the state and position of the Freeze Panes and turns off freeze panes

      + unhides sheets with names starting with "EPMFormat"

      The "close" routine simply restores all of the settings recorded by the open routine and deletes the temp sheet. Because the settings are stored in a separate tab, the open and closeĀ routines can be performed at any time and for multiple different sheets. A developer could open 2 sheets while leaving 3 closed, do some work on one of them, close it, save the workbook and then come back the next day and finish the work on the remaining open sheet and close it.

      Anyway, enjoy! Let me know if you find it useful or add any enhancements.

      Cheers,

      Steve Clymer

       

      Sub EPM_OpenEditSheet()
      '-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
      ' Created by: Steve Clymer
      ' Date: 6/28/2017
      ' Macro set: EPM_OpenEditSheet / EPM_CloseEditSheet
      '
      ' Set of macros to open up a published, presentation-ready BPC EPM Report
      ' for editing and debugging and to save the relevent settings to
      ' hidden sheet so that the original settings can be restored
      '
      ' TODO: 1) Use named ranges for hidden rows/columns and freeze pane position.
      ' Currently, CANNOT INSERT OR DELETE ROWS/COLUMNS without messing up
      ' hide/unhide position.
      ' 2) Switch restore to search for values rather than fixed order
      ' 3) Add error trapping.
      '-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
      Dim wks, wksTemp As Worksheet

      Set wks = ActiveSheet
      If Left(wks.Name, 5) = "$TMP$" Then
      MsgBox ("Activate Desired Report Worksheet")
      Exit Sub
      End If
      '*** Create temporary sheet to record settings
      strTempName = "$TMP$" & Right(wks.Name, 26)
      Set wksTemp = Nothing
      On Error Resume Next
      Set wksTemp = Sheets(strTempName)
      On Error GoTo 0
      If Not wksTemp Is Nothing Then
      Application.DisplayAlerts = False
      wksTemp.Delete
      Application.DisplayAlerts = True
      End If
      Set wksTemp = Sheets.Add
      wksTemp.Name = strTempName

      '*** Find, Record, Unhide each column
      iRow = 1
      iCol = 1
      wksTemp.Cells(iRow, iCol).Value = "Hidden Cols"
      For Each col In wks.UsedRange.Columns
      If col.EntireColumn.Hidden = True Then
      iRow = iRow + 1
      wksTemp.Cells(iRow, iCol).Value = col.Column
      col.EntireColumn.Hidden = False
      End If
      Next col

      '*** Find, Record, Unhide each row
      iRow = 1
      iCol = 2
      wksTemp.Cells(iRow, iCol).Value = "Hidden Rows"
      For Each Row In wks.UsedRange.Rows
      If Row.EntireRow.Hidden = True Then
      iRow = iRow + 1
      wksTemp.Cells(iRow, iCol).Value = Row.Row
      Row.EntireRow.Hidden = False
      End If
      Next Row

      '*** Record additional sheet settings
      wks.Activate
      iRow = 1
      iCol = 3
      wksTemp.Cells(iRow, iCol).Value = "Misc Attr"
      wksTemp.Cells(iRow, iCol + 1).Value = "Value"
      iRow = iRow + 1
      wksTemp.Cells(iRow, iCol).Value = "Worksheet"
      wksTemp.Cells(iRow, iCol + 1).Value = wks.Name
      iRow = iRow + 1
      ' Display headings
      wksTemp.Cells(iRow, iCol).Value = "Headings"
      wksTemp.Cells(iRow, iCol + 1).Value = ActiveWindow.DisplayHeadings
      ActiveWindow.DisplayHeadings = True
      iRow = iRow + 1
      ' Display gridlines
      wksTemp.Cells(iRow, iCol).Value = "Gridlines"
      wksTemp.Cells(iRow, iCol + 1).Value = ActiveWindow.DisplayGridlines
      ActiveWindow.DisplayGridlines = True
      iRow = iRow + 1
      ' Record Zoom
      wksTemp.Cells(iRow, iCol).Value = "Zoom"
      wksTemp.Cells(iRow, iCol + 1).Value = ActiveWindow.Zoom
      iRow = iRow + 1
      ' Display Formula Bar
      wksTemp.Cells(iRow, iCol).Value = "FormulaBar"
      wksTemp.Cells(iRow, iCol + 1).Value = Application.DisplayFormulaBar
      Application.DisplayFormulaBar = True
      iRow = iRow + 1
      ' Unfreeze Panes
      wksTemp.Cells(iRow, iCol).Value = "FreezePanes"
      wksTemp.Cells(iRow, iCol + 1).Value = ActiveWindow.FreezePanes
      iRow = iRow + 1
      wksTemp.Cells(iRow, iCol).Value = "FreezeAddr"
      Set cellFreeze = ActiveWindow.Panes(1).VisibleRange
      Set cellFreeze = cellFreeze.Cells(cellFreeze.Rows.Count + 1, cellFreeze.Columns.Count + 1)
      wksTemp.Cells(iRow, iCol + 1).Value = cellFreeze.Address
      ActiveWindow.FreezePanes = False

      '*** Unhide Formatting Sheets
      For Each wksHide In Application.Sheets
      If Left(wksHide.Name, 9) = "EPMFormat" Then
      wksHide.Visible = xlSheetVisible
      End If
      Next wksHide

      '*** Hide the Temp Sheet
      wksTemp.Visible = xlSheetHidden
      wks.Range("A1").Activate
      End Sub

      Sub EPM_CloseEditSheet()
      '-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
      ' Created by: Steve Clymer
      ' Date: 6/28/2017
      ' Macro set: EPM_OpenEditSheet / EPM_CloseEditSheet
      '
      ' Set of macros to open up a published, presentation-ready BPC EPM Report
      ' for editing and debugging and to save the relevent settings to
      ' hidden sheet so that the original settings can be restored
      '
      ' TODO: 1) Use named ranges for hidden rows/columns and freeze pane position.
      ' Currently, CANNOT INSERT OR DELETE ROWS/COLUMNS without messing up
      ' hide/unhide position.
      ' 2) Switch restore to search for values rather than fixed order
      ' 3) Add error trapping.
      '-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
      Dim wks, wksTemp As Worksheet

      Set wks = ActiveSheet
      If Left(wks.Name, 5) = "$TMP$" Then
      MsgBox ("Activate Desired Report Worksheet")
      Exit Sub
      End If
      '*** Create temporary sheet to record values
      strTempName = Left("$TMP$" & wks.Name, 31)
      Set wksTemp = Nothing
      On Error Resume Next
      Set wksTemp = Sheets(strTempName)
      On Error GoTo 0
      If wksTemp Is Nothing Then
      MsgBox ("Settings Sheet Not Found")
      Exit Sub
      End If

      '*** Unhide each column
      iRow = 2
      iCol = 1
      While wksTemp.Cells(iRow, iCol).Value <> ""
      wks.Columns(wksTemp.Cells(iRow, iCol).Value).EntireColumn.Hidden = True
      iRow = iRow + 1
      Wend

      '*** Unhide each row
      iRow = 2
      iCol = 2
      While wksTemp.Cells(iRow, iCol).Value <> ""
      wks.Rows(wksTemp.Cells(iRow, iCol).Value).EntireRow.Hidden = True
      iRow = iRow + 1
      Wend

      '*** Restore other settings
      wks.Activate
      iRow = 3
      iCol = 4
      ' Headings
      ActiveWindow.DisplayHeadings = wksTemp.Cells(iRow, iCol).Value
      iRow = iRow + 1
      ' Gridlines
      ActiveWindow.DisplayGridlines = wksTemp.Cells(iRow, iCol).Value
      iRow = iRow + 1
      ' Zoom
      ActiveWindow.Zoom = wksTemp.Cells(iRow, iCol).Value
      iRow = iRow + 1
      ' FormulaBar
      Application.DisplayFormulaBar = wksTemp.Cells(iRow, iCol).Value
      iRow = iRow + 1
      ' FreezePanes
      If wksTemp.Cells(iRow, iCol).Value = "True" Then
      iRow = iRow + 1
      ' Get FreezeAddr
      wks.Range(wksTemp.Cells(iRow, iCol).Value).Activate
      ActiveWindow.FreezePanes = True
      Else: ActiveWindow.FreezePanes = False
      End If

      '*** Hide Formatting Sheets
      For Each wksHide In Application.Sheets
      If Left(wksHide.Name, 9) = "EPMFormat" Then
      wksHide.Visible = xlSheetHidden
      End If
      Next wksHide

      '*** Now delete temp settings sheet
      Application.DisplayAlerts = False
      wksTemp.Delete
      Application.DisplayAlerts = True
      End Sub