Enter Planning Data in Tables

When working with a table based on a planning model in SAP Analytics Cloud, you can create and edit model values by typing in the table cells.

In this section, you will learn about the following topics related to editing values in a table:

You can also explore more advanced topics related to data entry:

Entering Values in a Table

When working with a table based on a planning model in SAP Analytics Cloud, you can create and edit model values by typing in the table cells.

Entering values is possible when the following conditions are met:
  • You have permissions and authorizations to make changes to the model values.

    Users with BI roles can create private versions and change single booked values. Planner Reporter, Modeler, and Admin roles can use any of the planning tools to enter data on public or private versions. For more information, see Permissions. For more information about working with versions, refer to Create, Publish, and Manage Versions of Planning Data

  • The cell is not locked, either by a value lock or by data locking.

    For more information on value locks in stories, see About Value Lock Management. For more information on data locking, see Configuring Data Locking.

  • If the cell is calculated by a formula, the formula must have one or more inverse formulas defined, and the target cells must be booked. For more information, see Inverse Formulas. For restricted measures and accounts, you can also change the values of cells that are included in the scope of the restriction.

  • The value can be disaggregated from the cell to one or more leaf members for each dimension in the model. For more information, see Disaggregation of Values During Data Entry.

Tip

When you modify data in a table cell, all of the cells in the visible area (processed area) – including any new cells (records) – will be highlighted.

Only the cells in the currently visible area (currently processed area) will be highlighted. If your table has a vertical scroll bar, cells in areas that haven't been processed yet won't have any highlighted cells.

Note

You can choose to show tooltips that explain why a selected cell does not allow data entry. To enable these tooltips, from your table select Start of the navigation path Next navigation step Show/Hide Next navigation step Reason for unplannable dataEnd of the navigation path.

Data entry and copy and paste operations do not affect members that have been excluded from the table by story, page, or table filters on dimensions or dimension attributes. However, data actions and allocation processes can still affect these members.

Members that are selected in a filter but set to invisible are treated as visible members when you type or paste values in the table.

How to Enter Values

You can type an absolute value in a cell (for example, -2398), or type a relative value such as *2 or +500.

The following symbols are used to change the relative value of a table cell. nn refers to the numeric value.
Relative Value Symbol Description
*nn Multiply by nn
/nn Divide by nn
nn% Increase or reduce by nn percentage.
+nn Add nn to the value
+-nn Subtract nn from the value (add a negative amount)

You can also include the scale (for example, Thousand, Million, Billion) when typing a value.

The following values can be used with or without a space between the number and the letter.

Values must start with a number. For example, to use the scale notation to write 750 Million, you could type 750 Million or 0.75B ( .75B will also work).

Scale

Single Letter

Complete Word

Short Form

Thousand

1T

1 Thousand

1K

Million

1M

1 Million

1M

Billion

1B

1 Billion

1Bn

You can change cell values by copying and pasting, cutting and pasting, or using allocation features. For more information, see Copying and Pasting Cell Values, Cutting and Pasting Cell Values, and Assign and Distribute Values with the Planning Panel.

Working with Booked and Unbooked Cells

When you change a booked value, the leaf members that aggregate up to that value are adjusted proportionally to reflect your change.

When you enter a new value in an unbooked cell, which displays a dash character (-) instead of a value, values are also booked to one or more leaf members for each dimension that aggregates up to the cell. The Unassigned member usually receives these values. For more information, see Disaggregation of Values During Data Entry.

Copying and Pasting Cell Values

In tables and grids in SAP Analytics Cloud, you can copy and paste cell values, the underlying values of their leaf members, and formulas. You can also create references between cells.

Data can be copied within or across grids and tables, and you can also copy data from an external source such as an Excel spreadsheet.

In a table, you can paste data into cells that can receive data input.

You can also cut and paste values. For details, see Cutting and Pasting Cell Values.

Copying Data in a Table

When copying data within a table, there are two types of paste operations: pasting details, or pasting overall values.

When you copy a source cell with model data, all of the underlying values that aggregate up to it are copied as well. By default, all of these values are pasted if possible. This is known as pasting details.

For example, you might copy a Q1 member and paste it to Q2 for the same version. On the month level for a calendar year, the value for January will be copied to April, the value for February will be copied to May, and the value for March will be copied to June.

Underlying data can be pasted when the following conditions are met:
  • You are pasting data to a single target cell. Cells that aggregate up to the target cell will also be affected.

  • You are copying across two time periods at the same level, for example, December and January, or you are copying across two different leaf members, for example, from one sales manager to another.

  • The source cell that you copied is visible.

