BPC Planning for S/4HANA – FOX formulas – Part 3
You would like to learn how to program BPC formula planning functions which require coding in FOX (formula extension language).
I started writing a blog on coding FOX formulas and happened to be working on a BPC Optimized for S/4HANA system. My intention was just to write a single blog but found it developing into a few parts due to the length and the fact that I wanted to build the system from scratch, including the entire data model.
So this is PART 3 of a 4-part blog with the following structure:
PART 1 – Setting up the data model, creating an Analysis for Office workbook, capturing and saving some seeding data to the data model. (No fox code yet)
PART 2 – Write a “Hello World” equivalent fox formula and execute it from a button placed in the Analysis for Office workbook.
PART 3 – Tools for debugging functions, variables, constants, loops, If-statements, messages.
PART 4 – Capturing data directly into the cube without using Analysis for Office, “DO” loops, “TMVL()” expression – time value offset calculations, “ATRV()” expression – using attribute values of a dimension member, a complete look at what it takes to calculate production quantities from sales demand and working across 2 cubes, periodic escalations of prices and quantities, overhead costs and cost of sales price calculations, sales price calculation based on a markup percentage, sales income calculation and cost of sales calculations.
FOX coding objectives
If you’ve followed part 1 and 2 the status of the data is as follows:
We have captured 3 finished products (PRD01, PRD02, PRD03) with key figure QUANTITY captured against period 1 for 3 different profit centres (PC01, PC02, PC03) all in one sales GL account and the data is all stored in the ZREV01 REVENUE cube of our composite provider. We have used fox code to write the captured QUANTITY values to the key figure NUMBER and manipulated the values slightly using some simple maths equations into periods 1, 2 and 3.
Now we will write a second fox formula function to do the following:
- Loop over the profit centres, count how many have sales quantities and determine the aggregate sales quantity
- We will use loops, variables, constants, messages, if-statement, data types
- Demonstrate how to debug functions
A word on debugging functions
It is useful when writing FOX formula functions to know what tools are available for debugging and we might not go as far as calling the editor for FOX an Integrated Development Environment (IDE) as it’s more like a text editor on steroids but there are some very useful things we can do to find bugs in our code.
- Firstly we can use the MESSAGE statement to create in-code debug watch points. The MESSAGE statement is used to send developer messages to the user, those that you frequently see in the status line at the bottom of the screen in SAP programs, but we can use them to debug our code.
- Secondly the planning sequence allows us to execute each function within the RSPLAN transaction code so we don’t have to go to the user front-end to execute it. We can execute the function with a TRACE mode which saves a copy of the original data set returned by the aggregation level + filter combination for processing by the function, any reference data (explain later) used to process the function and the resulting records created or modified by the function. After execution you can decide to save or dump the results as they are first stored in a temporary buffer area so you don’t clutter the cube until you are satisfied with the results.
- Thirdly you can capture input data directly in RSPLAN without using an input-ready Analysis for Office template which is extremely useful for changing values quickly and easily which might be inputs to a function to test the effect on the output.
- After using the above it is also possible to set break-points in the code and use the ABAP workbench to debug the FOX script code line for line. Here is a blog by Gerd Schoeffl on how to do this.
Create a new formula function
We will use the same aggregation level and filter that we created in part 2.
Create a new planning function based on the aggregation level we already created from transaction code RSPLAN in a SAP GUI either run from Eclipse, NAVIGATE, SAP GUI or from a SAP LOGON PAD GUI logon.
|…………………………………………………………………..||Select all the characteristics except 0FISVARN, 0FISCYEAR and ZVERSION as FIELDS TO BE CHANGED. We will not work with conditions yet. Note that you can toggle an incorrect selection just by clicking the selection again. Now select the PARAMETER button to proceed to the coding screen and remember the INFO button has all the reference information you need to know to code anything in FOX.|
Create a loop around the data set and debug it
The first thing you’ll notice when the editor opens is that the OPERAND DISPLAY is longer than in our first function. The OPERAND DISPLAY is very important because our characteristics MUST be written in this order which is why the pop-up screen is so useful for automatically completing the syntax than writing it manually. So pressing the OPERANDS button displays the longer selection list in the pop-up screen as per the characteristics we selected in the header area.
Let’s loop through the PROFIT CENTER and calculate the aggregate sales quantity and also add some debugging watch points. If we create a variable for the PROFIT CENTER and set a watch on it we will find that we can pin all the other characteristic values except for ITEM which changes as the PROFIT CENTER changes. So we actually need to create two variables, one for the PROFIT CENTER and one for the ITEM.
So make the selection as per the screen-shot below and leave the PROFIT CENTER and ITEM blank and accept.
The first argument of the function is inserted with the last two characteristics left out. We will replace those with variables and to do so we need two variable of the same TYPE as the characteristics we are trying to replace. So if you select the DATA TYPES button you will see that in addition to some generic types which are always available to any function such as INTEGER, DATE and STRING we also have all the characteristics and their attributes listed as available types to this specific function.
Drop down on the INFO button to obtain the help on any new reserved words you want to use and you find the technical details on DATA DECLARATIONS. The format is:
DATA <User_Variable> TYPE <BW_Characteristic_ID>.
I decided to use V_PC and V_ITEM as my variable names but you can use any text here that conforms to the text rules. Note that these variables are local to your function, are un-initialized and have no association to normal BW variables. These are FOX code-, function specific variables. (Also note I always use the DATA TYPES button to select the characteristic types and insert the syntax to avoid typos and it’s much easier than trying to remember the exact BW ID name)
You can now use these variables in your argument and replace the remaining two operands with your variable names.
Now in order to populate the variables with values from our cube we use a FOREACH statement and list all the variables you want populated. The FOREACH statement will read the current data in the cube for the characteristics represented by your list of variables based on your aggregation level + filter, sort them in alphabetical order in the COMBINATION AND ORDER you listed them after the FOREACH statement and return them to the loop one combination set at a time until there are no more combinations. In other words “V_PC, V_ITEM” returns a different sort order combination than listing them “V_ITEM, V_PC” which is important in certain scenarios.
Any records you generate inside the FOREACH loop which adds new combinations will be available immediately after you exit the current loop.
The FOREACH combination list is generated at runtime and the FOREACH statements can be nested and as always use the INFO button to get the low down on each reserved word.
So wrap the current function argument, which returns the current sales quantity, in the FOREACH statement and ENDFOR and you can see how the two variables will now be populated.
Let’s aggregate the sales QUANTITY into an internal variable and count the number of times the loop is executed. I’ve added a floating point variable V_TOTSALESQTY to hold the aggregated QUANTITY and an integer variable V_COUNT to count the loop iterations. Initialise each of them to zero before the loop and add them to the loop as depicted. Now press the CHECK button to ensure you don’t have any syntax errors and then SAVE your function.
The function is ready to be executed but there is no output. We are not using our counter or summation total anywhere so we can’t “see” anything. So let’s add some debugging code so we can visualise some results.
To do this we will use the MESSAGE statement which has the following syntax:
MESSAGE Innn(class) WITH <operand 1>, <operand 2>, <operand 3>, <operand 4>. where nnn = message number
Our approach will be to declare a CONSTANT which will control whether our internal debugging messages are set to display ON or OFF and in the interests of exploring all the data types I’ve used a STRING for this one and you can obviously set the value to any text string value. Often I just use an integer with a zero or one. There is no technical difference between a constant and a variable in FOX. It is just the manner in which you use it.
Now we need to execute this function. Create a new planning sequence with ID = ZSEQ02 and description and add ZFUNC02 to it using the same aggregation level ZPAL01 and filter ZFIL01 as in sequence 1. Then SELECT ROW 1 and press the EXECUTE STEP WITH TRACE button.
The function executes within RSPLAN without the need to use the Analysis for Office front end. It opens on the TRACE tab showing that 1 block of data was received from the cube with 9 records before the function was executed and 9 records were in the result table which means there were no new records added and in the message area we see that no records were modified either.
Our “debug” messages are displayed in no particular order and we can see that our counter went from 1 to 2 to 3 with the corresponding PROFIT CENTER and ITEM CODES and the aggregate variable is calculated correctly i.e. 100, 100 + 80 = 180, 180 + 150 = 330.
Now if you select ROW 1 for BLOCK 1 and press the DISPLAY CONTENTS button you will see the actual data processed by the function and the results it produced. Why were 9 records processed when we only had 3 PROFIT CENTERS?
The answer is shown in the data. There are two key figures in each row of our returned AGGREGATION LEVEL + FILTER dataset i.e. QUANTITY and NUMBER and in each record there is data for periods 1, 2 and 3 for the 3 PROFIT CENTER / ITEM rows i.e. 3 x 3 = 9. Both key figures belong to one record so the split is based on periods and profit centres.
You can double check this by going to RSA1, goto MODELING, find InfoProvider ZREV01, right-click and DISPLAY DATA. Just bear in mind the FOX record count is the aggregation level record count and not necessarily always a 1-to-1 match with the cube. If the cube has audit records, for example, which split a row into multiple records these will be aggregated in the FOX rows depending on how the aggregation level is modelled…but assuming the technical dimension is excluded from the level.