Creating and Editing Columns
Last modified on 2024-07-01
Overview
An Excel file for Disclosure Management must always contain the following three column types to ensure that values are inserted in the Excel file or existing values are updated:
- Name columns
- Value columns
- Programming columns
This section explains what the individual column types mean and how to create them. You will also learn what entries can be used for the programming column.
This article contains the following sections:
Definitions
The following table shows the various obligatory column types and their meaning:
Column
Description
Name column
Name columns contain the row labels, i.e. the names of the individual report values, such as the names of items and accounts.
A name column has the following Properties:
- The name of a name column begins with name_.
- The name of a name column is given a sequential number.
Notation for a Name Column
Generally speaking, name columns follow the notation below:
name_sequential number
Example: name_1
Value column
Value columns contain the report values of the current document. In columns that are defined as value columns, values are added and updated after each data import and whenever the Excel file is updated.
A value column has the following properties:
- The name of a value column begins with value_.
- The name of a value column is given a sequential number and a PeriodID.
Notation for a Value Column
Generally speaking, value columns follow the notation below:
value_sequential number[_suffix]
Example: value_1_PAJ06
Programming column
The programming column is responsible for programming the contents of the value_ column. It contains information such as item numbers or account numbers from the source system and other details for calculating values in value columns.
A programming column has the following properties:
- The name of a programming column begins with prog_.
- The name of a programming column is given a sequential number and a PeriodID.
Notation of a programming column
Generally speaking, programming columns follow the notation below:
prog_sequential number_Suffix
Example: prog_2_PCURRENTYEAR
More information on the entries permitted in programming columns can be found in Entries Permitted in the Programming Column.
Creating Columns
You can create all columns using the Excel ribbon and the buttons shown in the following figure:
![Displays part of the Excel ribbon. The buttons for inserting columns are highlighted in a red border.](/adobe/dynamicmedia/deliver/dm-aid--09fe8ddb-14a1-4b28-a11a-ae080a706230/exribbon-insert-columns.png?preferwebp=true&quality=85)
To create columns:
To create a name column:
- Select the desired column in MS Excel.
- Click Name in the Excel ribbon.
- Click the desired entry in the displayed menu:
'Name' button with menu in the Excel ribbon
To create a value column in an Excel file:
- Select the desired column in MS Excel.
- Click Value in the Excel ribbon. In the Excel ribbon, menu items are available for the periods that have been created in the Manage Periods workspace.
- Click the desired entry in the displayed menu:
'Value' button with menu in the Excel ribbon
To create a programming column:
- Select the desired column in MS Excel.
- Click Prog in the Excel ribbon. In the menu, menu entries are available for all the Periods that have been created.
- Select the desired entry:
'Prog' button in the Excel ribbon
Index of Value and Programming Columns
The indexes (= sequential number) of value and programming columns are interdependent. The prog_1 column contains the instructions for filling the value_1 column (e.g. by specifying the corresponding item numbers); prog_2 contains the instructions for value_2, and so on.
For that reason, there must always be the same amount of programming columns as there are value columns.
Entries Permitted in the Programming Column
Programming
Description
#s[name] or sequential number
Selection as an Excel sum formula (only addition, subtraction, multiplication, and division as well as the Excel sum formula =SUM()), e.g. #s1, #s2, #s[EBIT].
#sf[name] or sequential number
Excel sum formula with a freely definable number of decimal places.
Sums that have been programmed using #sf are not rounded to the number of decimal places that were specified when checking in the Excel file. Instead, exactly the number of decimal places calculated as the result is applied.
#m[name] or sequential number
Selection as a manual report value, e.g. #m1, #m2, #m[numberemployees].
#mf[name] or sequential number
Manual report value with a freely definable number of decimal places.
Numbers that have been programmed using #mf are not rounded to the number of decimal places that were specified when checking in the Excel file. Instead, exactly the number of decimal places that the user has entered is applied.
#mt[name] or sequential number
Manual text value
Contents that have been programmed using #mt are saved as text and are not available for further calculation.
#l and sequential number
Selection as a blank row, e.g. #l1, #l2. Disclosure Management does not expect a value here and leaves the row blank in the data view. This is mainly used before headings in tables (e.g. assets).
#r[sequential number]
Reference to another report data record
#rf[sequential number]
Reference with a freely definable number of decimal places
Numbers that have been programmed using #rf are not rounded to the number of decimal places that were specified when checking in the Excel file. Instead, exactly the number of decimal places that the reference source contains is applied.
30768000
Value of account number 30768000.
-30768000
Inversion of the sign for the value of account number 30768000.
Depending on the formatting of the cell, MS Excel may not accept a leading minus sign. You can solve this by starting with an apostrophe, e.g. '-30768000.
30768000;30769000
Sum of the values from accounts 30768000 and 30769000
; (semicolon)
Separates item/account numbers
30768000;-30769000
30768000 minus 30769000
30111000#9999#CC-EV
53290000#SEK1
Subitems/transaction types/exchange rates, etc. are entered using # after item numbers.
Subsequent changes to designations in #m or #s programming can lead to problems with validations and references. Validations and references refer to the unique #m or #s programming. For example, if the sequential index referred to by a reference is changed from #m6 to #m7, the value of the cell in which the #m6 programming is stored will remain assigned to the reference.
Example of a Programming Column
An example for programming the prog_ column is shown in the following figure:
![Displays a P&L table containing accounts and values.](/adobe/dynamicmedia/deliver/dm-aid--57994200-d2b2-4185-b8a7-83dc099441c0/example-prog-column.png?preferwebp=true&quality=85)
Explanations for the Example
The following list contains explanations of the entries in the example table:
Row
Explanation
5
The entry 30100000 in row 5 of the prog_ column means that the cell [the report value] of the value_ columns in row 5 is filled with the value recorded at account/item number 30100000 in the source system.
The minus sign in front of the item number inverts the sign in the table view.
In the example, the entry -30100000 denotes the sum of revenue recorded as -59,882 units [here € million] in the source system and presented as €59,882 million in cell G5 for the current year.
12
The entry #s12 in cell K12 indicates that the corresponding report values of the value_ columns [so cells G12 and I12] are calculated using a sum formula.
The formula to be calculated – in this case =SUM(G7:G11) and =SUM(I7:I11) – is entered in cells G12 and I12 of the value_ columns.
The #s programming is the only possible entry in the prog_ column that requires an additional entry (input in the value cell) for calculation in the value_ column.
13
The entry -31010000;-31020000 in cell K13 instructs Disclosure Management to invert the sign of the values of accounts 31010000 and 31020000 to a minus sign in the corresponding cells of the value_ columns (G13 and I13), to add them together (separation of item numbers using ;), and to then present the sum as a rounded figure in accordance with standard commercial practice if necessary. In this way, as many item numbers as required can be added together or subtracted from each other to then present the resulting amount in the value_ column.
The account numbers to be added together are always separated by a semicolon.
If only a certain transaction type or the value of a subaccount is to be presented instead of the total amount of an account, this is done by specifying the desired limitation. The limitation [e.g. transaction type 300] is entered behind the item number using the # symbol as a separator, e.g. 30200000#300].
The transaction type must have been imported with the corresponding designation and stored in the Disclosure Management database.
25
The entry #m25 in cell K25 defines the entries in cells G25 and I25 as numbers to be entered manually by the user. This means that these report values will stay at 0 until a user defines and saves the value for the report values in the Data View or in Excel.
31, 32
The rows of the Excel table will only be displayed in the rounding form if a programming instruction has been entered in their prog_ column. If the rounding form is to include a heading or blank row in which no report value is generated or manually entered, this can be done using the entry #l. In this case too, the instructions must be given a sequential number like in the #s and #m programming instructions.
In the example presented, a special calculation operation is performed in rows 31 and 32, which is not to be recorded and managed by Disclosure Management. For that reason, the entries #l31 and #l32 are used in the programming column for these two rows. All entries in the corresponding rows of the value_ columns of the Excel table will not be displayed or evaluated in the Data View (e.g. not included in sums).
Sums programmed using #s cannot be read in via #l programming. In this case, Disclosure Management generates an error message because the values cannot be calculated as a result of the blank row.