When these conditions are not met, you are prompted to paste the overall value instead. In this case, the behavior is the same as typing the value into the cell. The distribution of the copied value among the leaf members is determined by the existing proportions between those members if the cell already has a value. Only the aggregated value of the source cell’s leaf members has been copied.

Note

Copying and pasting details isn’t supported with a BPC live data connection. You can still copy and paste overall values, though.

If you want to specify which paste operation to use, you can select Start of the navigation pathEdit Next navigation step Paste SpecialEnd of the navigation path from the toolbar. You can then choose to Paste overall value or Paste details to include underlying values, and set the selected option as the default for future Paste Special operations.

Copying with Percentage Data

You can copy and paste to and from table cells that show percentages. When copying and pasting between percentage cells and numerical cells in a table or in an external spreadsheet, 100% is treated as a numerical value of 1.

Copying and Pasting Multiple Values

A rectangular group of selected cell values can also be copied and pasted to multiple cells in a table. In this case, cell values are pasted as overall values.

Note
If the area that you are pasting to contains cell references, you cannot paste multiple cells to that area. You will need to copy and paste values individually, or paste to a different area.

This type of operation can also be performed by selecting a source area and dragging the bottom right corner of the region horizontally or vertically to include the target area. For example, you could select several account values for January and February 2019 and drag them to the December 2019 cell to paste the January and February data to alternating months for the rest of the year. If the target region is larger than the source region, the copied values are repeated. If the target region is smaller, not all of the source values are pasted.

Copying and Pasting with Empty Cells

The destination cells can be booked values, unbooked values, or a mix of both. When there is a mix of booked and unbooked target cells, the proportions between the child members of booked cells are maintained before and after the operation.

When copying from a grid or an external spreadsheet, cells that are empty or that contain the en dash (–) character are treated as unbooked. You can therefore copy and paste data from an external spreadsheet to a region of unbooked cells while keeping some of the target cells unbooked. However, an unbooked value cannot be pasted to a booked cell.

Pasting to More Than One Account or Measure

The target cells can belong to different accounts, as long as they use the same aggregation type. Note that copied values won’t have their signs flipped if there are conflicting target account types, such as income and expenses.

You can also paste to multiple measures in a model with measures.

Pasting to More Than One Hierarchy Level

If you paste to multiple levels of the same hierarchical dimension, values are pasted only to the child members in the target cells, and the cells for parent members are calculated by aggregation. For example, if you paste to an area that contains values for Q1, January, February, and March, then only January, February, and March are updated with the copied values. The new value for Q1 is calculated by aggregating the monthly values.

Avoiding Slow Performance

When there are multiple dimensions in the table, you can paste to different sets of members for each dimension. To avoid slow performance, you'll be notified when you try to paste to an area that includes a large number of different sets.

Copying and Pasting on Calculations

You can paste overall values to a calculated measure or account if you've defined an inverse formula for it. Underlying values generally can't be included.

Pasting to currency conversions is also supported. For a model with measures, you can copy and paste overall values to or from base currency measures and conversion measures. However, the values won't be converted. See Plan with Currency Conversion for details.

Pasting to Aggregated Accounts

In some cases, you can paste underlying values to an empty aggregated account that has calculated subaccounts.

The cells of the calculated subaccounts do not receive any values directly, but the rest of the data is copied as usual. If the copied data includes calculation inputs, the calculation will show the same values in the target cells.

For example, you may have created a blank version with an empty top-level account like Income Statement, which has several calculated subaccounts. To quickly fill in the values, you can copy and paste the value of this account, including its underlying records, from an existing version to the new version.

Say one of the Income Statement subaccounts is Revenue, calculated as Price * Units Sold. If Price and Units Sold don't aggregate up to Income Statement, Revenue doesn’t receive any values in the new version. To get those values, you can copy the calculation inputs: Price and Units Sold.

When you copy an aggregated account with calculated subaccounts, possible empty target cells will be highlighted. Source and target cells need to belong to the same account, and meet the requirements for copying and pasting details. Also, the target cell can't be a restricted measure or account.

Copying Across Restricted Measures

You can copy and paste between restricted accounts and measures. The target cells must be included in the restriction applied to the account or measure, however.

