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.