| Docs Help
  AppSource  
Print this pageEntire manual (printable version)
  The topic is not available for the selected language ().
Docs  /  Excel Report Builder  /  Edit Evaluations  /  Data Sheets

 Table Settings


2024/07/15 • 10 min. to read
The Table Settings page is used to set up a table of a Data Sheet. Depending on whether the table is a child table or not, different table settings are available or possible. In the Filter or Filters and Relations area you can specify filter criteria for the table and relations with parent tables.

Fields
Parent TableDisplays whether the table is a child table to a parent 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.
TableSpecifies for which table the settings are specified.
Temporary Data CodeunitWith the help of temporary data codeunits, evaluations can be extended with functions that temporarily calculate certain data of the evaluation during execution. This makes it possible, for example, to evaluate data that is normally not available or not available in this form. Temporary Data Codeunits can be provided or added via custom programming. For more information, see Information for Developers, Temporary Data Codeunits.
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. If the limit is reached, the search for further records is stopped.
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.
Start with New RowSpecifies whether the first record of the table should also start with a new row.

Note

Regardless of the setting, a new line 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
No Column InheritanceSpecifies whether the columns of the parent table should be empty after a new row starts.

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
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 This 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 This 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).

Filters and Relations

In the Filter or Filters and Relations area you can specify filter criteria for the table. 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.
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.

Actions

  • Suggest Filters and Relations

    Depending on the settings, this action suggests fields of the table and related fields between table and a parent table to filter the records. Existing filters will not be overwritten or changed.

  • Suggest Filters

    Depending on the settings, this action suggests fields of the table to filter the records. Existing filters will not be overwritten or changed.

Related information



Submit feedback for
DE|EN Imprint
◀☰▶
△