Skip to Content

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
To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Rohit Sharma

    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

    (0) 
  2. Steve Clymer

    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

    (0) 

Leave a Reply