| Docs Help
  AppSource  
Print this pageEntire manual (printable version)
Docs  /  Excel Report Builder  /  Edit Evaluations  /  Data Sheets

 Table Settings


2025/04/01 • 11 min. to read
The Table Settings page is used to set up a table of a Data Sheet.

Fields
TableSpecifies for which table the settings are specified.
NameSpecifies a name for the table. If no name is specified, the default caption of the table is used. It is possible to specify the placeholder %1 in the text, which is replaced by the default caption of the table.
SortingSpecifies by which fields the records should be sorted.
OrderSpecifies whether the records should be sorted in ascending or descending order.
Disable Company ChangeSpecifies whether the Company table should perform a company change or not.
Max. No. of RecordsSpecifies a maximum number of results for a 1:n relationship. When the limit is reached, no more records are output.
Add Blank RecordSpecifies whether an empty record should also be added in a 1:n relationship. The setting can be useful especially in connection with child tables where the table is used for setting a FlowFilter.
Parallel Tree StructureSpecifies whether the tree structure should be output parallel to the previous tree structure. Normally tables and fields of a Data Sheet are handled serially. I.e. for each field another Excel column is added. Parallel tree structures can be used to output parts of a Data Sheet parallel to each other. The tree structures share the same columns and are output one below the other. Example The example can be downloaded in the Download Area. The following Data Sheet was created: The following result is displayed in Excel after executing the evaluation:

Tip

Folgende Formeln wurden für die Berechnung der Summen verwendet: Customer Total =SUMIF( [TC:COL]:[TC:COL] , "Customer" , [BC:COL]:[BC:COL] ) Vendor Total =SUMIF([ TV:COL]:[TV:COL] , "Vendor" , [BV:COL]:[BV:COL] )
Example 2 The example can be downloaded in the Download Area. The requirement in this example was to display all sales documents for a customer. The documents should be displayed indented by one field under the customers. Using a small "trick", such evaluations can also be implemented. The following Data Sheet was created: The trick is to subordinate the Customer table to the Customer table in a 1:1 relationship. After that, the Sales Header table can be output parallel to the subordinate customer. Both subordinated tables are linked to the superordinated customer table. The indentation is created with an empty formula field (Spacer). The following result is displayed in Excel after executing the evaluation:

Note

The following rules apply to parallel tree structures:
  • When executing the evaluation, parallel tree structures are only treated as such if they are defined directly one after the other and on the same level. The Parallel Tree Structure field only needs to be set in the following tree structure(s), not in the first one.
  • The number of fields do not have to match in the parallel tree structures. The tree structure with the most fields determines how many columns are output in Excel.
  • The column headers are determined by the first tree structure or, if more columns are defined in subsequent tree structures, the first occurrence.
  • Fields from a parallel tree structure cannot be added to a PivotTable directly but only indirectly via the first tree structure.
  • Since there are multiple specifications for a column in the sheet due to parallel tree structures, there are also multiple Field Settings for a field. Depending on the setting, either the setting of the first tree structure or the largest specified value is taken. There are also settings that become active as soon as they are set for one of the columns.
Disable 'Add Filter'Specifies whether additional filters can be added to the table in the Filter Page when the evaluation is executed. The filter page is displayed if filters are specified for a table that are not hidden (Filter Type).

Parent Table

If the table is subordinated to another table, additional table settings are available.
Parent TableDisplays the parent table of the table.
RelationshipSpecifies the relationship between the parent table and the table.
  • 1:n In a 1:n (one-to-many) relationship, a record in the parent table may be associated with multiple records in the child table. For example, each Customer can have several Customer Ledger Entries.
  • 1:1 In a 1:1 (one-to-one) relationship, a record in the parent table may be associated with only one record in the child table. For example, each Customer Ledger Entry is assigned to only one Customer.
  • First Record Only outputs the first record that is found.
  • Last Record Only outputs the last record that is found.
Output ConditionSpecifies whether records in the parent table should only be output if records in the child table exist or do not exist.
  • Child records Only outputs parent records if child records exist.
  • No child records Only outputs parent records if there are no child records.
  • At least (n) child records Only outputs parent records if there are a minimum number of child records. The minimum number n is specified in the Output Condition Value (n) field.
  • Not more than (n) child records Only outputs parent records if a maximum number of child records exist. The maximum number n is specified in the Output Condition Value (n) field.

Note

  • If a parent table has several child tables with output conditions, the records are only output if all conditions are met.
  • If the current (child) table also has child tables with output conditions, these conditions are not taken into account for the output decision of the current parent table.
  • The position of an output condition in the tree structure is irrelevant. However, if several output conditions exist, they are processed according to their position.
  • Output conditions are ignored for temporary data codeunits. For more information, see Information for Developers, Temporary Data Codeunits.
