Hello! It’s great to see you again. Welcome back to Workbook Calculation Adapter Blog Posts Series. One example down and three to go. In the second blog post “Workbook Calculation (with Additional Created Field)”, we talked about configuring a Workbook Calculation function with an additional Field which was not present from the Input table. After being enlightened of the process, we were left asking ourselves about Parameters and how can it be included in configuring Workbook Calculation. In this third blog post, we’ll talk about the second example and I trust that you’ll find this interesting.
This scenario shows how Workbook Calculation processes the data from the Input Model Table and allows an additional field to be included in the result calculated through a formula with parameters.
Parameters
Create a parameter based on the below information.
Key Figure Parameter |
General |
Field |
Description |
Scale |
Type |
Data length |
Data Decimals |
Unit Field |
PKF_DISC |
Discount Parameter |
1 |
DEC |
18 |
2 |
|
For more information on how to create environment fields or parameters, see this link.
Input
This is the table that will be used as an input function:
Input Model Table
Customer |
Product |
Quantity |
Amount |
CUST01 |
PROD01 |
1 |
10 |
CUST02 |
PROD02 |
2 |
20 |
CUST03 |
PROD03 |
3 |
30 |
CUST04 |
PROD04 |
4 |
40 |
CUST05 |
PROD05 |
5 |
50 |
CUST05 |
PROD0X |
50 |
500 |
CUST06 |
PROD06 |
60 |
600 |
In the Signature tab, we maintain the following:
Signature |
Granularity |
Selection |
Action |
Customer |
Customer |
Total Amount |
Product |
Product |
Total Discounted Amount |
|
Quantity |
|
|
Amount |
|
By maintaining the above fields, we are:
- Putting the fields Customer and Product in the Granularity fields as they contain unique data records. Thereby we are making sure that the system processes data records from the input data in a grouped manner and that it considers all records from the input function for processing.
- Making sure that the fields Customer, Product, Quantity and Amount (which can also be seen in our Input Mode Table) will be included in the result table.
- Adding other fields (make sure that the fields are created in the environment, otherwise an error will be displayed). We use it to create a formula which will eventually be included in the final result.
In the Calculation Rules (worksheet), we see three tabs (Parameters, Input and Result). The Input and Result tabs contain the fields maintained in the Signature section.
Customer |
Product |
Quantity |
Amount |
Total Amount |
Total Discounted Amount |
|
|
0 |
0 |
0 |
0 |
Both Input and Result tab contain the table above. The tabs do not show the data records from the input table. We recommend adding dummy data in the Excel tabs to test the Excel formula logic. These data are only used for modeling purposes and are not considered in the calculation during system runtime.
The Parameter tab will show the below table. You can manually enter dummy data as input in the Parameter field so that you have a feel of how the formula to be created in the Result tab works.
Parameter |
Description |
Value |
PKF_DISC |
Discount Parameter |
0 |
Dummy Data provided below can be maintained.
Input tab
Customer |
Product |
Quantity |
Amount |
Total Amount |
Total Discounted Amount |
ARR21 |
SHOES |
143 |
300 |
0 |
0 |
Note
Even though dummy data is maintained in the Input tab, it is still capturing the data from the Input Model table.
The formulas shown below will be used for the Result tab.
Result tab
Customer |
Product |
Quantity |
Amount |
Total Amount |
Total Discounted Amount |
=Input!A2 |
=Input!B2 |
=Input!C2 |
=Input!D2 |
=C2*D2 |
=E2-(E2*Parameters!C2) |
By maintaining these formulas, we are cross-referencing the value which will be captured by the first four fields, to the Input tab. For the additional field Total Amount, we maintained a formula which multiplies the value of the Quantity and Amount fields. For the second additional field Total Discounted Amount, we are computing the discounted amount of the total amount based on the set parameter for the discount value.
Process Templates
Since the maintained value of parameter in the workbook calculation rules is just dummy data, we need to maintain the parameter value by creating a Process Template and Execution Activity.
Under Calculation Unit Details, create the Process Template and Execution Activity based on the information below. Make sure that the created parameter is declared in the Parameters node under Calculation Unit.
Process Template - General |
Process |
Description |
Process Type |
Process State |
PRO_ |
Process for Workbook Calculation |
Run |
Active Template |
Parameter |
Formula |
Value Selection |
PKF_DISC |
.75 |
|
Process Activity - General |
Activity Type |
Activity |
Description |
Activity Function |
Execution Activity |
A001 |
Using Parameters in Workbook Calculation |
03: Workbook Calculation |
Note
If the process template is not used for running a workbook calculation function, the parameter value is set to initial.
Final Output
Customer |
Product |
Quantity |
Amount |
Total Amount |
Total Discounted Amount |
CUST01 |
PROD01 |
1 |
10 |
10,00 |
2,50 |
CUST02 |
PROD02 |
2 |
20 |
40,00 |
10,00 |
CUST03 |
PROD03 |
3 |
30 |
90,00 |
22,50 |
CUST04 |
PROD04 |
4 |
40 |
160,00 |
40,00 |
CUST05 |
PROD05 |
5 |
50 |
250,00 |
62,50 |
CUST05 |
PROD0X |
50 |
500 |
25.000,00 |
6.250,00 |
CUST06 |
PROD06 |
60 |
600 |
36.000,00 |
9.000,00 |
The first four fields (Customer, Product, Quantity and Amount) just captured the data from the Input Model table as we make them to do because we just create a formula referencing to the Input. While for the additional field (Total Amount), it multiplied the value from the Quantity and Amount accordingly. For the second additional Field (Total Discounted Amount), the function calculated the total discounted amount based on the formula maintained.
We only just created a one liner of formula. Anyway, the function considered all records from the input function for processing because the fields have been maintained in the Granularity fields from the Signature section.
That process wasn’t like the previous one that we have. Dummy values play more roles in this scenario than of the others. I believe all possible confusion that you may have in the future about Parameters is already cleared out in this blog post. Knowing the fact that the Result is being calculated using a Parameter from your one and only Input table. But what if you have more than one Input table? What if aside from Input Table, you also have a Look Up table? How do you do the configuration? That too is a very interesting topic and I hope to answer those question on the fourth blog post of this Workbook Calculation Adapter Blog Posts Series focusing on the third example.
For other SAP Profitability and Performance Management related inquiries, you can post your questions through https://answers.sap.com/questions/ask.html and use primary tag: SAP Profitability and Performance Management.
You can also read other SAP Profitability and Performance Management posts via https://community.sap.com/topics/profitability-and-performance-management.