In many cases, such as when you are copying from one period to another in a forecast layout, it's helpful to copy the underlying values of the source cell. You can copy underlying values across restricted accounts and measures when a few conditions are met:

  • They are both restricted along the same dimensions.

  • A single member is chosen for each restricted dimension. For example, Date = "2020".

  • Each restricted dimension other than the date dimension is restricted to a leaf member.

  • The date dimension is restricted to the same level, for example, Date = 2019 and Date = 2020.

Cutting and Pasting Cell Values

In SAP Analytics Cloud, you can cut and paste cells to paste the values while deleting them from the source cells.

Cut and paste by selecting source cells and pressing Ctrl + X or Cmd + X , then selecting target cells and pressing Ctrl + V or Cmd + V .

In most cases, the cell that you cut will become unbooked when you paste the value. There are a few exceptions, though:
  • If the value of the source cell cannot be deleted, it will only be copied. You'll see a message in this case. See Deleting Values in a Table for a list of restrictions.

  • If you cut a group of cells and paste to an overlapping group of cells, new values are pasted to the overlapping cells.

  • If you cut a cell and paste it to a cell that aggregates up to the source cell, the source cell keeps its original value. For example, you can cut from 2020 and paste to December 2020. In this case, 2020 keeps the same value but only December is booked.

Otherwise, cutting and pasting cells works like copying and pasting them. For example:
  • Where possible, you'll paste underlying records by default.

  • You can cut multiple cells.

  • You can cut and paste across different tables, or between custom cells and cells with model data.

  • You can undo the operation to get the previous values back. If you cut and paste across versions, you can undo the change separately for each version.

For more details, see Copying and Pasting Cell Values.

Deleting Values in a Table

In SAP Analytics Cloud, you can delete values in a table to remove them from a public or private version of a planning model, or to simulate changes to embedded data.

You can delete data values from a table by selecting a cell or multiple cells and then pressing Delete. You are able to delete from public versions and private versions.

How Deleting Values Works

When you choose to delete a value, keep the following information in mind:

  • Delete removes the value instead of setting the cell value to zero.

  • When publishing, the deletions are propagated to the public version for those values that were already present in the private version when it was created.

  • You can use the history feature to undo and redo delete actions.

  • In Mass Data Entry mode, delete followed by copy & paste operates as a simple value change, not a redistribution.

  • For first or last accounts, delete removes all data in the time span of the node (for example, the whole year).

Limitations

There are some things that you need to be aware of when deleting values:

  • Deleting values may affect performance for queries and planning.

  • You can't use delete in assignee versions of input schedules or in BPC write-back models.

  • Delete doesn't work for formula accounts with inverse formulas.

  • Delete doesn’t work in tables that have cell locks. Unlock all cells in your table before deleting values.

Note
You can also cut and paste cell values. The cut cell values are deleted when you paste them. For more details, see Cutting and Pasting Cell Values.

Entering Multiple Values in a Table

In SAP Analytics Cloud, you can enter multiple values in a table without waiting for the system to update between entries.

Context

Adding multiple values to a table can be time-consuming if you have to wait for the system to update the data source after each entry. This feature provides an editing session that allows you to enter multiple values before processing the updates. You also have the option to process and save some updates and then continue to make changes.

Procedure

  1. From your table, select Start of the navigation path (More Actions) Next navigation step  Mass Data EntryEnd of the navigation path.

    The Mass Data Entry dialog appears.

    Tip

    You can also select this option by using the following keyboard shortcut: Ctrl + Alt + M (for Windows) or Control + Option + M (for Mac).

  2. Add values to the table cells.

    You can enter values in unbooked cells and change existing cell values. Each time you enter a cell value, the cells that aggregate up to it are locked until after you have left the editing session.

    Note
    You can copy and paste data to multiple cells during a data entry session, but there are some limitations:
    • Underlying values cannot be copied across versions; only values can be copied.

    • The source cell used for the copy is disabled to avoid conflicting entries.

    • Underlying values cannot be copied from a cell that was originally NULL.

      During Mass Data Entry, you can change a NULL cell to a data cell by entering a number into it. Even though it may look like a booked cell, it originally wasn't one so you won't be able to copy underlying values from the cell.

  3. To control how your data changes are applied, select one of the following:
    • Process Data - Process the changes you've made so far and then continue adding data.
      Tip

      You can also select this option by using the following keyboard shortcut: Ctrl + Enter (for Windows) or Control + Return (for Mac).

    • Exit Mass Data Entry - Any changes that have not been processed won't be saved.

Results

The changes are applied.

About Value Lock Management

In SAP Analytics Cloud, you can prioritize locks to control data entry for table cells.

