Planning in Data Tables

Objectives

After completing this lesson, you will be able to:

  • Perform simple data entry and work with planning data in tables
  • Perform mass data entry
  • Use inverse dimension formulas

Data Entry

Data entry is key to planning functionality. SAP Analytics Cloud's planning functionality makes it easy to enter data and perform calculations – from simple to robust – on that data and write it back to your database.

In the following example, you can see some of the key data entry features in SAP Analytics Cloud:

  • Adjust plan by +/-, %, or absolute value
  • Shortcut scaling characters to simplify input
  • Automatic highlights of impacted and related cells
  • Messages with number of records changed

Data Scaling

Data tables in stories are automatically scaled to thousands or millions depending on the data. The scaling factor is displayed on the upper left of the data table.

It is also possible to set scaling for a table in the data region styles.

When you enter numeric values, the system automatically displays the scaling factor in the cell, as seen in the following example.

Note
The data table is scaled automatically by the system. That's what appears on the upper left in the following image.

Data Change Indicator

You can see in the following example that when there's a data change an asterisk appears in the affected column. The asterisk and the yellow markup mean that the data entry function is being used but not published.

Data entries can be tested before you save and publish them.

Values

You can type an absolute value in a cell, or type a relative value such as *2 or +500 to perform simple mathematical calculations on existing data. For example, if the data value in a cell is 100, and you type *2 in the cell, the value will change to 200.

Relative Value Options

X represents a number

Relative Value SymbolDescription
*XMultiply the value by X
/XDivide the value by X
X% or -X%Increase or reduce the value by X percentage
+XAdd X to the value
+-XSubtract X from the value

You can also include the scale when typing a value. For example, if you want to enter the value 1,000,000, you can simply type 1M, and SAP Analytics Cloud will interpret that entry as 1,000,000.

Scale Options

ScaleSingle LetterComplete WordShort Form
ThousandTThousandK
MillionMMillionM
BillionBBillionBn
Hint
You can also copy and paste values from a source such as Microsoft Excel or a flat file to develop your dataset.

Perform Manual Planning

Business Scenario

You have a forecast income statement for 2025, however, there are some adjustments you need to make due to changes in planning assumptions. There is a roll-out of a new product in March of 2025. Supply chain issues will not allow the new product to be produced in-house but one of your suppliers has the capacity. The only downside is that the supplier's quality is not the best, therefore, returns are expected to go up.

You need to adjust the data as efficiently as possible and to do this, you will perform manual input planning.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Create a private planning version
  • Update the forecast income statement using manual planning

Disaggregation

When data is entered for a parent member, the system will automatically disaggregate the data to the children of the parent equally or in proportion to existing values. This saves time for planners since they don't have to enter data for every base member.

Disaggregation can be controlled as follows:

  • Filter the data table so that input cells only represent base members.
  • You can control disaggregation by enabling Data Disaggregation - Data Locking and / or Data Disaggregation - Validation Rules in the planning model and also in the data table.
Note
Base members in a dimension don't have children. Base members are also referred to leaf members.

Key Terminology

There are several terms that relate to disaggregation, such as allocation and distribution. In this context, we're using these definitions:

  • Disaggregation: The system automatically disaggregated values entered for a parent to its children equally, or proportionally based on existing values.
  • Allocation: This is based on explicit instructions in an allocation process.
  • Distribute: This is an option in the planning panel used in stories.

Disaggregation Example

In the following Disaggregation example, discounts are disaggregated:

  1. Existing discounts: The children of the Discounts account in Feb (2025) contain existing values that total .8m. In Mar (2025) no values are posted for Discounts.
  2. New discounts: Values of 1m for Discounts in Feb (2025) and in Mar (2025) are entered.
  3. Discounts disaggregated:
    • The delta of .2m is spread proportionally to Pricing Adjustments and Sale Allowances. Since every dimension in the model is restricted to base members in the table filter, only two records are updated.
    • The 1m entered for Discounts in Mar (2025) it’s disaggregated equally to the four children accounts because they don’t contain any data.
Note
In the following Disaggregation example, if a dimension such as product isn't restricted in the table filter, then a new value will be disaggregated proportionally to all products that contain posted values.

