Script logic – Passing values from Excel to Script logic
Business Scenario:
Execute script logic from a Data Manager (DM) Package passing parameters for:
1. Text substitution
2. Dimension selection
Purpose:
To document my own findings and experience on this topic which might assist you to be more effective in finding your own solution. Perhaps it might also invoke those more technical to add to the discussion and close the knowledge gaps we as functional consultants struggle with in the field.
Reference (Data Manager help documentation):
http://help.sap.com/saphelp_bpc70/helpdata/en/af/36a94e9af0436c959b16baabb1a248/frameset.htm
Overview of DM package and interface:
1. When setting up the DM package the interface to BW is ALWAYS by means of a BW process chain
2. The script language in the DM package is being executed on the client machine in the front-end while the script logic (.lgf) is being executed as part of the process chain on the BW server in the backend. These are two separate concepts with two separate syntaxes.
3. The DM script language reserved words are fairly limited and can be viewed as the parameter list which is displayed when you press “MODIFY SCRIPT”
4. The values from this part of the DM script are processed in the process chain under “MODIFY DYNAMICALLY” and bearing in mind that the parameters may not be relevant depending on the content of the process chain. In this discussion we are focussing on the /CPMB/IC_DATA process chain for executing script logic. Effectively providing a user interface to UJKT.
5. When we double-click the process chain variant, “RUN LOGIC” in this case, we find the list of fields which can be populated and recognise some as standard values set by the DM “MODIFY SCRIPT” language. This list would differ between process chains and some values may not be mandatory.
6. If we revert our attention back to the DM script language then we see that setting these values requires the use of the TASK function. The script is by default populated with the constant variables e.g. %USER% (refer to help documentation for complete list)
PROMPT(SELECTINPUT,,,,”%ENTITY_DIM%,%CATEGORY_DIM%,%CURRENCY_DIM%,%TIME_DIM%”)
PROMPT(TEXT,%PROMPT_VAR1%, “Text input”, , ,)
INFO(%EQU%,”=”)
INFO(%TAB%,;)
TASK(/CPMB/ICDATA_LOGIC,SUSER,%USER%)
TASK(/CPMB/ICDATA_LOGIC,SAPPSET,%APPSET%)
TASK(/CPMB/ICDATA_LOGIC,SAPP,%APP%)
TASK(/CPMB/ICDATA_LOGIC,SELECTION,%SELECTION%)
TASK(/CPMB/ICDATA_LOGIC,REPLACEPARAM,VAR1%EQU%%PROMPT_VAR1%)
TASK(/CPMB/ICDATA_LOGIC,LOGICFILENAME,BPC_LOGIC.LGF)
7. SELECTION
The standard syntax for prompting a user for dimension input is: PROMPT(SELECTINPUT, [variable], [second variable], [label], [dimensions])
The default prompt for user input of the standard dimensions (E/C/R/T) is: PROMPT(SELECTINPUT,,,,”%ENTITY_DIM%,%CATEGORY_DIM%,%CURRENCY_DIM%,%TIME_DIM%”). Firstly notice the absence of the second parameter for [variable] but if you refer to the help doc. you will see that when omitted the default value of %SELECTION% is used.
You could therefore override or add to the prompt your own dimension(s), add a personal variable and set the SELECTION property e.g.
PROMPT(SELECTINPUT,%MYVAR%,,”Time”,”TIME”) and TASK(/CPMB/ICDATA_LOGIC,SELECTION,%MYVAR%)
Also note that the use of the “%” for denoting variables is optional and purely used out of convention for readability.
The net effect of these two lines of syntax is to dynamically set what you would otherwise have hard-coded in script logic as an *XDIM_MEMBERSET function. For completeness sake you would therefore EXCLUDE any dimensions from *XDIM_MEMBERSET scoping that you have passed using the SELECTION property.
Also note that it is not possible to “hand-shake” the selection set created by your user input dialog prompt variable (DM variable) and your script logic file variables. The DM variable contains multiple dimensions each with multiple selections so your script logic has no ability to “unravel” these sets into usable script logic variables. Rather think of the SELECTION property as a *XDIM_MEMBERSET statement sent from DM. Managing individual values must be done using *WHEN functions or other.
You can check your scoping via the SELECTION property in two ways after the script logic has been executed and you review the log file (DM View status):
a) The Package log will confirm the values of the user inputs against the DM variable.
b) The selection part of the log file will show you the resolved result of the exact full scope i.e. the combination of the user selections passed by SELECTION and the *XDIM scope.
8. REPLACEPARAM
REPLACEPARAM is the property used to pass a TEXT string from DM to script logic whether this is collected via a dialog prompt such as PROMPT(TEXT… or not. Receiving this value into the script logic file requires the same parameter value enclosed in “$” as we are fairly familiar with.
9. Conclusion
Having this basic understanding of how the interface works should better assist you in finding functional solutions for typical interface issues.
Typical functional requirements may be to:
a) Use the current context selection to drive script logic. (Context / Current view available in BADi but not in DM to pass as a parameter)
b) Limit the dialog box selections for end users (Use security settings)
c) Have the REPLACEPARAM functionality for a single selection but based on a dimension selection. (not possible)
d) Assign dimension selection to text variable in DM. (Not possible)
Hi Noel,
I can see some issues with the following:
"Also note that it is not possible to "hand-shake" the selection set created by your user input dialog prompt variable (DM variable) and your script logic file variables. The DM variable contains multiple dimensions each with multiple selections so your script logic has no ability to "unravel" these sets into usable script logic variables. Rather think of the SELECTION property as a *XDIM_MEMBERSET statement sent from DM. Managing individual values must be done using *WHEN functions or other."
The result of SELECTION DM variable passed to the script will be a number of single dimension variables like %DIMNAME_SET%. The %DIMNAME_SET% variables can be used in the script and can be combined with additional members (in *XDIM_MEMBERSET, *SELECT, etc.)
Also, using single dimension in SELECTINPUT you can assign the resulted members to $$ variable using MEMBERSELECTION instead of REPLACEPARAM.
B.R. Vadim
Hi Vadim,
Yes agreed. Thanks for expanding on that. %DIMNAME_SET% can be used in the script logic to identify any dimensions memberset being processed regardless of how it was selected and can be used "as is" or transferred to other variables as you have mentioned thereby performing the "hand-shaking" effect we are looking for.
Regards,
Noel.
Hi Noel,
the title is a bit misleading - as here parameters are not passed from Excel but from DM dialog.
I am searching for a solution how to pass parameters from EPM Excel, i.e. rows with employees displayed and by positioning on certain row parameters (employee No) are taken from that row. Any idea about that?
Regards
Ivan
Hi Ivan,
Please read here: How to deal with BPC Data Manager packages programmatically
Vadim
Hi Ivan,
The contraints we are working with here for passing a parameter directly from the content of excel is going to be:
1. Default logic is executed with the scope set to changed records
2. DM script logic is run via a DM package
3. The DM package interface is controlled by the available reserved word prompts
So executing logic using data found in excel cells as parameter values is going to mean one of two things:
1. Using the BPC API interface with VB code as explained in the link provided by Vadim to set the prompt values programatically and then executing the DM package.
2. Flagging the selected row with a change in one of it's values so that default logic can pick it up as a changed record and then process it.
Perhaps this is a development idea for the programmers to allow excel content like the context menu and excel cells to be referenced as prompt values for DM packages.
Regards,
Noel.
Hi Noel,
The default values for SELECTINPUT are available starting from some SP.
PROMPT(SELECTINPUT, [variable], , [label], [dimensions],
[default members])
The members that are selected by default
Enter 0 for "All"
Enter 1 to take into account the context member
Vadim
Hi Vadim,
I think the requirement here is to set the value automatically from excel so there is no PROMPT required at run-time.
EXAMPLE:
User selects Category 1 in cell A1 and Category 2 in cell A2. Excel combines these in cell A3 using a formula =A1&"."&A2 creating a new input Category which is a combination of both. Now when the DM package is run we want to use the formula result in A3 as the DM paramater with a DM parameter override formula which might look something like this:
=EPMDMPromptOverride([variable],[dimension],[worksheet!cell])
=EPMDMPromptOverride(,%CATEGORY_DIM%,A3)
Noel.
Hi Noel,
They implemented only partial solution that can be used in some cases only. Example:
You have some context in the input schedule. Ex: CATEGORY: ACTUAL
You have DM advanced script containing the line:
PROMPT(SELECTINPUT,%CAT%,,"Select Category:","CATEGORY",1)
When the user will launch the DM package the value in this prompt will be filled from context - ACTUAL. User can change it.
Vadim
Hi Vadim,
I tried your statement with TIME dimension:
(SELECTINPUT,%TIME%,,"Select Time:","TIME",1)
...but it does not have any impact. Still I get prompt for <all> instead of taking the value from context.
Is there any other tricks to do? I am on SP17 of EPM Add-in
Regards
Ivan
Hi Ivan,
There is a small mistake in the documentation - additional comma have to be added before 1:
PROMPT(SELECTINPUT,%TIME%,,"Select Time:","TIME",,1)
B.R. Vadim
It works!
Thank you. Ivan
Hi Ivan,
There is a How-To paper which explain how you can substitute the PROMPT values in the DM package with values directly from the Excel cells without the use of manipulating the temporary file. This means the DM PROMPT statement does not pop-up a selection screen for the user but reads the value directly from an Excel cell.
It is found at http://scn.sap.com/docs/DOC-32636 and is called "How to Call BPC Data Manager Package from Visual Basic". It is authored by Tristan Colgate, Amaury Gruyelle, Muthu Ranganathan
Regards,
Noel.
Please open a new discussion and explain your requirements with some screenshots!
It's not possible to answer here!
Vadim
You have to create a discussion, not a blog!
Hi... sorry didn't realise. I created the discussion. In the interim I have included it here also. Can you advise please?
Hi,
Basically I have rows of data and when saved, the default script must be able to import values from cells in the rows of the Excel Sheet and run accordingly. Firstly the "PROMPT" below still presents me with <ALL>
PROMPT(SELECTINPUT,%ACCOUNT%,,"Select ACCOUNT:","ACCOUNT",,1)
In essence its actually an allocation script that is called, however depending upon what the user selects in another cell it can perform either Average or a Weighed spread ( LYS ); dynamically calling the appropriate allocation script.
See below... When saved the called default script should receive two sets of data and perform tow separate allocations
A_1010 Perform an Average Allocation
A_2020 Perform an LYS Allocation
Think I'm close... but no cigar