When you lock a table cell, the value in that cell will not be updated when you make changes to 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 Lock Priority

You can rearrange locks in the Value Lock Management panel to change their priority. After you move one lock, priority values appear for all locks. The first item in the list (lock or data entry process) shows priority 1, which is the highest priority.

To rearrange the locks, use the following procedure.
  1. Select your table.

  2. From Tools, select Value Lock Management.

  3. In the Value Lock Order section, select a lock and drag it higher or lower.

    When you release the lock, each lock now shows a value: 1 is the highest priority.

  4. Continue to drag the locks to your desired positions in the list.

  5. When you are finished and want to close the panel, select Done.

Set Multiple Locks

You can set locks individually in the table (by selecting a cell and then using Lock cell for each lock) or you can use the Value Lock Management panel to quickly lock multiple cells.

To use the panel to apply locks, use the following procedure:
  1. Select your table.
  2. From Tools, select Value Lock Management.
  3. In the Value Lock Management panel, select (Create Locks).
  4. Select the cells in the table to apply locks to.
  5. When finished setting locks, select (Create Locks).
  6. To close the panel, select Done.

Disaggregation of Values During Data Entry

In SAP Analytics Cloud, when you enter or change data for a planning model cell, the value is automatically spread to leaf members that aggregate up to it. This process is called disaggregation.

For planning models, data is stored in leaf members of each dimension hierarchy. Parent members only show the aggregated values of their children, and generally don't contain values on their own. Because of this, disaggregation happens whenever you change data that represents more than one combination of leaf members for all dimensions.

Tip

Keep in mind that disaggregation doesn’t just happen over the dimensions added to your table axes. For the other dimensions, disaggregation will occur unless they’re filtered to a single leaf member.

Accounts and measures can use different aggregation types, including a default aggregation type and an optional exception aggregation type for specific dimensions. These aggregation types determine whether data entry is supported in different circumstances, and how data entry will be disaggregated.

Note

If your model contains both measures and dimensions, a modeler can choose whether to use aggregation settings from the accounts or measures. To learn more, see Set Structure Priority and Create Custom Solve Order.

Classic account models don’t have separate measures, so the accounts always set the aggregation type.

There are a few ways that you can get more control over disaggregation while planning in a table:

Changing Data for Booked Values

When you change a booked value, the leaf members that aggregate up to that value are adjusted to reflect your change. Usually, this happens proportionally.

Some aggregation types don’t support data entry on booked parent members, so the corresponding cells won’t be input enabled in a table. You can check the reason that a cell is greyed out by selecting Start of the navigation pathShow/Hide Next navigation step Reason for unplannable dataEnd of the navigation path from the table action menu (More Actions) and selecting the cell.

Entering Data on Unbooked Values

When you enter a value in an unbooked cell, which displays a dash character (-) instead of a value, 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, for example, because it is filtered out of the table, because it does not aggregate up to the source cell, or because it does not exist for dimensions such as date and account. In these cases, the value is spread to leaf members of the dimension based on the aggregation type.
  • This aggregation type is determined either by account or measure settings, depending on which has priority. When the account or measure just has a default aggregation type, all dimensions will use it for disaggregation. With an exception aggregation type defined, specific dimensions can use that aggregation type instead.

Aggregation type

Description

SUM

The source value is divided equally among the leaf members. For example, if you enter one million in a cell with two leaf members, the leaf members receive 500,000 each.

AVERAGE and AVERAGE excl. NULL

(Available as exception aggregation. To support disaggregation, they need to be used with SUM.)

Each leaf member receives the same value as the source cell. In this case, the leaf members receive one million each.

NONE

Each leaf member receives the same value as the source cell. The leaf members receive one million each.

FIRST

(Available as exception aggregation)

The first leaf member receives the same value as the source cell. If you enter one million for Q1 using the calendar year, for example, January receives one million.

LAST

(Available as exception aggregation)

The last leaf member receives the same value as the source cell. If you enter one million for Q1, March receives one million.

For some exception aggregation types, data can’t be disaggregated from unbooked cells. In this case, you can enter data directly to leaf members of the exception aggregation dimensions, but not to their parents. If the exception aggregation dimensions aren't added to a table axis, they need to be filtered to a single leaf member to enable data entry:
  • SUM

  • NONE

  • MIN

  • MAX

  • AVERAGE excl. 0, Null

  • MEDIAN (and variants that exclude null and zero values)

  • FIRST QUARTILE (and variants that exclude null and zero values)

  • THIRD QUARTILE (and variants that exclude null and zero values)

  • AVERAGE when used with NONE as default aggregation

  • COUNT (when booked, leaf members will only show a value of 1)

