Additional Blogs by Members
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 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 http://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

  1. 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

    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

 

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 = "http://www.w3.org/2001/XMLSchema-instance"

    objRootElem.setAttributeNode objMemberRel

    ' XSD Attribute

    Set objMemberRel = objDOM.createAttribute("xmlns:xsd")

    objMemberRel.NodeValue = "http://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 http://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”.

 


Output

Step by Step Video

Reference Links

Passing parameters to package using VB (http://scn.sap.com/docs/DOC-32636)

How to enable check box for member selection in SAP BPC EPM Add-in reports (http://scn.sap.com/docs/DOC-59363)


8 Comments