Skip to Content
0

Issue useing VBA to call data manager package in BPC 10

Jan 16, 2017 at 02:58 AM

246

avatar image

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

10 |10000 characters needed characters left characters exceeded

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

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Vadim Kalinin Jan 16, 2017 at 08:18 AM
0

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.

Show 7 Share
10 |10000 characters needed characters left characters exceeded

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

0

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.

0

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} http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd=" <_ap> %SELECTION% <_apc> Category BRSFCST10_09G http://www.w3.org/2001/XMLSchema"> <_ap> %SELECTION% <_apc> Category BRSFCST10_09G >

0

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!

0

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

0

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?

0

Unfortunately no way...

0
Benjamin Tan Jan 16, 2017 at 07:26 AM
0

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.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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

0

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!

0
Benjamin Tan Jan 16, 2017 at 12:18 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded