Skip to Content

Introduction

This guide demonstrates how to create dynamic member selection from BPC input forms or reports and pass the selection dynamically back for data manager package for execution.  The guide is divided in two parts,

  • The first part of this session will illustrate how to create dynamic check boxes on BPC’s report or input schedules
  • The second part of this session will cover how to determine the members selected by the users and pass it back to data manager

Overall Business Case

To facilitate member selection from the EPM Add-in reports using a check box type of functionality. The check box should be dynamically applied on the input form or report. The selected members should be used for running a data manger package.

The members of account dimension will be selected by the users from EPM Add-in excel report and the selected members should be pass to data manager – clear package.

Steps

Step 1: Create a simple report using EPM 10 add-in with one dimension (account in this case) in row key range. Make sure that you have one column available before the report/input form.

/wp-content/uploads/2014/11/image001_580457.png

Step 2: Create a new range named “rngRowSelectionSrc”.

/wp-content/uploads/2014/11/image002_580458.png

Step 3: Format this cell to look like a selection box and choose the font a “Wingdings 2”

/wp-content/uploads/2014/11/image003_580460.png

Step 4: Paste the following code in the Excel VBA.

  • Press ALT + F11
  • Create a new module named “modCheckBox”
  • Add reference to FPMXLClient
  • And paste the code below, this  code create the function that allows the creation of new named range “rngRowSelection”     

Public Const iNumberofRowDim As Integer = 1

Sub CreateCheckBox(sRptNumber As String)

Dim oEPMObj As New FPMXLClient.EPMAddInAutomation

Dim oInitRange As Range, oRange As Range, oCell As Range

Dim iColShift, iTopLeftCell, iBottomRightCell, iColumn, iFirstRow, iLastRow As Integer

iColShift = oEPMObj.GetShift(ActiveSheet, sRptNumber, True) * -1

iTopLeftCell = oEPMObj.GetDataTopLeftCell(ActiveSheet, sRptNumber)

iBottomRightCell = oEPMObj.GetDataBottomRightCell(ActiveSheet, sRptNumber)

iColumn = Range(iTopLeftCell).Offset(0, iColShift).Column – iNumberofRowDim

iFirstRow = Range(iTopLeftCell).Offset(0, iColShift).Row

iLastRow = Range(iBottomRightCell).Row

Set oRange = Range(Cells(iFirstRow, iColumn).Address & “:” & Cells(iLastRow, iColumn).Address)

                ActiveWorkbook.Names.Add Name:=”rngRowSelection”, RefersTo:=oRange

Range(“rngRowSelectionSrc”).Copy

Set oInitRange = Selection

Range(“rngRowSelection”).PasteSpecial xlPasteFormats

               Application.CutCopyMode = False

              oInitRange.Select

End Sub

Step 5: Paste the following code in Excel VBA.

  • Press ALT + F11
  • Create a new module named “modEPMAddinAutomation”
  • Add reference to FPMXLClient
  • And paste the code below, this code will invoke the function that will facilitate the creation of named range “rngRowSelection” every time the report is refreshed

Function AFTER_REFRESH()

    CreateCheckBox “000”

    AFTER_REFRESH = True

End Function

Function BEFORE_REFRESH()

     On Error Resume Next

    Range(“rngRowSelection”).ClearFormats

   Range(“rngRowSelection”).Clear

End Function

Step 6: Paste the following code in Excel VBA.

  • Press ALT + F11
  • Double click the sheet which contains the report
  • Paste the following

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Application.Intersect(Range(“rngRowSelection”), Target) Is Nothing Then

    If Target.Value = “P” Then

        Target.Value = “”

    Else

        Target.Value = “P”

    End If

End If

Cancel = True

End Sub

Output

The report output will be like the following

/wp-content/uploads/2014/11/image004_580461.png

Step by Step Video

Next Session

In the session we will cover how to determine the members selected by the users and pass it back to data manager. (How to enable check box for member selection in SAP BPC EPM Add-in reports – Session 2)

To report this post you need to login first.

3 Comments

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

  1. Ashok Babu Kumili

    Hello Badrish Shriniwas.

    Thank you so much for this blog. This is very cool. I specifically love that it is SOLUTION oriented.  This is so organised and well described. Giving 5 Start Rating for the Content..

    Regards-Ashok.

    (0) 

Leave a Reply