Configuring Tables in Form Templates

Getting Started

Global Functions

Lucanet Lume

Data Hub

Administration

Extended Planning & Analysis (xP&A)

Consolidation & Financial Planning

ESG Reporting

Disclosure Management

XBRL Tagger

Lease Accounting

Banking & Cash Management

Data Collection

All Steps at a Glance

User interface of Data Collection

User Roles in Data Collection

Basic Configuration

Creating and Configuring Form Templates

Configuring Tables in Form Templates

Creating and Configuring Validation Rules

Importing Reporting Entities from Consolidation & Financial Planning

Creating and Configuring Reporting Packages

Collecting Data

Tax Compliance & Reporting

Glossary

This page describes how to configure Pivot table, CFP Pivot table, and Dynamic table elements in a form template — adding and configuring columns and rows, and setting up drill down. For instructions on how to create a form template and add elements to it, see Creating and Configuring Form Templates.

Configuration options vary depending on the table type:

Pivot table

and

CFP Pivot table

support the full range of column types, row configuration, and drill down described on this page.

Dynamic table

supports all column types. Rows are added by collectors during data collection, so a dynamic table has no row configuration in the template. Drill down is not available for dynamic tables.

When you add a CFP Pivot table element to a form template, the system asks you to choose which rows to include from the selected Consolidation & Financial Planning workspace before the table is created. This row selection step lets you control exactly which eligible rows end up in the new table, instead of pulling all of them in automatically.

To add a CFP-based pivot table:

Add a CFP Pivot table element to a section (see Adding Elements). The Add Element dialog is displayed:

Select the Consolidation & Financial Planning (CFP) workspace whose structure you want to import and click Import. The Select Rows to Include dialog is displayed, showing all eligible rows of the selected workspace in their original hierarchy:

For

General ledger

Subledger

, or

Statistical Ledger

workspaces, only account hierarchy rows are shown. Formulas, separators, and total lines are excluded.

For

Schedule

workspaces, all workspace structure rows are shown. Formulas and total lines are excluded.

The 'Select Rows to Include' dialog is displayed. — 'Select Rows to Include' dialog

Choose one of the available selection options:

Select all

— selects all eligible rows across all levels.

Show items only

— selects only rows of type

Item

in the CFP account hierarchy, regardless of their position in the hierarchy.

Custom selection

— lets you check or uncheck individual rows. Selecting a superordinate row automatically selects all of its descendants (unless

Show items only

is active).

Click Confirm. The CFP Pivot table is created with the selected rows, preserving their original hierarchy.

For Schedule workspaces, columns are also pre-populated from the workspace. For General ledger, Subledger, and Statistical Ledger workspaces, the table starts without columns — add them as described in Adding Columns.

A selected superordinate row whose subordinate rows were not selected is included as a standalone row. If only some subordinate rows of a superordinate row are selected, the superordinate row travels with the selected subordinate rows to preserve the hierarchical structure.

Columns define the data points you want to collect for each row in the table. Click Add column in the table header and select one of the available column types.

The available column types depend on the table type.

Creates a column from scratch where you define all properties manually.

The 'Add new column' dialog is displayed. — 'Add new column' dialog

Field	Description
Name	Unique name for the column within the table.
Type	Data type for the column values. Available types: Numeric (float), Numeric (integer), Currency, Text.
Period value type	Defines which type of period data is retrieved when importing from Consolidation & Financial Planning. Available for Numeric and Currency columns. See Period Value Type for CFP Import for details.
Used for postings	When activated, this column is included in the posting data that CFP imports from Data Collection. Available only for Currency columns in CFP Pivot table elements. One or more currency columns per table can be activated. See Used for Postings for details.
Decimal places	Specifies the number of decimal places. Available for Numeric (float) and Currency column types.
Text type	Defines whether the text field allows single-line or multi-line input. Available for Text column type.
This column is mandatory	When activated, data collectors must provide a value for this column.

Period value type is a required setting for Numeric and Currency columns in Pivot tables and CFP Pivot tables. It defines which type of period data the system retrieves when importing from Consolidation & Financial Planning, so that each column gets the right values — for example, opening balances, within-period transactions, or closing balances.

Period value type	Description
Beginning of period	Value at period start
Within period	Changes during the period
End of period	Value at period end
YTD value	Cumulative from fiscal year start
Standard (auto)	Lets Consolidation & Financial Planning determine the value based on account type.

Period value type is configured when creating the column. Once data has been collected in the column, this setting cannot be changed.

When Used for postings is activated on a currency column in a CFP Pivot table, values from that column are included when posting data from Data Collection is imported into CFP. One or more currency columns per table can be activated to be included in the posting import.

Postings are supported for the following CFP pivot table types: Balance Sheet, P&L, Schedule, and Statistical Ledger.

For the full import workflow, see Importing Data Collection Postings into CFP.