Unbooked Cells

When you enter a value in an unbooked cell with a hash character (-), the following rules are used to determine how to disaggregate the value along each dimension:

  • If the unassigned member (#) is available as a leaf member of the source cell, this member receives the same value as the source cell, and other members of the dimension remain unbooked.
  • The unassigned member may not available and in these cases, the value is spread to base members based on the dimension's aggregation type. Some reasons why the unassigned member may not be available:
    • It's filtered out of the table.
    • It doesn't aggregate up to the source cell.
    • It doesn't exist for dimensions such as Time and Account. In these cases, the value is spread to base members based on the dimension’s aggregation type.

Additional Information

Visit SAP Help to find additional information about the disaggregation of values during data entry

Cell Locking in Stories

Cell Locking

Cell locks prevent data entry for specific table cells and are defined in the table. When you lock a table cell, the value in that cell won't be updated when you change the data.

When using cell locks, it's important to remember:

  • Cells locks can be saved with the story.
  • Cells locks are page-specific.
  • Cells locks are story-specific.
  • Cells locks apply to everyone who accesses the same page in the same story.

You can set locks individually in the table by right-clicking a cell and choosing Lock Cell or set locks on multiple cells by using the Value Lock Management panel. The Value Lock Management panel can be accessed from the main toolbar in the ...More option.

Set Locks

In the example below, five key elements for cell locking in the story and in the Value Lock Management panel are highlighted:

  1. Lock a cell: Right-click on a specific cell and lock it. This prevents data entry and disaggregation won't update the cell.
  2. View all locks: View existing locks and their priority in Value Lock Order.
  3. Set new lock: Create a new lock by clicking the icon.
  4. Clear locks: Clear all locks at once.
  5. Reset priority: Reset the lock order.

Rearrange Locks

To change a lock's priority, you can rearrange locks in the Value Lock Management panel. In the Value Lock Order, you can drag a lock to change the priority. After you move one lock, priority values appear for all locks in the list with their specific priority.

When you lock a table cell, the value in that cell won't be updated when you change the data. By default, data entry processes have lower priority than cell locks. If you want to give data entry processes higher priority, you can change the order of the locks.

Set and Unset Read Only Cells

When you need to prevent input for a large range of cells, you can use the Set/unset read only for selected cells option.

To use this option, follow these steps:

  1. Highlight the cells that you want to disable for input.
  2. Go to … More in the Tools area of the ribbon (depending on your resolution, use the ...More option on the right).
  3. Select Set/unset read only for selected cells.

Once applied, the selected cells are grayed out and data entry isn't possible.

When you save the story, the Set/unset read only for selected cells setting is retained.

Note
If you want to prevent data entry on only a few cells, use cell locks from the Value Lock Management panel. This panel only applies to Cell Locks.
Note
The Set/unset read only for selected cells setting doesn’t prevent disaggregation.

Disaggregate Planning Data, Lock Cells, and Set to Read Only

Business Scenario

You have made changes using manual planning to your private version of the forecast income statement for 2025. As the new product will be manufactured exclusively by a supplier, you need to increase gross sales and raw materials for 2025 but you want the change to be spread proportionally to all months based on existing values.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Disaggregate planning data
  • Lock cells from the planning table and Value Lock Management
  • Set cells to read only mode

Data Entry Modes

The data entry modes for data tables are as follows:

  • Single Data Entry Mode: Processes each new data change immediately. This the default data entry mode.
  • Fluid Data Entry Mode: Processes all new data changes at the same time if they are made in a fast sequence.
  • Mass Data Entry Mode: Processes all new data changes when you confirm that you've finished entering values.
Note
The default for new data tables is Fluid Data Entry Mode.

Mass Data Entry

Adding multiple values to a table can be time consuming when you've to wait for the system to update the data source after each entry.

Mass Data Entry allows you to enter multiple values in a table without waiting for the system to update between entries.

In order to use Mass Data Entry, follow these steps:

  1. Switch to Mass Data Entry using the table's More Actions menu.
  2. Enter data values.
  3. Select Process Data to apply the changes and exit or select Exit Mass Data Entry to leave without applying changes.

Perform Mass Data Entry in the Story Page

Business Scenario

You need to make quite a few adjustments to your planned income statement, due to some new information regarding supply chain issues in Q1. You want to make the changes and then trigger the system processing when you are ready. In order to do this, you use Mass Data Entry to make the changes.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Access Mass Data Entry from the table
  • Update the table and process the data

Fluid Data Entry

By default, data values are entered using Fluid Data Entry Mode.

In Fluid Data Entry Mode, data values entered in a fast sequence will be processed together at the same time as a batch; you don’t need to wait for the system to update between entries.

Time Interval for Fluid Data Entry

To set or change the time interval for fluid data entry batches, go to SystemAdministrationSystem Configuration and update the value for Time interval of fluid data entry batches (in milliseconds).

Data values entered with time between every two successive data entries within this set time interval will be treated as belonging to the same fluid data entry batch. For example, you set the time interval to 500 milliseconds (0.5 seconds). If there's a 0.5 second of inactivity, a batch is processed, and the next data value you enter will be treated as the first one of a new fluid data entry batch.

Use Fluid Data Entry Mode in a Data Table

Business Scenario

You need to make quite a few adjustments to your planned income statement, due to some new information regarding supply chain issues in Q1. You want to enter multiple values rapidly and after you are finished typing, the system will process the new data. In order to do this, you use Fluid Data Entry from the Builder pane to make the changes.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Access Fluid Data Entry from the table
  • Update the table and process the data

Inverse Dimension Formulas

Inverse Formulas

An aspect of dimension formulas is that you can add inverse functions to calculate a value that is the result of the formula. By default, planning models don't support data entry for formulas. But with Inverse functions, you can enable data entry for the formula result and then specify one or more ways as to how the formula is reversed.

Scenario: You want to input values for revenue and cost and have the system calculate profit. You also want to enter profit and have the system back-calculate cost.

In the example below, the two possible outcomes are displayed:

  • Without an Inverse Formula: Profit is calculated and input is not possible.

  • With an Inverse Formula: Profit is calculated and input is possible.

In the previous example, on the left, the Account dimension with the calculated member Profit that is calculated as [Revenue] – [Cost]. With this formula, you can't enter data for the Profit account since it's the result of a formula.

However, you can define an inverse formula for it. In the previous example, on the right, you can see the formula [Revenue] – [Cost] | INVERSE ([Cost] := [Revenue] – [Profit]). With this inverse formula option of Cost = Revenue - Profit, you can now enter a value for Profit in a story, and Cost will be adjusted while Revenue remains constant.

Note

You can display the dimension formula in the story: Highlight the account with the formulaGo to ...MoreDisplay the Formula bar.

You can also input data for an account that is a percent type. For example, profit %.

Multiple Inverse Formulas

You can define multiple inverse formula options in a given formula. When doing so, the order in which they're typed determines the priority.

Inverse formulas require the following characters:

CharacterDescription
|A vertical bar (pipe) marks the end of the base formula and the beginning of one or more inverse functions.
:=You must add a colon before the equal sign when defining an inverse function.

In some cases, it may not be possible to change the value for cost. For example, a cell lock can be applied to cost, or values can be entered for cost and profit simultaneously. To allow data entry in these cases, you can define a secondary inverse formula that assigns value to revenue:

[Revenue] – [Cost] | INVERSE([Cost] := [Revenue] – [Profit]) OR INVERSE([Revenue] := [Profit] + [Cost])

When the first inverse formula can't be applied, the data entry can be carried out using the secondary formula. As a result, the revenue value is adjusted.

Additional Information

Visit SAP Help for more information on Inverse Formulas

Use Inverse Dimension Formulas to Plan

Business Scenario

In the process of determining a planned income statement, you are interested in the resulting gross profit.

In addition to calculating gross profit, you also need to be able to enter the target gross profit and have the system back-calculate cost or revenue to achieve the goal. In order to do this, you will use an account with an inverse formula.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Access the formula bar in the story
  • Input the target gross profit and back-calculate cost
  • Input the target gross profit and back-calculate revenue

Log in to track your progress & complete quizzes