For other aggregation types, data entry is not supported at all, for example:
  • LABEL

  • COUNT excl. NULL and COUNT excl. 0, NULL

  • NONE when used with SUM as default aggregation

Note

If you enter data for an aggregated account member, its children may include different account types such as Income and Asset accounts as well as Expense and Liabilities and Equity accounts. In this case, accounts with different aggregation behavior from the source account do not receive values from disaggregation.

For more information on the aggregation types and sign switching, see Attributes of an Account Dimension.

If you do not want to book values to the Unassigned member for a dimension, you can use one of the following methods:
  • After the value is booked to an Unassigned member in the table, select it and select Start of the navigation path Next navigation step Distribute ValuesEnd of the navigation path to distribute the value to other members.

  • To prevent values from being booked to the Unassigned member, filter the dimension and select all members except the Unassigned member. You can apply this filter to an individual table by selecting Add Filters in the Builder panel, or to the entire story by selecting (Story Filter) from the top navigation panel.

Note
  • If your model contains many dimensions where the Unassigned member is not available, spreading data to all leaf members for each dimension may result in slow performance. In this case, you'll either get a warning about slower performance, or a message to filter the data or change the target cell to create fewer data records in a single operation.

  • Limits apply to data entry on unbooked cells: data can't be disaggregated to more than 650 000 records from a single cell. For a mass data entry session, the combined limit for all changed cells is 6 500 000 records.

  • In some cases where you are working with more than one hierarchy for a dimension, data may be booked directly to parent nodes. For more information, see Entering Values with Multiple Hierarchies.

Entering Values with Dynamic Time Filters

In a table based on a planning model in SAP Analytics Cloud, you can enter data into cells calculated by dynamic time filters, including member functions such as YTD.

Entering values on member functions

When you enter data on a YTD, QTD, or MTD value, the change in value is assigned to the last booked member of the date dimension.

For example, consider a table that shows YTD values for 2018, with $1 million booked to both January and February 2018 and the rest of the months unbooked:

YTD member function and monthly values before data entry

If you change the YTD value for April 2018 from $2 million to $3 million, the extra $1 million will be booked to February 2018:

YTD member function and monthly values after data entry
Entering values for calculations with dynamic time filters

Inverse functions can be defined for calculated members based on restrictions that use dynamic time filters. This allows you to simulate planning scenarios by changing KPIs such as YOY growth, for example. For more information, see the Inverse formulas with dynamic time filters section of Inverse Formulas.

Entering Values with Multiple Hierarchies

In SAP Analytics Cloud, when you enter planning data for a dimension with multiple hierarchies, you can end up with values booked directly to parent nodes in some of the hierarchies.

For example, you might be working with a geography dimension displayed in one table using a hierarchy that shows the regions as leaf members, and in another table that shows regions as parent members of the countries. If you use the first table to book a value to North America, the second table will show the value booked directly to that parent node.

Booking data to North America

You might use this type of workflow if you want global leads to book values to the regions only, and then have regional managers spread those values to the country level. But the tables can look confusing side-by-side. (If you don’t want any booked parents to show up, you can turn on the Show only leaves in widget option. See Modifying a Table to learn how.)

Tip

To set up this workflow, you can add hierarchies to the geography dimension that exclude the leaf members of the original hierarchy. For more information, see Parent-child hierarchies with a subset of members. You can then exclude the “Not In Hierarchies” member in your story.

Working with the second table, you can continue performing data entry, for example, by changing the value for North America or booking data to other members in the hierarchy. Because data is aggregated differently for each hierarchy, the value of North America may appear inconsistent between the two tables when you book data to its children.

Different values for North America across two hierarchies

If you want to disaggregate the value of North America to its leaf members, you can select the North America cell in the second table and choose Start of the navigation path Next navigation step Distribute ValuesEnd of the navigation path. The source cell is automatically locked, and you can spread its value to leaf members of that hierarchy. See Assign and Distribute Values with the Planning Panel for details.

Spreading data from North America

In this case, all of the value is spread to the United States member. Since it's a leaf member in both hierarchies, the data is consistent across the two tables.

Values spread to leaf members

If you need to get rid of the booked parent node and can’t use spreading, you can also delete the cell value or Undo, Redo, and Revert Changes to Versions.