Creates a column based on dimension elements from Consolidation & Financial Planning. The following dimensions are available:

Transaction types

Partners

Adjustment levels

The 'Add Column from CFP' dialog is displayed. — 'Add Column from CFP' dialog

CFP pivot tables based on Schedule workspaces automatically include Value query columns. Value query is a transaction type specific to Schedule workspaces in CFP. These columns appear alongside any other transaction-type columns in the table.

Value query columns behave as follows:

Read-only for collectors

— the values are calculated from the CFP schedule source and cannot be edited during data collection.

Available as operands

— you can reference value query columns in total and formula column expressions.

Included in CFP import

— values are included when collectors import data from CFP into the form.

Excluded from Excel and Lucanet.Financial Warehouse import

— Value query columns are skipped when importing from those sources.

Creates an automatically calculated column that aggregates values from other columns.

The 'Add Total Column' dialog is displayed. — 'Add Total Column' dialog

Field	Description
Name	Unique name for the column within the table
Type	Data type for the aggregated values. Available types: Numeric (float), Numeric (integer), Currency.
Decimal places	Number of decimal places for the result. Available when Currency or Numeric (float) is selected.
Select columns	The columns whose values are aggregated.

The total column is only available when your table contains at least two numeric or currency columns. Calculated columns (such as existing total columns) cannot be included in a new total column.

Creates a column whose values are calculated automatically from an arithmetic expression. Only administrators can add formula columns. Formula columns show the ∫ icon in the column header alongside a lock indicator.

The 'Add Column' dialog for a formula column is displayed, showing fields for Name, Data type, Formula, and Decimal places. — 'Add Column' dialog for a formula column

Field	Description
Name	Unique name for the column within the table
Data type	Data type for the calculated values: Numeric or Currency. Determines which same-table columns are eligible as operands.
Formula	The arithmetic expression, built from operand columns and numeric constants
Decimal places	Number of decimal places for the result

Operand scope

A formula column expression can reference:

Columns in the same table that match the selected data type

A column from another table within the same form template. You also specify how the source row is resolved:

Fixed row

— uses one specific row from the source table for every row in the formula column. Useful when the source table contains a single reference value such as an exchange rate.

Matched row

— uses the row from the source table with the same name as the current row. Useful when both tables share the same row structure.

A column from a table in a different form template — resolved at collection time for the same reporting entity and period

Numeric constants

— for fixed rates or divisors, for example

× 0.25

for a 25% tax charge

Key behaviors

Collectors can hover over a formula cell to see its expression.

If any operand cell is

empty

, the result is empty — not zero.

Division by zero

produces an empty result — never an error or infinity.

References are stored by internal ID. Renaming a row or column never breaks a formula.

Formula columns

can

be used as operands in intercompany validation rule definitions.

If the table contains no numeric or currency columns when you create the formula column, the formula builder indicates that no same-table columns are currently available. Operands from other tables remain accessible, and same-table columns can be added later.

Row configuration is available only for Pivot table and CFP Pivot table elements. Dynamic table rows are added by collectors during data collection.

Rows define the items or categories for which you want to collect data. Click Add row at the bottom of the table and select one of the available row types.

Creates a new row where you define the name manually.

A drop-down menu with an option to add an empty row is displayed. — 'Add empty row' option

Creates a row that automatically aggregates values from other rows.

A drop-down menu with an option to add a total row is displayed. — 'Add total row' option

Creates a row whose values are calculated automatically from an arithmetic expression. Only administrators can add formula rows. Formula rows show the ∫ icon next to the row name.

To add a formula row, click + Add row and select Add formula row.

The 'Add formula row' dialog is displayed, showing fields for Name and Formula. — 'Add formula row' dialog

Field	Description
Name	Unique name for the row within the table
Formula	The arithmetic expression, built from operand rows and numeric constants

Operand scope

A formula row expression can reference:

Rows in the same table — including regular data rows, the total row, and other formula rows

A row from another table within the same form template. You also specify how the source column is resolved:

Fixed column

— uses one specific column from the source table for every column in the formula row. Useful when the source table contains a single reference value such as an exchange rate.

Matched column

— uses the column from the source table with the same name as the current column. Useful when both tables share the same column structure.

A row from a table in a different form template — resolved at collection time for the same reporting entity and period

Numeric constants

Key behaviors

Collectors can hover over a formula cell to see its expression.

If any operand cell is

empty

, the result is empty — not zero.

Division by zero

produces an empty result.

References are stored by internal ID. Renaming a row or column never breaks a formula.

Formula rows

cannot

be used as operands in intercompany validation rule definitions.

A formula row can be placed at any position in the table except after the

Total row

. If one formula row references another, the system calculates them in the correct sequence automatically.

The Total row always excludes formula rows from its sum to prevent double-counting of derived values.

