How to enable check box for member selection in SAP BPC EPM Add-in reports – Session 2

By admin Last updated Jan 20, 2021

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 checkboxes 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 checkbox 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.

Session 2 – Steps

Step 1: Follow the session 1 and setup the reports https://scn.sap.com/docs/DOC-59363 as mentioned in the link. You should be looking at an EPM add-in report like below.

Step 2: Add the following function in the Excel VBA.

Press ALT + F11
Add the following function to the module named “modCheckBox” (this module should already exist based on session 1)
This function will help to get the selected members as comma separated value

Public Function fnGetSelected() As String

Application.Volatile

Dim strWIP, strTmpAddress, strTmp As String

For Each oCell In Range(“rngRowSelection”).Cells

If oCell.Value = “P” Then

strTmp = “”

strTmpAddress = oCell(1, 2).Formula

strTmp = Mid(strTmpAddress, InStrRev(strTmpAddress, “[“) + 1, InStrRev(strTmpAddress, “]”) – InStrRev(strTmpAddress, “[“) – 1)

If strWIP = “” Then

strWIP = strTmp

Else

strWIP = strWIP & “,” & strTmp

End If

Next oCell

fnGetSelected = strWIP

End Function

Step 3: Add the following function in the Excel VBA.

Press ALT + F11
Create a new module named “modPackageAutomation”
Add reference to following components

And paste the code below, this allows passing parameters to package using VB (https://scn.sap.com/docs/DOC-32636)

Public Sub executeDmPackageWithParameters(sPackage As String, sParameters As String)

createAnswerPromptFile sPackage, sParameters

Dim objDMautomation As EPMAddInDMAutomation

Set objDMautomation = New EPMAddInDMAutomation

objDMautomation.RunPackage objPackageFromSheet(sPackage), strFileName(sPackage)

Dim objEPMAutomation As New EPMAddInAutomation

objEPMAutomation.DataManagerOpenViewStatusDialog

End Sub

Private Function strFileName(strRange As String) As String

‘ Get the range in which the DM package paramteres is set

Dim rngPackageRange As Range

Set rngPackageRange = Application.Names(strRange).RefersToRange

‘ Loop through the rows

For i = 1 To rngPackageRange.Rows.Count

If rngPackageRange(i, 1).Value = “PromptFile” Then

strFileName = rngPackageRange(i, 2).Value

Exit Function

End If

Next

End Function

Private Function strPackageDescription(strRange As String) As String

‘ Get the range in which the DM package paramteres is set

Dim rngPackageRange As Range

Set rngPackageRange = Application.Names(strRange).RefersToRange

‘ Loop through the rows

For i = 1 To rngPackageRange.Rows.Count

If rngPackageRange(i, 1).Value = “PackageId” Then

strPackageDescription = rngPackageRange(i, 2).Value

Exit Function

End If

Next

End Function

Private Function objPackageFromSheet(strRange As String) As ADMPackage

‘ Get the range in which the DM package paramteres is set

Dim rngPackageRange As Range

Set rngPackageRange = Application.Names(strRange).RefersToRange

‘ Create the ADM Package object

Set objPackageFromSheet = New ADMPackage

‘ Loop through the rows

For i = 1 To rngPackageRange.Rows.Count

Select Case rngPackageRange(i, 1).Value

Case “Filename”

objPackageFromSheet.Filename = rngPackageRange(i, 2).Value

Case “GroupId”

objPackageFromSheet.GroupId = rngPackageRange(i, 2).Value

Case “PackageDesc”

objPackageFromSheet.PackageDesc = rngPackageRange(i, 2).Value

Case “PackageId”

objPackageFromSheet.PackageId = rngPackageRange(i, 2).Value

Case “PackageType”

objPackageFromSheet.PackageType = rngPackageRange(i, 2).Value

Case “TeamId”

objPackageFromSheet.TeamId = rngPackageRange(i, 2).Value

Case “UserGroup”

objPackageFromSheet.UserGroup = rngPackageRange(i, 2).Value

End Select

Next

End Function

Private Sub createAnswerPromptFile(strPackageName As String, strParametersName As String)

‘ Create a new XML document

Dim objDOM As DOMDocument

Set objDOM = New DOMDocument

‘ Set the processing instruction of the XML document

Dim objProcessingInstruction As IXMLDOMProcessingInstruction

Set objProcessingInstruction = objDOM.createProcessingInstruction(“xml”, ” version=’1.0′ encoding=’utf-16′”)

objDOM.appendChild objProcessingInstruction

‘ Create root element

Dim objRootElem As IXMLDOMElement

Set objRootElem = objDOM.createElement(“ArrayOfAnswerPromptPersistingFormat”)

objDOM.appendChild objRootElem

‘ XSI Attribute

Dim objMemberRel As IXMLDOMAttribute

Set objMemberRel = objDOM.createAttribute(“xmlns:xsi”)

objMemberRel.NodeValue = “https://www.w3.org/2001/XMLSchema-instance“

objRootElem.setAttributeNode objMemberRel

‘ XSD Attribute

Set objMemberRel = objDOM.createAttribute(“xmlns:xsd”)

objMemberRel.NodeValue = “https://www.w3.org/2001/XMLSchema“

objRootElem.setAttributeNode objMemberRel

‘ Get the range of cells containing the parameters

Dim rngParameters As Range

Set rngParameters = ThisWorkbook.Names(strParametersName).RefersToRange

‘Excel.Names(strParametersName).RefersToRange

‘

Dim objCurrentStringPairParent As IXMLDOMElement

‘ Loop through each row

For i = 1 To rngParameters.Rows.Count

‘ See which type of parameter is being passed

Select Case rngParameters(i, 2).Value

‘ If it is a single Parameter, then add a parameter node to the root node

Case “Parameter”

addSingleSelectionParameterToXML rngParameters(i, 1).Value, rngParameters(i, 4).Value, objRootElem, objDOM

‘ If it is a list of values

Case “StringListPairs”

‘ If it’s a new set of String List Pairs, then create a new parent

If rngParameters(i, 1).Value <> strCurrentStringPair Then

strCurrentStringPair = rngParameters(i, 1).Value

Set objCurrentStringPairParent = getStringListPairParent(rngParameters(i, 1).Value, objRootElem, objDOM)

End If

‘ Add the Dimension Name and Value to the parent

addStringListPair rngParameters(i, 3).Value, rngParameters(i, 4).Value, objCurrentStringPairParent, objDOM

End Select

Next

‘ Create the File object

Dim objFile As FileSystemObject

Set objFile = New FileSystemObject

‘ Create a stream to create and write to the file

Dim objStream As TextStream

Set objStream = objFile.OpenTextFile(strFileName(strPackageName), ForWriting, True)

‘ Write the name of the DM package first and then the XML output

objStream.WriteLine strPackageDescription(strPackageName) & “{param_separator}” & objDOM.XML

‘ Close the file

objStream.Close

End Sub

Private Function addStringListPair(strVariableName As String, strValue As String, objParent As IXMLDOMElement, objDOM As DOMDocument)

‘ Create the “StringListPair” node

Dim objStringListPairElement As IXMLDOMElement

Set objStringListPairElement = _

objDOM.createElement(“StringListPair”)

objParent.appendChild objStringListPairElement

‘ Create the “Str” element containing the variable name

Dim objStrElement As IXMLDOMElement

Set objStrElement = objDOM.createElement(“str”)

objStringListPairElement.appendChild objStrElement

objStrElement.Text = strVariableName

‘ Create the “lst” element

Dim objLstElement As IXMLDOMElement

Set objLstElement = objDOM.createElement(“lst”)

objStringListPairElement.appendChild objLstElement

‘ Create the “string” element containing the variable value

Dim objStringElement As IXMLDOMElement

Set objStringElement = objDOM.createElement(“string”)

objLstElement.appendChild objStringElement

objStringElement.Text = strValue

End Function

Private Function getStringListPairParent(strVariableName As String, objParent As IXMLDOMElement, objDOM As DOMDocument) As IXMLDOMElement

‘ Create the “AnswerPromptPersistingFormat” node

Dim objAnswerPromptPersistingFormatElement As IXMLDOMElement

Set objAnswerPromptPersistingFormatElement = _

objDOM.createElement(“AnswerPromptPersistingFormat”)

objParent.appendChild objAnswerPromptPersistingFormatElement

‘ Create the “_ap” node

Dim objApElement As IXMLDOMElement

Set objApElement = objDOM.createElement(“_ap”)

objAnswerPromptPersistingFormatElement.appendChild objApElement

‘ Create the parameter name element

Dim objParameterElement As IXMLDOMElement

Set objParameterElement = objDOM.createElement(“Name”)

objApElement.appendChild objParameterElement

objParameterElement.Text = strVariableName

‘ Create the values element

Dim objValuesElement As IXMLDOMElement

Set objValuesElement = objDOM.createElement(“Values”)

objApElement.appendChild objValuesElement

‘ Create the “_apc” node

Set getStringListPairParent = objDOM.createElement(“_apc”)

objAnswerPromptPersistingFormatElement.appendChild getStringListPairParent

End Function

Private Function addSingleSelectionParameterToXML(strVariableName As String, strValue As String, objParent As IXMLDOMElement, objDOM As DOMDocument)

‘ Create the “AnswerPromptPersistingFormat” node

Dim objAnswerPromptPersistingFormatElement As IXMLDOMElement

Set objAnswerPromptPersistingFormatElement = _

objDOM.createElement(“AnswerPromptPersistingFormat”)

objParent.appendChild objAnswerPromptPersistingFormatElement

‘ Create the “_ap” node

Dim objApElement As IXMLDOMElement

Set objApElement = objDOM.createElement(“_ap”)

objAnswerPromptPersistingFormatElement.appendChild objApElement

‘ Create the parameter name element

Dim objParameterElement As IXMLDOMElement

Set objParameterElement = objDOM.createElement(“Name”)

objApElement.appendChild objParameterElement

objParameterElement.Text = strVariableName

‘ Create the values element

Dim objValuesElement As IXMLDOMElement

Set objValuesElement = objDOM.createElement(“Values”)

objApElement.appendChild objValuesElement

‘ Create the string element with the value passed to the parameter

Dim objStringElement As IXMLDOMElement

Set objStringElement = objDOM.createElement(“string”)

objValuesElement.appendChild objStringElement

objStringElement.Text = strValue

End Function

Step 4: Add the following function in the Excel VBA.

Press ALT + F11
Add the following function to the module named “modEPMAddinAutomation” (this module should already exist based on session 1)
This procedure will aid in running the data manage package using the procedures mentioned in Step 3

Sub RunPackage()

executeDmPackageWithParameters “rngPACKAGE”, “rngPARAMETER”

End Sub

Step 5: Add a sheet named “PACKAGE” following the document https://scn.sap.com/docs/DOC-32636 .

Add a sheet named “PACKAGE”
Add two ranges “rngPACKAGE” and “rngPARAMETER” following the steps mentioned in the document
Once configured the sheet should contain the following elements (refer the link for more details)

Step 6: Add the following formulas in the sheet “PACKAGE”.