How to Trigger BW Process Chains from Analysis for Office
There are scenarios where there’s a need to trigger a process chain from an Analysis for Office. For example, you might need to allow a user to click a button in your Analysis for Office workbook which triggers a disaggregation of gross margin in the background based on percentages that a user have just entered. To enable this functionality, simply follow these steps:
1. Create a InfoObject of type Characteristic. Name the characteristic ZPCCHAIN and configure it to be of type CHAR with length 25 (the maximum length of process chain technical IDs).
2. Create a real-time InfoCube named YCX_R01 that contains characteristic ZPCCHAIN and a key figure of your choice. The key figure is not really used to store any real values. We simply need it to be there in order to be able to build a MultiProvider on top of our cube (a MultiProvider needs at least one key figure). For this how-to blog, I’ve chosen to use 0AMOUNT. I’ve set the description of 0AMOUNT in my InfoCube to be “Dummy/unused Key Figure” to imply that it does not serve any function but to enable as to build a MultiProvider on top of it.
3. Create a MultiProvider named YCX_M01 on top of your InfoCube and map InfoObject ZPCCHAIN and your chosem key figure. Note that the MultiProvider is not mandatory (i.e. you can build an aggregation level directly on top of InfoCube YCX_C01) but I recommend creating one to follow best practice.
4. Create an aggregation level named YCX_A01 on top of MultiProvider YCX_M01 (or directly on top of InfoCube YCX_C01 if you’ve chosen to skip step #3). Ensure that you use InfoObject ZPCCHAIN and your chosen key figure to be part of your aggregation level.
5. Enable function model RSPC_API_CHAIN_START to be called in a FOX formula by creating an entry in table RSPLF_DIR.
6. Create a BeX query variable named ZCUP_PCCHAIN_001 with the following configuration:
7. Create a BeX query filter named YCX_A01_FI001 with the following configuration:
8. Create a planning function named YCX_A01_PF01 of type FOX formula with following characteristics configuration and FOX code:
9. Create a planning sequence named YCX_A01_PS001 that makes use of planning function YCX_A01_PF001 and filter YCX_A01_FI001.
10. In InfoCube YCX_C01, add an entry for the process chain that needs to be triggered. You do can this using transaction RSINPUT or through a data load. Ensure that your dummy key figure is non-zero.
11. In an Analysis for Excel workbook that already contains an input ready query, insert planning sequence YCX_A01_PS001 in the components tab. In this example, we will leave the alias to PS_1.
12. In your workbook, specify parameters for planning sequence YCX_A01_PS001. You need to either hardcode a value for variable ZCUP_PCCHAIN_001 or specify the value using a cell reference. Ensure that the value your specify as the parameter is contained in InfoCube YCX_R01 (see step 10)
13. Execute the planning sequence when a button is pressed by linking the button into a VBA subroutine with the following code. Note that if you change the name of your planning sequence to other than PS_1, you should use that name instead.
Dim lResult As Long
That’t all to it! When you click the button, your planning sequence should now execute and the relevant process chain should be triggered.
Great. Thanks Emmanuel.
great stuff! thanks
Thanks Emmanuel for a nice Detailed step by step Documentation.
I just implemented it and works. However, after I click on the button to execute the process chain, it asks for the chains priority list (A,B,C) which is very annoying. Is there a way to skip this.
The workaround will be to set SY-BATCH = 'X' before calling function module RSPC_API_CHAIN_START. The API will think that it's being executed in the background and hence, will not prompt for the chain priority.
Very nice blog , Just I am not able to set the priority by default , used I synchronous to X which gets the process chain to error , and SY-BATCH = 'X' where to define as FOX doesn't take this statement.
Are you aware of problems with the implementation with AFO 2.4? We would like to introduce the release.
Just to mention that if you find out that your process chain is being triggered many times, we had this issue, and discovered that the planning function is executed in fact for each records. Even trying to manage this in a Z table by checking status of previous job running was not working, that just goes too fast... So a workaround for us was to put a filter on the planning filter linked to the planning sequence to select only one record, that you are sure is already there. Not perfect but does the job.
I recommend against following this blog. Per Christian's note above, unless you can 100% guarantee that you only have one record per filter, you will eventually find yourself triggering chains multiple times. This is because the Function Module will be called once for each "data Block" the planning sequence is run on.
I believe the proper way to handle this is via a custom Planning Function type. You can follow the blogs advice on creating the InfoObjects for Process Chain ID. Then, in your custom Function type, you simply read the variable to determine the process chain ID, then trigger the process chain in the exact same way Mr. Nepomuceno recommends.
I would like to point out that the table which holds the list of acceptable Function Modules to be used within a FOX Formula is RSPLF_FDIR not RSPLF_DIR. It was misspelled in this post, missing the second 'F'.
I would also suggest following JS Irick 's advice above. The FOX Formula framework is deliberately built to loop over multiple packets of records and apply the same logic to each.