To add a subordinate row under an existing row, right-click the row and select Add row. The submenu offers:

Option	Description
Empty	Adds an empty subordinate row under the selected row.
Based on the CFP Partner dimension	Opens the Add row based on CFP dialog to add subordinate rows based on the Partner dimension.

Context menu for adding a subordinate row — Adding a subordinate row

The Add row option is not available on total rows.

Drill down is available only for Pivot table and CFP Pivot table elements.

Drill down allows data collectors to provide more detailed data at a lower, reporting entity-specific level. You can configure drill down at the row level or the column level.

To configure drill down for a row:

Right-click the row.

Select Drill down from the context menu. The Configure Drill Down dialog is displayed:

Select the dimension from the Drill down by drop-down list:

Click Save.

The row now displays an indicator showing that drill down is configured. When row-level drill down is configured, data collectors must provide data for all numeric and financial columns of that row for each member of the selected dimension.

You can configure drill down for columns with Numeric or Currency data types. To configure drill down for a column:

Right-click the column.

Select Drill down from the context menu. The Configure Drill Down dialog is displayed.

Select the dimension from the Drill down by drop-down list (Local account, Cost center, Partner, Transaction type, or Adjustment level).

Click Save.

When column-level drill down is configured, data collectors must provide data for that column for each member of the selected dimension, across all rows in the table.

Once drill down is configured for a row or column, you can add a second-level drill down to create hierarchies for more detailed data collection.

To add a second-level drill down:

Right-click the row or column that already has drill down configured.

Select Configure drill down from the context menu The Configure Drill Down dialog is displayed:

'Add second-level drill down' option is displayed. — 'Add second-level drill down' option

Click + Add second-level drill down.

Select the dimension for the second level from the drop-down list.

Click Save.

The row now displays indicators for both drill down levels. Data collectors must provide data for each combination of the selected dimensions.

A table with a second-level drill down is displayed. — Second-level drill down added

This section applies to Pivot table and CFP Pivot table elements. Reordering is only available while the form template is in Draft state.

You can change the display order of rows and columns in a pivot table or CFP pivot table by drag-and-drop. Rows can also be moved under a different superordinate row within the same table. Both operations only change the display structure — collected data, drill-down configurations, and aggregations are bound to row and column identifiers, not to their position, so no data is lost or recalculated unexpectedly.

You can drag any row to a new position among the rows at the same level (rows under the same superordinate row). The Total row is fixed at the bottom of the table and cannot be moved.

When you drag a superordinate row, all its subordinate rows move with it as a single unit. The internal superordinate–subordinate relationships within the moved subtree are preserved.

A horizontal line indicates the drop position during the drag. If you drop a row on an invalid target, it returns to its original position and an error message is shown.

You can drop a row onto another row to make it subordinate to that target row, changing its position in the hierarchy.

A row is being dragged onto another row to make it subordinate. — Moving a row to another superordinate row

When you drop a row on a valid target, a Move Row dialog is displayed showing the row you are moving, the source superordinate row, and the target superordinate row:

The 'Move Row' dialog is displayed. — 'Move Row' dialog

Review the details and click Move to commit the move.

After the move:

The aggregation of the source superordinate row decreases by the moved row's contribution.

The aggregation of the target superordinate row increases by the same amount.

The

Total

is unaffected — it aggregates the values of all the lowest-level rows regardless of hierarchy.

Drill-down configurations travel with the moved row and continue to work.

The following moves are not allowed:

Restriction	Reason
A row cannot be moved under one of its own subordinate rows.	Circular hierarchy.
A row cannot be moved under the Total row.	The Total row is fixed.
A manually-added CFP partner-based row cannot be moved under another CFP partner-based row.	CFP partner-based rows are never valid targets.
A row cannot be moved under a lowest-level row that already has drill down configured.	Rows with drill down cannot accept subordinate rows.
A row cannot be moved beyond the maximum supported hierarchy depth.	Depth limit.

You can drag any column to a new position within the table. All column types are draggable.

Exceptions:

The first column of the table is fixed.

The last action column is fixed.

When you move a Total column, it continues to reference its source columns by identifier. Moving the total column or its source columns has no effect on the calculated value.

Rows and columns can only be moved within their own table — moving them between tables is not supported.

Overview

Adding a CFP Pivot Table

Adding Columns

Empty Column

Period Value Type for CFP Import

Used for Postings

Based on Consolidation & Financial Planning Dimension

Value Query Columns

Total Column

Formula Column

Adding Rows

Empty Row

Total Row

Formula Row

Subordinate Row

Configuring Drill Down

Drill Down at Row Level

Drill Down at Column Level

Second-Level Drill Down

Reordering Rows and Columns

Reordering Rows

Moving Rows to Another Superordinate Row

Reordering Columns

Table of Contents

Company

Legal Notice

Table of Contents

Related Articles

Company

Legal Notice