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

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

 

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

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

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

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

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

Output

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

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)


To report this post you need to login first.

8 Comments

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

  1. Kaarthic Jayapaul

    This is a brilliant way to totally avoid the Data Manager screens (a separate UI which needs to be taught to users), with the much needed multi-selection option. Users will not have to go through those screens. Completely reduces the number of clicks/key-strikes. Saves end-users from technical stuff and helps to keep the business user engaged with their results.

    (0) 
    1. Badrish Shriniwas Post author

      Yes, the idea was to build the functionality on top of that HTG. Moreover the function fnGetSelected  only returns the members for one dimension selected in the row key range. The users may still want to pass members of other dimensions, for which they can use PACKAGE sheet.

      (0) 
      1. Vadim Kalinin

        As a result, the code for the simple task looks overcomplicated!

        1. It’s better not to use asynchronous function fnGetSelected – instead RunPackage can do the job of creation of member list.

        2. The original code in HTG also have to be improved to split reading parameters from Excel sheet and generation of answer file (createAnswerPromptFile contain both). Reading from Excel has to be improved using range to array copy instead of cell by cell loop.

        3. MSXML is a good library to analyze some incoming XML file, but in this particular case with very simple file structure it’s better to generate it using trivial string concatenation.

        (0) 
        1. Badrish Shriniwas Post author

          Sure, I will look forward for your next blog up on this. Your idea sounds good. I will also try to see if avoiding using function is possible. Thanks for your feedback.

          (0) 

Leave a Reply