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.
You have created a matrix and defined the basic settings for your matrix. For more information, see Creation of a Data Matrix.
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
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.
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
A large number of different characteristics can have a negative impact on the performance of the created report.
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).
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
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.
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.
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 .
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
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.
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.
You can display additional information for certain characteristics in any cell in the form of an attribute.
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.
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
.