Workbook Calculation (with Parameter)

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:

1. 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.
2. 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.
3. 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.