Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member204026
Active Participant

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.

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

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

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

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)

3 Comments
Labels in this area