| Docs Help
  AppSource  
Docs  /  Excel Report Builder  /  Edit Evaluations  /  Data Sheets

 Field Settings


2024/10/24 • 7 min. to read
The Field Settings page is used to set up a field of a Data Sheet. Depending on the type of field, different field settings are available or possible.

Fields
FieldSpecifies for which field the settings are specified. Click on the field to change or remove the field no. If the field no. is removed, the field becomes a formula field.
Field TypeDisplays information about the field.
Field FiltersSpecifies whether FlowFilters that are valid only for the field are set for the field. Field Filters can only be set for FlowFields. Click on the field to set up the filters.

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.

Tip

With field filters you can, for example, set up an evaluation in which the Sales (LCY) field of a Customer is displayed once for the current month and once for the last month. The following example can be downloaded in the Download Area. To set up the evaluation:
  1. Add the Customer table to the Data Sheet.
  2. Add the fields No., Name and Sales (LCY) to the Data Sheet.
  3. Open the table settings of the Customer table and add the No. field as a filter.
  4. Return to the Data Sheet.
  5. Open the field settings of the Sales (LCY) field and enter '%1 Current Month' in the Name field.
  6. Open the field filters of the field and enter 55 (Date Filter) in the Field No. field and '[-CM]..[CM]' in the Filter field.
  7. Return to the Data Sheet.
  8. Copy the line you just set up using the Add action, option: Copy current line.
  9. Change the name of the copied line to '%1 Last Month'.
  10. Change the field filter of the copied line to '[-CM-1M]..[-CM-1D]'.
For more information, see Edit Evaluations, Data Sheets, Table Settings.
NameSpecifies a name for the field. If no name is specified, the default caption of the field is used.

Tip

It is possible to specify the placeholder %1 in the text, which is replaced by the default caption of the field. For example, if the Sales (LCY) field of a a Customer is set up to show the total for the last month, you could specify '%1 Last Month' in the name. The result would be 'Sales (LCY) Last Month'.
Add Table to NameSpecifies whether the name of the table should be added to the name of the field.
Formula IDSpecifies an ID that can be used to reference or access the field value in formulas. A Formula ID must begin with [ followed by a unique ID and end with ]. The brackets are added automatically when the ID is entered.
FormulaFormulas can be composed of Excel Functions, fixed and dynamic references (Formula IDs), constants and operators. However, it is also possible to simply specify a text, which is then written to the Excel cells when the evaluation is executed. If a calculation is to be performed in a formula, it begins with an equal sign. Click on the field to set up the formula. For more information, see Edit Evaluations, Data Sheets, Formulas.
Internal FieldSpecifies whether the field is used internally and should not be transferred to Excel. If a Formula ID is assigned to the internal field, the value of the internal field can be accessed in subsequent fields via a formula. To contain a value, internal fields must be specified before the formulas in which they are used. For more information, see Edit Evaluations, Data Sheets, Formulas.
Column HiddenSpecifies whether the column should be hidden. Regardless of whether there are parallel tree structures and thus several specifications for a column in the sheet, a column is hidden if any of these columns is defined as hidden.

Font, Fields
BoldSpecifies whether the column should be bold.
ItalicSpecifies whether the column should be italicized.
Fill ColorSpecifies whether the background of the column should be colored and if so, with which color. If white (RGB 255,255,255 or HEX #FFFFFF) is selected, the background of the column will not be colored. The selection can be canceled via the escape key.
Font ColorSpecifies whether the text of the column should be colored and if so, with which color. If black (RGB 0,0,0 or HEX #000000) is selected, the text of the column will not be colored. The selection can be canceled via the escape key.

Alignment, Fields
Horizontal AlignmentSpecifies the horizontal alignment for the column.
Width (Characters)Specifies the column width in Number of characters, based on the Calibri font in size 11. If there are several parallel tree structures and thus several specifications for a column in the sheet, the largest value is used.
Wrap TextSpecifies whether extra-long text should be wrapped into multiple lines.

Formatting, Fields
FormattingSpecifies how the column should be formatted in Excel.
  • If the Automatic option is selected, the column in Excel is formatted as follows:
    • Yes/No fields (Boolean) are converted to Excel Functions =TRUE() or =FALSE().
    • Numbers (Decimal, Integer, BigInteger) are formatted as Number with/without comma.
    • Date fields are formatted as Date.
    • All other fields are formatted as Text.

      • Note

      Unlike the Text option, Automatic saves the data in a standardized format in the workbook. This makes the data independent of the visualization and therefore also independent of language, region and country settings. When a user opens the workbook, the data is automatically displayed in the format he or she has set.
  • If the Text option is selected, the data will be formatted depending on the settings in the Text Formatting field and the column in Excel will be formatted as Text.
  • Formulas are basically always formatted as standard regardless of the formatting setting. Exceptions:
    • If the Format as Number field is set, the formula will be formatted as Number with/without comma.
    • If the Format as Date field is set, the formula will be formatted as Date.
    However, when the placeholder [%1] is used in a formula, the text formatting affects the field value by which the placeholder is replaced. For more information, see Edit Evaluations, Data Sheets, Formulas.
Text FormattingSpecifies whether a specific format should be used when formatting as text is selected. Depending on the type of field, different formatting options are available. A lookup can be used to select from all the options that Business Central provides for the field. The formatting parameters can still be changed manually afterwards.

Tip

For example, if you want to determine the year and the month name from a date field, it is best to use the text formatting for the date field. For more information, see Edit Evaluations, Tips & Tricks, Get month name from date.
Format Formula asSpecifies whether the formula or the result of the formula should be formatted in Excel as a number or as a date.
Decimal PlacesSpecifies the number of decimal places to display in Excel when automatic formatting is selected and the field is a decimal field, or when the formula is formatted as a number.
Without 1000 SeparatorSpecifies whether a thousands separator should be used or not.
Opposite SignSpecifies whether the sign of the field value should be reversed.

Various, Fields
No Column InheritanceSpecifies whether the column should be empty after a new row starts (e.g. for child tables). For more information, see Edit Evaluations, Data Sheets, Table Settings.
Data BarSpecifies whether a colored data bar should be added to the column to represent the value in a cell. The higher the value, the longer the bar. Regardless of whether there are parallel tree structures and thus several specifications for a column in the sheet, a data bar is added if any of these columns is defined as a data bar.
Excel Table Total Row TypeSpecifies what should be calculated for the field in the total row of an Excel Table if Excel Table is activated in the Sheet Settings of the Data Sheet. If there are several parallel tree structures and thus several specifications for a column in the sheet, the first tree structure or the first occurrence determines the calculation.

See also



Submit feedback for
DE|EN Imprint
◀☰▶
△