Example Only customers with current orders and at least two credit memos in the last 6 months should be listed. The example can be downloaded in the Download Area (Example: Table Settings, Output Condition).
Output Condition Value (n)Specifies the parameter for the output condition.
No Column InheritanceSpecifies whether the first record of the child table should also start with a new row.

Note

Individual columns can also be excluded from inheritance via the Field Settings of the parent table fields.
Example Customer 1:n Customer Ledger Entries with Column Inheritance Example Customer 1:n Customer Ledger Entries without Column Inheritance
Start with New RowSpecifies whether the columns of the parent table should be empty after the child table starts a new row.

Note

Regardless of the setting, a new row is always started after running through a 1:n relation or in the case of a parallel tree structure.
Example Customer 1:n Customer Ledger Entries without new row Example Customer 1:n Customer Ledger Entries with new row

Filters and Relations

In the Filters area you can specify filter criteria for the table and relations with parent tables. Only records that match the criteria are included in the evaluation. If you specify criteria for multiple fields, then only records that match all criteria will be displayed. In case of a Relation the field value of the parent record is used as filter criterion. A operator in the filter determines how the filter should be set. The Suggest Filters action can be used to suggest fields of the table and related fields between the table and the parent table. Existing filters will not be overwritten or changed.
FieldSpecifies which field should be filtered. If a field is specified here that is not hidden (Filter Type), a filter page is displayed later when the evaluation is executed. You can also simply specify a field without a filter so that additional filters can be added to the table via the filter page when the evaluation is executed. For more information, see Execute Evaluations, Evaluation Filters.
Related withSpecifies whether, and if so, to which field of a parent table the field of the table should be linked. In the case of a relation, the value of the field of the parent record is used as the filter and a operator for the relation (Equal, Less, Greater, etc.) must be specified in the Filter field. Click on the field to change or remove the relation.

Note

Tables can be linked to each other over several levels. Theoretically, one and the same table can be superordinated to another table more than once. In this case, the record from the lowest or most immediate parent is used for the relation. In the following example, when linking the detailed customer ledger entries to the customer, always the subordinated customer is used:
FilterSpecifies the filter for the field. The possible or allowed values for the filter depend on the field:
  • If the field is related with a parent field, the operator ('=', '<=', '<', '>=', '>', '<>') for the relation must be specified in the filter. The Filter values and operators can be selected via a lookup.
  • For date fields parameters can be specified in the filter, which will be converted or calculated only when the evaluation is executed. For more information, see Edit Evaluations, Data Sheets, Date Filter IDs.
  • The filters of option fields (and yes/no fields) are stored in the current display language. When executing the evaluation in another display language (or during a lookup of the filter), the filter is automatically converted.
It is also possible to specify Filter IDs in the filter, which are then replaced by the current filter values of the Filter IDs when the evaluation is executed. Filter IDs can be added to a filter via the Add Filter ID action. This opens a page that displays an overview of all available Filter IDs.

Note

  • A Filter ID cannot be added to the filter if the field is related with a parent field.
  • Multiple Filter IDs can be specified in the filter.
  • Date Filter IDs can be combined with Filter IDs.
  • The Filter Type must be Hidden if a Filter ID is specified in the filter.
For more information about entering filter criteria, see: [Microsoft Learn] Business Central - Sorting, Searching, and Filtering
Filter IDSpecifies an ID that can then be used in the filter of other lines to automatically set the same filter there. The Filter IDs in the filters are replaced by the current filter values of the Filter IDs when the evaluation is executed. A Filter ID must begin with [ followed by a unique ID and end with ]. The brackets are added automatically when the ID is entered.

Note

  • Filter IDs are not tied to a data sheet but cross data sheet.
  • A Filter ID cannot be specified if the field is related with a parent field.
  • A Filter ID cannot be specified for restricted filters.
Filter TypeSpecifies whether and how the filter can be changed when executing the evaluation.
  • Required Required filters must be specified by the user.
  • Restricted Restricted filters can only be further restricted by the user.
  • Locked Locked filters cannot be changed by the user.
  • Hidden Hidden filters are not displayed to the user. Relations are always created as hidden filters, because here the value of the field of the parent record is used as a filter.

    Tip

    If you want to have the possibility to add additional filters to the table when executing the evaluation, but you want certain fields to be excluded from this, you can also specify these fields as Hidden.
User FiltersSpecifies whether different filters for certain users are set for the field. Click on the field to set up the filters.

Field Filters

If FlowField fields are added to the table, FlowFilters set at the table level will affect the calculation of the fields. Via the Field Settings FlowFilters can be set, which are only valid for the field.

Note

If a FlowFilter is set on table level and on field level, the FlowFilter on table level is ignored for the calculation of the field.
For more information, see Edit Evaluations, Data Sheets, Field Settings.

Related information



Submit feedback for
DE|EN Imprint
◀☰▶
△