Cell Locking and Inverse Formula in SAP BPC 10.1 Unified Model

Cell Locking and Inverse Formula in SAP BPC 10.1 Unified Model

Business Case:

In a business scenario, End users would want plan data on multiple scenarios in an input schedule. SAP BPC 10.1 unified model provides us with two new functionalities, Inverse Formula and Cell locking. Business requirement is to calculate amount from price and quantity in a query as a formula. The system will recalculate price and quantity when amount is entered. We will be using inverse formula and cell locking functionality to achieve the requirement.

Amount = Quantity * Price

Creating an inverse formula is similar to calculated key figure in BEX query and use cell locking to locally lock a particular cell in an excel so that the value is not affected while planning data. Here we will see in detail for designing a BEX query with inverse formula and creating a BPC input schedule on top of the BEX query,

Prerequisite is to have a real time info cube built which we will be using it as a source.

Step 1: Create a BEX query which has below structure,

Row:

Key figure: Price, Quantity and Amount (We will see how it defined in step 2)

Characteristic: Product

Column: Characteristic: Calendar year/Month

Step 2: Amount is a formula created as product of Quantity and Price.

Amount (Inverse Formula),

Define inverse formula for Amount so that the reverse calculation works based on below definition.

Quantity = Amount/Price

Price = Amount/Quantity

Now we can validate and save the input ready BEX query. This would be our source while building a BPC input schedule in unified model.

Step 3: Create the BPC report with input ready BEX query we created in the previous step.

Step 4: When we change the value for Amount, quantity gets recalculated.

Quantity got recalculated,

Step 5: In second scenario, we want to keep quantity value fixed. We will be using cell locking functionality. So when we change the value for amount and lock the quantity cell, price value gets updated and not the quantity.

Cell locking keeps measure values fixed upon calculations.

Updating Amount value,

Quantity was kept fixed while the price got recalculated based on amount value,

Benefits:

Users can perform what-if analysis before saving the data in the system.

Provides flexibility while planning data.

Cell locking works locally so it doesn’t affect any other users.