Issue useing VBA to call data manager package in BPC 10

[former_member225113]

Hi Expert,

I’m wondering if anyone can help with this. We are using BPC NETWEAVER V10, EPM 10 SP22, EXCEL 2007.

I try to use visual basic to help user run a DMP directly from Excel input sheet.The following article provided very good step by step instructions.I have followed the steps illustrated and got it to work with one issue where it runs on ALL scoped out categories, rather on the specific category listed in the PARAMETERS range.I have only one Prompt for this DMP and the screen shot provides the setup of the name range.I also included the VBA scripts which I suspected might have some issue.If you can give any comment, that would be great.Thanks!

How to Call BPC Data Manager Package from Visual Basic

http://www.sap.com/documents/2015/08/f8fac683-547c-0010-82c7-eda71af511fa.html

VBA:

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

[Former Member]

Hi Benjamin,

Before moving to the solution given in the document......would first like to know...what is your requirement??? Are you also trying to run Copy Package? Please explain what is it that you are trying to achieve?

JP

[former_member186338 · Accepted solution]

You have to debug yourself VBA code and check the contents of generated answer file!

Also Prompt is incorrect - %CATEGORY_DIM% is wrong!

Please provide your advanced DM script.

[former_member186338 · Accepted solution]

P.S. What is your real name of CATEGORY type dimension?

[former_member225113 · Accepted solution]

Hi Vadim,

I changed Prompt from %CATEGORY_DIM% to %SELECTION% but it still didn't work. I compared package log run by DMP and it seems that the Category defined in the Excel NameRange is not passed for execution; so the scripts run on ALL categories.

The generated answer file:

YEE10{param_separator}<?xml version="1.0" encoding="utf-16"?> <ArrayOfAnswerPromptPersistingFormat xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><AnswerPromptPersistingFormat><_ap><Name>%SELECTION%</Name><Values/></_ap><_apc><StringListPair><str>Category</str><lst><string>BRSFCST10_09G</string></lst></StringListPair></_apc></AnswerPromptPersistingFormat></ArrayOfAnswerPromptPersistingFormat>

[former_member186338 · Accepted solution]

Please compare your prompt with a sample xml file provided in the document:

http://www.sap.com/documents/2015/08/f8fac683-547c-0010-82c7-eda71af511fa.html

Your answer file is incorrect!

[former_member186338 · Accepted solution]

P.S. Try to reproduce the full code of the tutorial and ensure that you can get the same results...

[former_member225113 · Accepted solution]

Hi Vadim,

Your hunch is right. Our dimension name is Category, but I need to enter in the PARAMETERS NameRange as CATEGORY. Then it works. A bit strange, but works ....

Thanks for your quick help.

[former_member225113 · Accepted solution]

It appears that it works well and I can get the same results.

On a separate note, do you know if it is possible to call up the View Status of DMP from visual basic?

[former_member186338 · Accepted solution]

Unfortunately no way...

[former_member225113]

Thanks Vadim!

The advanced scripts are as following:

PROMPT(SELECTINPUT,,,,"%CATEGORY_DIM%") TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%) TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%) TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%) TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%) TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,YEE10.LGF)

The real name of Type C dimension is Category.

[former_member225113]

Hi JP,

We have scripts to be run by DMP to calculate certain expenses. If run by DMP it works fine. Because the users are finance background and probably going through all these steps to run a DMP is likely a bit cumbersome for them, I found the method described in the document is very useful so I follow the steps to run our DMP. I could get the DMP run but the prompt value is not passed.

Hope this explains.

[Former Member]

Sorry but are you planning to pass the selections from the Excel to DMP??? Or is user fine with selecting the selections while executing the DMP? And which package are you using to execute the script? DEFAULT_FORMULAS? It has only 2 prompt screens!

JP

[former_member225113]

Hi JP,

There is only one variable to be passed from Excel to DMP. DEFAULT_FORMULAS is used and modified to work with one prompt. You can see the advanced scripts below. Thanks!