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,
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.
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.
Public Function fnGetSelected() As String
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.
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.
Sub RunPackage()
executeDmPackageWithParameters "rngPACKAGE", "rngPARAMETER"
End Sub
Step 5: Add a sheet named “PACKAGE” following the document http://scn.sap.com/docs/DOC-32636 .
Step 6: Add the following formulas in the sheet “PACKAGE”.
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)