Using SAP Business Planning and Consolidation (BPC), version for NetWeaver and an ABAP Business Add-In (BAdI), you can dynamically change the content of the script logic file before you run it. Therefore, you don’t have to maintain multiple similar static script logics. This reduces maintenance efforts.
Key Concept
A Data Manager package executes business logic on SAP Business Planning and Consolidation (BPC), version for NetWeaver data through a series of SAP BW process chains. One important Data Manager package, Default_Formulas, triggers script logic to accomplish the task. An advanced way to run script logic is to trigger it from an ABAP environment. Further, in ABAP Object Oriented (OO) for SAP BPC, you can retrieve script logic file content, make changes at run time, and trigger the script logic on this dynamically changed script logic file content.
SAP Business Planning and Consolidation (BPC), version for NetWeaver provides a standard process for creating or running Data Manager packages. When a business needs to run a large variety of variations of similar logic and uses the standard process, it has to create multiple script logic files, one for each logic, to accomplish the task. This requires the user to either enter different parameter values in a selection screen and execute a script logic file that takes the input parameter values, or choose one of the many script logic files to execute. Each file then has to be hard coded with preselected parameter values.
Either way it is not convenient for users or for BPC administrators or developers to maintain a large number of similar script logic files. Users, administrators, and developers have 30-plus script logic files to choose from.
Consider this business scenario:
- For business unit A1, you need to plan at the cost center group and product group levels.
- For business unit A2, you need to plan at the cost center group and profit center group levels.
- For business unit A3, you need to plan at the cost center group and profit center group levels; then, you need to make adjustments afterwards.
In a common, general solution, this would require three scripts:
Script 1: Input a parameter for business unit A1 and scope at the cost center group and product group levels
Script 2: Input a parameter for business unit A2, scope at the cost center group and product group levels, and total to the cost center group and profit center plan group levels
Script 3: Input a parameter for business unit A3, scope at the cost center group and profit center group plan levels, and run an adjustment
If you have 30 scenarios, that usually means 20 scripts, possibly 30 Data Manager packages (one for each script logic file), or one Data Manager package with complex instructions on which script logic file to select for each scenario.
I implemented an alternative solution that allows you to reuse just one script logic template file. To use this method, you need to:
- Maintain parameter mapping in a script logic template file
- Maintain logic in the same script logic template file
This solution is based on SAP BPC 10.X, version for NetWeaver. (For SAP BPC 7.5, version for NetWeaver the concept is the same, although the coding could be slightly different.)
The user just needs to choose one or a few key parameters from the Data Manager package. An ABAP program looks for mapping in the script logic template file to get the complete parameter mapping. It then dynamically changes the content of the script logic template file and executes it to accomplish the task, returning with a detail log. Here you could have one script logic template file to cover the three scenarios in the above example.
In this way, you need just one script logic template file, and mapping information is stored in the script logic template file as well. This significantly reduces the maintenance workload for BPC administrators and developers and makes it easier for users to do their jobs.
Note
This article is about how to dynamically get script logic, change the
content, and execute the script logic in a Business Add-In (BAdI) in
order to have one generic script logic file for multiple, similar
scenarios. Comparing the currency conversion result is beyond the scope
of this article.
How the Data Manager Package Works
Businesses usually have complex and different but similar scenarios in their processes. For example, users need to do the planning process each month, each quarter, and each fiscal year-end. They selectively deal with actual version, budget version, forecast version, revisions, currency conversion, and variance calculations. Depending on which period they enter, the process is similar.
However, the input parameter values for version (category), currency conversion scope, and data sources are different. BPC provides a standard process to create a Data Manager package. When executing a Data Manager package, it triggers a script logic file that, in turn, triggers an ABAP program to accomplish the task.
A user can run a Data Manager package from a Microsoft Excel interface with an Enterprise Performance Management (EPM) Add-in menu. This Data Manager package (Default_Formulas) triggers a script logic file. The script logic file communicates to the SAP back end to trigger a standard BPC ABAP program to accomplish the task. It returns log information to the Excel front end with result, warning, error, or success messages, and statistical information. This is the standard Data Manager package process in general.
Optionally the developer can invoke BAdI UJ_CUSTOM_LOGIC in the body of the script logic file to tell the system to execute some custom BAdI code for complex logic.
An Example Scenario
A use case: A user wants to run the similar or same logic with different scenarios. Let’s say you have a script logic that does currency conversion with the inputs of category, time, and entity (i.e., company code).
Figure 1 shows the script logic file.
*RUN_PROGRAM CURR_CONVERSION
CATEGORY = [ACTUAL]
CURRENCY = [USD]
TID_RA = [2011.02]
RATEENTITY = [GLOBAL]
OTHER = [ENTITY=1000]
*ENDRUN_PROGRAM
Figure 1
Script logic file for actual category currency conversion
The category, time, and entity are all hard coded. If the user wants to run a currency conversion for the category BUDGET and the time 2012.02 ENTITY 3000, the user has to write another script logic file as shown in
Figure 2.
*RUN_PROGRAM CURR_CONVERSION
CATEGORY = [BUDGET]
CURRENCY = [USD]
TID_RA = [2012.02]
RATEENTITY = [GLOBAL]
OTHER = [ENTITY=3000]
*ENDRUN_PROGRAM
Figure 2
Script logic file for budget category currency conversion
For this scenario of currency conversion with different category/entity/time, bring in the parameters in
Figure 3.
For this scenario of currency conversion with different category/entity/time, bring in the parameters in Figure 3.
*RUN_PROGRAM CURR_CONVERSION
CATEGORY = [%CATEGORY_SET%]
CURRENCY = [USD]
TID_RA = [%TIME_SET%]
RATEENTITY = [GLOBAL]
OTHER = [ENTITY=%ENTITY_SET%]
*ENDRUN_PROGRAM
Figure 3
Script logic file for currency conversion with parameters
The variables %CATEGORY_SET%, %TIME_SET%, and %ENTITY_SET% are given to you by a standard SAP program that carries user input values from Excel via a Data Manager package interface. Therefore, you can have one script logic file to cover different inputs of categories, time, and entities. There is another scenario in which the user wants to run a currency conversion for two categories and compare the difference. To compare the categories you can use the script logic in
Figure 4.
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_MEMBERSET ENTITY = %ENTITY_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET CURRENCY = USD
*WHEN TIME
*IS *
*REC(EXPRESSION = %VALUE% - [CATEGORY].[BUDGET], CATEGORY = CAT_DIFF)
*ENDWHEN
Figure 4
Script logic for difference between two categories
Due to the limitation of the dynamic script used in the Data Manager package, I cannot have two parameters for category. Secondly, category CAT_DIFF presents the difference between %CATEGORY_SET% and BUDGET. Thirdly, I will have currency conversion pieces for both categories before I run this compare-the-categories script.
There is another common requirement. There is usually a list of pairs of similar parameters for this type of comparison. For example, you always want to compare one category (From category) against another category (To category); for example, comparing ACTUAL to BUDGET, FORECAST Q1 to BUDGET, FORECAST Q2 to FORECAST Q1, or FORECAST Q3 to FORECAST Q2, as shown in
Table 1.
| From category |
To category |
| ACTUAL |
BUDGET |
| FORECAST Q1 |
BUDGET |
| FORECAST Q2 |
FORECAST Q1 |
| FORECAST Q3 |
FORECAST Q2 |
Table 1
Mapping category comparison
Let’s add one more dimension, TIME, to this mapping table as shown in
Table 2.
| From category
|
From time |
To category |
To time |
| ACTUAL |
CURRENTYEAR.Q1 |
BUDGET |
CURRENTYEAR.00 |
| ACTUAL |
CURRENTYEAR.Q2 |
BUDGET |
CURRENTYEAR.00 |
| FORECASTQ1 |
CURRENTYEAR.Q1 |
BUDGET |
CURRENTYEAR.00 |
| FORECASTQ2 |
CURRENTYEAR.Q2 |
FORECASTQ1 |
CURRENTYEARQ1 |
| BUDGET |
CURRENTYEAR.00 |
BUDGET |
LASTYEAR.00 |
Table 2
Mapping category and time comparison matrix
As you add more dimensions this mapping table can grow bigger and bigger, which is more than the Data Manager package selection screen and dynamic script can handle.
One way to resolve this is to hard code these combinations, one for each script logic, to create multiple script logic files. You could give them different file names, such as ACT_CYQ1_VS_BUD_CY00.LGF, then let the user choose the file name from the Data Manager package selection screen.
This can result in dozens of script logic files stored in the back end. Because they are similar but still different in content, if you need to make a change (let’s say profit center ELECTRONIC_CONTROL should be excluded from this logic), then you need to go to each one of the script logic files to make the change in scope.
There are two points to consider when determining a solution to improve this method:
- Can you maintain a mapping table to store these parameters and let the program read and pass these entries into one parameterized script logic file? This mapping table needs to be easy for the user (with administrative rights) to maintain. An ABAP table in the back end or an ABAP table with a Web Dynpro interface that a user can maintain would be a good idea.
- Can you conveniently pass the parameters to the one parameterized script logic file and conveniently run it? Normally, the path is from script logic to the ABAP BAdI one way. Once an ABAP program reads the entries from the mapping table, it needs to invoke script logic (e.g., you need to do script logic from an ABAP BAdI, two-way traffic).
A Solution
The solution is to provide one integrated script logic and have the ABAP BAdI support this one integrated script logic to handle all the scenarios. To do so, follow the steps below.
First you run script logic from an ABAP BAdI via function module UJK_SCRIPT_LOGIC_EXECUTE.
Secondly, you modify the content of script after you get the script logic file in the ABAP environment by calling CL_UJK_DISPATCH->get_file( ).
Thirdly, with a parameter in the script logic file, (ACTION = ????) you can choose what to do with the script logic file: Either store the mapping entries into
Table 1 or execute the script logic in the ABAP BAdI. For example, you can have a script logic file as shown in
Figure 5.
*START_BADI ZBPC_VERSION_COMPARE
FROM_CATEGORY = FROM_TIME = TO_CATEGORY = TO_TIME
MAPPING = ACTUA L: CURRENTYEAR.Q1:BUDGET:CURRENTYEAR.00
MAPPING = ACTUAL: CURRENTYEAR.Q2:BUDGET:CURRENTYEAR.00
ACTION = STORE_MAPPING
*END_BADI
Figure 5
Build mapping based on ACTION = STORE_MAPPING
In the BAdI, when you see parameter ACTION = STORE_MAPPING you split the following strings shown in
Figure 6 into fields by =, and store them in an internal mapping table.
ACTUAL=CURRENTYEAR.Q1=BUDGET=CURRENTYEAR.00
ACTUAL=CURRENTYEAR.Q2=BUDGET.CURRENTYEAR.00
Figure 6
The strings to be brought into the mapping table
Then you change the script to the script shown in
Figure 7.
*START_BADI ZBPC_VERSION_COMPARE
FROM_CATEGORY = FROM_TIME = TO_CATEGORY = TO_TIME
ACTUAL=CURRENTYEAR.Q1=BUDGET=CURRENTYEAR.00
ACTUAL=CURRENTYEAR.Q2=BUDGET.CURRENTYEAR.00
ACTION = EXECUTE
*END_BADI
Figure 7
Execute the logic based on ACTION = EXECUTE
In the BAdI, when you see parameter ACTION = EXECUTE, you execute the logic.
Figure 8 shows the process.
Figure 8
Program flow for parameterized generic script
From this diagram let’s see in detail how to use one script logic file to accomplish this task.
The Process
The process to follow is below.
Step 1. Execute the Data Manager package from a Microsoft Excel EPM interface, RUN PACKAGE. This is a standard step in BPC. Before running this Data Manager package, you have to have the script logic file set up, and you have to have a Data Manager package set up so that the Data Manager is linked to the script logic file.
Step 2. The Data Manager package executes the script logic file, which is standard functionality in BPC.
Step 3. The script logic triggers the ABAP BAdI as a standard function. The BAdI that is triggered is called ZBPC_VERSION_COMPARE. The only thing you need to be aware of here is that now the parameter ACTION is set to STORE_MAPPING.
Step 4. Initialize the mapping table with parameter ACTION = STORE_MAPPING in the BAdI in
Figure 5.
I first created a local data type ts_mapping and tt_mapping in the class ZCL_BPC_VERSION_COMPARE, as shown in
Figure 9.
Types: begin of ts_mapping,
from_category type uj_dim_member,
from_time type uj_dim_member,
to_category type uj_dim_member,
to_time type uj_dim_member,
end of ts_mapping,
tt_mapping type standard table of ts_mapping.
Figure 9
Type declaration of the BAdI
I then created a class attribute DT_MAPPING in the instance/private section as shown in
Figure 10. This attribute is of data type TT_MAPPING which I created in
Figure 9. Therefore, I can populate the content of
Table 2 into data dt_mapping, which has structure type tt_mapping.
Dt_mapping type tt_mapping.
Figure 10
Data declaration on a class attribute
In the body of the class method if_uj_custom_logic~execute, I have code as shown in
Figure 11 to store the mapping.
Data: ls_mapping type ts_mapping,
ls_param TYPE ujk_s_script_logic_hashentry.
Read table it_param into ls_param with table key hashkey = ‘ACTION’.
If ls_param-hashvalue = ‘STORE_MAPPING’.
*STORE_MAPPING. (in Figure12 below)
*EXECUTE A CHANGED SCRIPT LOGIC FILE(in figure 13 below)
RETURN.
elseIf ls_param-hashvalue = ‘EXECUTE.
*EXECUTE
ELSE.
Return.
Endif.
Figure 11
Pseudo-code for the BAdI program flow
Here let’s see how you implement each of the following two substeps. Take the following actions.
Step 4.1. *STORE_MAPPING (using the code in
Figure 12).
Data: lt_mbr type ujk_t_dim_member,
L_mbr type uj_dim_member.
Field-symbols: <l_fld> type any.
Loop at it_param into ls_param where hashkey = ‘MAPPING’.
Clear: lt_mbr, l_mbr.
Split ls_param-hashvalue at ‘:’ into table lt_mbr.
Loop at lt_mbr into l_mbr.
Assign component sy-tabix of structure ls_mapping to <l_fld>.
<l_fld> = l_mbr.
Endloop.
Append ls_mapping to dt_mapping.
Endloop.
Figure 12
The code for pseudo-code *STORE_MAPPING
Step 4.2. *EXECUTE A CHANGED SCRIPT LOGIC FILE. You get the script logic file, change the content, and then execute the script logic file. The code is as shown in
Figure 13.
Data; lo_context type ref to if_uj_context,
lo_user type ref to cl_uje_user,
l_filename type uj_docname,
lt_lgx type ujk_t_script_logic_scripttable,
l_user type uj_user_id,
ls_lgx like line of lt_lgx.
lo_context = cl_uj_context=>get_cur_context( ).
call method lo_context->get_user_obj
receiving
ro_user = lo_user.
l_user = lo_user->d_obj_id.
create object lo_dispatch.
concatenate 'ROOTWEBFOLDERS' 'ENVIRONMENT_SHELL' 'ADMINAPP' 'PLANNING' '' i_file into l_filename.
try.
call method lo_dispatch->get_file
exporting
i_appset = ‘ENVORNMENT_SHELL’ “ENVIRONMENT ID
i_application = ‘PLANNING’ “APPLICATION ID
i_user = l_user
i_filename = l_filename
importing
et_lgx = lt_lgx.
catch cx_uj_static_check .
endtry.
Figure 13
Code for pseudo-code *EXECUTE A CHANGED SCRIPT LOGIC FILE
Change the content of the file that is stored in table lt_lgx, as shown in
Figure 14.
Read table lt_lgx into ls_lgx with table key hashkey = ‘ACTION’.
If sy-subrc = 0 .
Ls_lgx-hashvalue = ‘EXECUTE’.
Modify lt_lgx from ls_lgx.
Endif.
Table 14
Switch the file to EXECUTE mode by changing the file content
Execute the same script logic file, but with the changed content in
Figure 15.
try.
call function 'UJK_SCRIPT_LOGIC_EXECUTE'
exporting
i_appset = ‘ENVIRONMENT_SHELL’
i_application = ‘PLANNING’
i_user = l_user
i_logic = et_lgx
* I_FILE_TYPE = 'LGX'
* IT_CV =
i_module = cl_ujk_model=>g_module_id
i_lgf = l_filename
i_mode = ujk0_cs_run_mode-execute
* IS_BADI_PARAM =
importing
* ET_LGX = et_lgx
e_fm_error_message = l_error_message
* ET_LOG =
* E_SP_WARNING =
.
catch cx_uj_static_check.
Endtry.
Figure 15
Execute the changed script logic in execute mode
This execution will come to the second branch of the main program. I am highlighting this branch in
Figure 16, which leads to step 5.
Read table it_param into ls_param with table key hashkey = ‘ACTION’.
If ls_param-hashvalue = ‘STORE_MAPPING’.
*STORE_MAPPING.
*EXECUTE A CHANGED SCRIPT LOGIC FILE
RETURN.
elseIf ls_param-hashvalue = ‘EXECUTE.
*EXECUTE
ELSE.
Return.
Endif.
Figure 16
Sketch on how to determine mapping versus executing in the ABAP BAdI
Step 5. Execute the script logic. This step is a standard function module in SAP BPC triggered by call function 'UJK_SCRIPT_LOGIC_EXECUTE', as shown in
Figure 15.
Step 6. Trigger the ABAP BAdI. This step is a standard step in the SAP system. Function module 'UJK_SCRIPT_LOGIC_EXECUTE' triggers your BAdI if you have it set up beforehand. Step 6 is same as step 3 in terms of functionality; however, this time it carries the changed content of the script logic file.
Step 7. Execute the logic. This is the highlighted spot in the main program.
The logic is to execute currency conversion for version ACTUAL for period T1, then to execute currency conversion for version BUDGET for period T0, for example. You compare the results to get the difference to save to the BPC model.
Similarly you can have one generic currency script and change the content before you actually run the currency conversion. Therefore, you don’t have to store two similar but different script logic files for currency conversion.
To be even more aggressive, you can merge the generic currency conversion script logic with the main script logic into one generic script logic file to handle three situations—currency conversion, store mapping, and execution.
When you start to execute currency conversion, have your mapping table DT_MAPPING handy to run currency conversion. A typical currency conversion script logic file is shown in
Figure 17.
*RUN_PROGRAM CURR_CONVERSION
CATEGORY = ACTUAL
GROUPS = [USD]
TID_RA = 2015.FEB
RATEENTITY = Global
OTHER =
*ENDRUN_PROGRAM
Figure 17
Typical currency conversion script logic
Here you just need to replace the content of the script logic with the mapping data you stored in DT_MAPPING, which is similar to what you did in step 4.2, then execute the changed script logic.
George Chen
George Chen is the owner of Lynk, Inc. An SAP consultant, he has worked on SAP Netweaver ABAP/HANA/BPC/BW/IP/TPM/CRM for the past 17 years. He specializes in process integration, performance tuning, and introducing advanced ABAP design patterns in development. He has worked with SAP America many times to conduct ABAP performance tuning and troubleshooting. His focuses are ABAP for HANA and ABAP OO in SAP NetWeaver.
You may contact the author at
george.chen@lynksapconsulting.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the
editor.
