Background documentationDefinition of a Column and Row Layout

 

You can define the row and column layout of a data entry form or report matrix by arranging characteristics and characteristic values on a worksheet.

Prerequisites

You have created a matrix and defined the basic settings for your matrix. For more information, see Creation of a Data Matrix.

Features

Row and Column Layout

You define characteristic values and insert them into the worksheet as a row and column layout. You can specify characteristics and their values in the following ways:

  • By manually inserting the characteristic values in the matrix

  • By selecting the characteristic values from proposal lists that are generated from master data and control parameters in the database

    Note Note

    The advantage of selecting values from lists of proposals is that Interactive Excel recognizes the reference of the cell in which a characteristic value is located and you do not need specify it explicitly. For more information, see Determination of Values for Characteristics.

    End of the note.

If you use proposals, you can display the key and/or the short text or long text in the matrix. If, in addition to the key, you use the long text, the long text automatically changes if you select a different characteristic value by entering a new key.

You can use the following types of characteristics to define the matrix layout:

  • Single values

    You can arrange single values in sequence, either horizontally or vertically.

    For more information, see Specifying Single Values.

  • Set list

    Series of single values arranged in sequence, either horizontally or vertically. The use of sets in Business Consolidation (SEM-BCS) is enabled when single selections are defined in the master data in the backend system.

    For more information, see Specifying Sets.

  • Set aggregation

    A single entry in the matrix definition summarizes data for all entries of a named single selection.

  • Hierarchy

    A series of single values arranged vertically with a structure of up to eight (8) levels. This feature is determined by the native grouping options provided by Microsoft Excel.

    You can insert an unlimited number of single values, sets, and hierarchies into your data matrix, and subsequently delete, move, format, or change them.

    For more information, see Specifying Hierarchies.

    Caution Caution

    A large number of different characteristics can have a negative impact on the performance of the created report.

    End of the caution.

    Recommendation Recommendation

    If you use more hierarchies for columns/rows, the display can combine both together. We recommend that you use one hierarchy for one direction (column/rows).

    End of the recommendation.

When you insert a single value or set aggregation into a cell on the worksheet, a help function for values is automatically set for this cell. You can display a list of possible values for the characteristic by positioning the cursor on the cell and using the function button that appears. This function enables you to change a characteristic value quickly and easily.

Caution Caution

If you subsequently change a characteristic value in a report matrix when connected to a database, the matrix is automatically refreshed with the appropriate database data. You can suppress automatic update by deselecting Refresh values automatically on the Data matrix tab.

End of the caution.

Caution Caution

When you define characteristics, take this into account that certain characteristics are dependent on others. The value you set for one characteristic could determine the values that Interactive Excel proposes for a second characteristic.

End of the caution.

When you use the Data matrix tab in Interactive Excel to specify values for characteristics that you want to insert into a matrix, you need to define any delimiting characteristics before generating a proposal list .

Characteristic Roles

Before you prepare a matrix for data entry, you need to define all required characteristics. Required characteristics are defined roles of a consolidation model, which are required for valid data selection. The following table shows the characteristic roles that are supported by Interactive Excel. Their dependencies are defined by the roles of the consolidation data model.

The following table shows the characteristic roles that are supported by Interactive Excel. Their dependencies are defined by the roles of the consolidation data model.

Role

Characteristic Required

Dependent On

Fixed in consolidation area

No

FS item

Yes

Cons chart of accounts

Version

Yes

Restatement

Characteristic required, if restatement is part of data model

Fiscal year

Yes

Period

Yes

Consolidation group

Single value

Characteristic required if reporting model is different from data model

Attributes

Hierarchy

“None”

or: -

no consolidation unit is specified

or: -

consolidated data (posting level 30)

Dimension

Version

Fiscal year

Dimension

Version

Fiscal year

Period

Consolidation unit

Single value

Attributes

Set

Hierarchy

Characteristic required, if no consolidation group is specified

Dimension Dimension Fiscal year Dimension Fiscal year Period Version Dimension Fiscal year Period Version

Partner unit

Single value/Set

Attributes

No

Dimension

Dimension

Fiscal year

Key figure

Yes

Subassignment

No

Unit of measure

No

Year of acquisition

No

Period of acquisition

No

Custom characteristic

No

Transaction currency

No

Document type

No

Dimension

Reference fiscal year

No

Reference period

No

Posting level

No

Key figure

Note Note

Data is aggregated for characteristics without a defined value. For example, if a receivables item has a breakdown by trading partner and transaction currency and you do not specify a value for the trading partner breakdown, the sum of all data records with a trading partner assignment is calculated (reporting) by the system or must be entered (data entry) by the user, for this item. If a row or a column contains no specified characteristics, it can be used for a user-defined entry, for example, in an Excel formula.

End of the note.

Note Note

The value context help of some characteristics displays a restricted value list only. This feature is deliberate to improve performance. If you need to specify a value that is not displayed in the list box, first select any value and then write the required value directly into the respective cell of the worksheet.

For example, it would be extremely time consuming to preselect and display all possible fiscal years from 0000 to 9999. Only twenty years around the current fiscal year are offered in the context value help for Business Consolidation (SEM-BCS). The years are displayed in EC-CS are the years for which transactional data has already been posted to the database.

End of the note.
Display of Additional Information

You can display additional information for certain characteristics in any cell in the form of an attribute.

Note Note

You can display the debit/credit sign of a financial statement item or the currency, text, or language of a company. This additional information is irrelevant for the import (reporting) and export (entry) of data because it has no influence on the matrix definition.

End of the note.

Activities

  • You make sure that values or value references are set for all the characteristics that you have defined, so that data can be saved correctly to or imported correctly from the database.

  • You specify any remaining required characteristics and check your global settings.

  • To check the data structure of the data matrix, on the Data matrix tab, click Check.