Excel Report Builder

Manual

☰ Contents

General

• Excel Report Builder
  Each Microsoft Dynamics 365 Business Central solution contains valuable information that can be used for strategic decisions...
• Role Center Integration
  Excel Report Builder is integrated into the Accountant and Accounting Manager roles, but can be accessed from any role via the Search function...

Setup

• Excel Report Builder Setup
  In the Excel Report Builder Setup page the general settings and defaults for the Excel Report Builder are defined...
• Excel Functions
  In the Excel Functions Setup page the Excel Functions are set up, which can then be selected in the Formulas of the Excel Evaluations...
• Evaluation Groups
  In the Excel Evaluation Groups page you can set up groups for the evaluations...
• Blocked Tables
  In the Blocked Tables page you can specify tables that are not allowed in Excel Evaluations. If a blocked table is used in an evaluation, the evaluation cannot be executed...

Edit Evaluations

• General

• • Edit Excel Evaluation
    Excel Evaluations are managed or created via the Excel Report Builder Evaluations page...

• Data Sheets

• • Data Sheet
    Via Data Sheets, sheets with data from tables can be added to the Excel workbook...
  • Sheet Settings
    The Sheet Settings page is used to set up the general settings of the Data Sheet...
  • Table Settings
    The Table Settings page is used to set up a table of a Data Sheet...
  • Date Filter IDs
    For date filters, IDs (placeholders) can be specified in square brackets [ ] that will be calculated and replaced based on the Date Filter Calculation Date when the evaluation is executed...
  • Field Settings
    The Field Settings page is used to set up a field of a Data Sheet...
  • Formulas
    Formulas 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...
  • Text Settings
    The Text Settings page is used to set up a text line of a Data Sheet...

• PivotTable Sheets

• • PivotTable
    The PivotTable page is used to set up the general settings of the PivotTable and the fields of the PivotTable...
  • Sheet Settings
    The Sheet Settings page is used to set up the general settings of the PivotTable Sheet...
  • Field Settings
    The PivotTable Field Settings page is used to set up a field of a PivotTable...

• Other Sheets

• • Text Sheet
    Via Text Sheets, sheets with text can be added to the Excel workbook...
  • Information Sheet
    Via the Information Sheet, a sheet with information about the evaluation can be added to the Excel workbook...

• Tips & Tricks

• • Output only if child records exist
  • Get month name from date
    If you want to determine the month name from a date field, it is best to use Text Formatting in the Field Settings of the date field...
  • Get data via sheet reference

• Extended

• • User Permissions
    The User Permission field on the evaluation card specifies whether the evaluation may be executed only by the user responsible for the evaluation, by all users, or only by a restricted group of users...
  • User Assignments
    The No. of User Assignments field on the evaluation card shows the number of users who have added the evaluation to their My Excel Evaluations list...
  • Copy, Export & Import
  • Download Area
    The Download Area provides a selection of free evaluations that you can download and use...

Execute Evaluations

• My Excel Evaluations
  In the My Excel Report Builder Evaluations page you can arrange and manage your personal list of Excel Evaluations that you are allowed to execute...
• Execute Excel Evaluation
  Via the Execute action, an evaluation can be executed. This creates an Excel workbook on your device...
• Evaluation Filters
  In the Excel Evaluation Filters page you can specify the filters for the execution of the evaluation...
• Archive Excel Evaluation
  Via this action, an evaluation can be archived. This will execute the evaluation and save the Excel workbook in the archive...
• Evaluation Archive
  The Excel Evaluation Archive page shows an overview of all archive entries of the evaluation and can be accessed directly via the evaluation, or via My Excel Evaluations...
• Analyze Data in Excel

Information for Developers

• Runnable Codeunits
  On the Excel Evaluation Card page, a Codeunit for the evaluation can be specified in the Codeunit to Run field. This can be used, for example, to display notes or to recalculate or update data directly before the evaluation is executed...
• Temporary Data Codeunits
  More information coming soon...
• Connector
  Via the codeunit NCE Connector Excel Evaluations can also be called or integrated from other extensions or from elsewhere...

Appendix

• System Requirements & Limits
  To execute an evaluation or create an Excel workbook, Excel is not required and therefore does not need to be installed...
• Comparison with BC Standard and NC Cube
  Would you like to know what advantages the Excel Report Builder offers compared to the Business Central Standard...
• Import old NC Cube files
  It is possible to import old NC Cube files (file name extension .nccx) via the import action. When importing an old NC Cube file, an attempt is made to import or convert as much information as possible from the file...
• NAVAX License Management
  The NAVAX License Management page (in older versions NAVAX License Overview or NCEX License Overview) displays the current license status of the NAVAX extensions...
• Installation Notes
• Release Notes

Docs  /  Excel Report Builder  /  General
 Excel Report Builder

Navigation

Tell me what you want to do

System Requirements & Limits

Comparison with Business Central Standard and NC Cube

Installation Notes

Docs  /  Excel Report Builder  /  General
 Role Center Integration

• The first part consists of the setup and editing of Excel Evaluations.
• The My Excel Report Builder Evaluations part is the part from where users usually execute Excel Evaluations. Here, each user can arrange and manage a personal list of Excel Evaluations. For more information, see Execute Evaluations, My Excel Evaluations.

Docs  /  Excel Report Builder  /  Setup
 Excel Report Builder Setup

Setup, Actions

• Excel Functions

  View or set up the Excel Functions for the Excel Evaluations. For more information, see Setup, Excel Functions.

• Evaluation Groups

  View or set up the Groups for the Excel Evaluations. For more information, see Setup, Evaluation Groups.

System, Actions

• NAVAX License Management

  Opens the NAVAX License Management which displays the current license status of the NAVAX extensions. For more information, see Appendix, NAVAX License Management.

• Blocked Tables

  Here you can specify tables that are not allowed in Excel Evaluations. For more information, see Setup, Blocked Tables.

• Translate all Excel Functions

  This action can be used to translate the Excel Function names in the Formulas of all evaluations into English Excel Function names after upgrading the old NC Cube version to Business Central.

  Note

  • Only users with the SUPER permission set can perform this action.
  • The action should only be executed once per client. If you translate German Formulas, for example, the option Replace Semicolons in Formulas is also set by default. If you run the translation again, the semicolons that were replaced by commas the first time will be incorrectly replaced by dots.

Docs  /  Excel Report Builder  /  Setup
 Excel Functions

Actions

• Download

  Via this action the Excel Functions can be downloaded and thus the setup can be created or updated quickly and easily.

• Import / Export

  Via Import and Export the Excel Functions can be imported from a file or exported to a file.

• Help

  Opens an external website that displays detailed information about the function in English.

• Help (Translated)

  Opens an external website that displays detailed information about the function in your display language.

• Function Overview

  Opens an external website that displays all functions in alphabetical order in English.

• Function Overview (Translated)

  Opens an external website that displays all functions in alphabetical order in your display language.

Docs  /  Excel Report Builder  /  Setup
 Evaluation Groups

Docs  /  Excel Report Builder  /  Setup
 Blocked Tables

Docs  /  Excel Report Builder  /  Edit Evaluations  /  General
 Edit Excel Evaluation

Actions

• Execute

  Executes the evaluation. This creates an Excel workbook on your device. For more information, see Execute Evaluations, Execute Excel Evaluation.

• Open in OneDrive

  Executes the evaluation, saves the Excel workbook in your Business Central folder in OneDrive and opens it in a new window so you can manage or share the file. The action is only available if OneDrive is available. For more information, see Execute Evaluations, Execute Excel Evaluation.

• Share

  Executes the evaluation, saves the Excel workbook in your Business Central folder in OneDrive and shares the file. You can also see who it's already shared with. The action is only available if OneDrive is available. For more information, see Execute Evaluations, Execute Excel Evaluation.

• Send by Email

  Executes the evaluation and prepares to send the Excel workbook by Email. For more information, see Execute Evaluations, Execute Excel Evaluation.

• Archive

  Executes the evaluation and saves the Excel workbook in the archive. For more information, see Execute Evaluations, Archive Excel Evaluation.

• Copy / Export / Import

  Creates a copy of the evaluation. Exports the evaluation to a file. Imports an evaluation from a file. For more information, see Edit Evaluations, Extended, Copy, Export & Import.

• Download Area

  Opens the Download Area from where you can download free evaluations. For more information, see Edit Evaluations, Extended, Download Area.

• Comments

  View the comments for the evaluation.

Sheets

• Data Sheet ⭮

  A sheet with tables and fields. For more information, see Edit Evaluations, Data Sheets, Data Sheet.

• PivotTable ⌗

  A PivotTable based on a Data Sheet. For more information, see Edit Evaluations, PivotTable Sheets, PivotTable.

• Text Sheet ≡

  A sheet with freely definable text. For more information, see Edit Evaluations, Other Sheets, Text Sheet.

• Information Sheet

  A sheet with information about the evaluation. For more information, see Edit Evaluations, Other Sheets, Information Sheet.

• Copy current sheet

  Creates a copy of the current sheet. Data Sheets can be copied with or without PivotTables.

• Insert sheet from...

  Can be used to insert a Data Sheet or a Text Sheet from another evaluation. Data Sheets can be copied with or without PivotTables.

Sheets, Actions

• Add

  Inserts a new sheet.

• Edit

  View or set up the sheet.

• Up / Down

  The position or the order of the sheets can be changed with Up and Down.

• Copy

  Inserts a copy of the current sheet.

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Data Sheets
 Data Sheet

Add

• Table ⭮

  Adds a top-level table or a new tree structure to the Data Sheet. The option is only available if no lines have been created yet or if a top level line is selected when calling the Add action. If the option is selected, an overview with all available tables opens. The Search icon at the top of the page can be used to search for a table. The Field Search action can be used to search for a specific field, similar to the table search. Both searches can also be combined. The selected table can be added to the Data Sheet with Ok and then displayed or set up via the Edit action or by clicking on the Settings field. For more information, see Edit Evaluations, Data Sheets, Table Settings.

• [Table Name] > Child Table ⭮

  Adds a child table to a table. The option is available if a table or field is selected when calling the Add action. Note: [Table Name] = Name of the table to which the option applies. The table is selected in the same way as for the Table ⭮ option. In addition, however, the following actions are available in the selection window:
  • Relations to the Table This action can be used to display only tables for which a simple relation to the table exists.
  • Relations from the Table This action can be used to display only tables to which a simple relation via fields from the table exists.

  Note

  • When a table is added as a child or parent table, related fields between child and parent table are automatically suggested. Check, add or change the suggested relations in the child table via the Table Settings.
  • If the same table is subordinated to the table once again (e.g.: Customer > Customer), this is suggested as a 1:1 relationship with the primary key fields as a filter.

• [Table Name] ⮌ Parent Table ⭮

  The option is similar to the [Table Name] > Child Table ⭮ option with the difference that it can be used to add a parent table to a table. The option is only available if a table is selected when calling the Add action.

• [Table Name] > Fields

  Adds fields to a table or a field with a related table. The option is available if a table or a field with a related table (indicated by the 🞥 symbol) is selected when calling the Add action. Note: [Table Name] = Name of the table to which the option applies. If the option is selected, an overview of all available fields of the table opens. The Search icon at the top of the page can be used to search for a field. In addition, various actions are available for filtering the fields.

  Tip

  You can add multiple fields to the Data Sheet at once by selecting multiple fields.
  The selected fields can be added to the Data Sheet with Ok and then displayed or set up via the Edit action or by clicking on the Settings field. For more information, see Edit Evaluations, Data Sheets, Field Settings.

  Note

  Field 🞥 If a field has a fixed 1:1 relationship to a table, it is marked with a 🞥 symbol. In this case, fields from the related table can be added to the Data Sheet directly and without additional table settings.

• [Table Name] > Formula Field

  Adds a formula field to a table or a field with a related table. The option is available if a table or a field with a related table (indicated by the 🞥 symbol) is selected when calling the Add action. Note: [Table Name] = Name of the table to which the option applies. Formulas 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. The formula can be set up by clicking on the formula field. For more information, see Edit Evaluations, Data Sheets, Formulas.

• Text Line ≡

  Adds a text line to the Data Sheet. Text lines are only allowed on top level and the option is only available if no lines have been created yet or if a top level line is selected when calling the Add action. Via text lines e.g. general descriptions can be added to the Data Sheet. The text line can be displayed or set up via the Edit action or by clicking on the settings field. For more information, see Edit Evaluations, Data Sheets, Text Settings. Text lines can also be used in combination with formulas to specify parameters for calculations or to perform calculations over the entire Data Sheet. A simple example with a text line as parameter for a limit value can be found in Example 1 at Edit Evaluations, Data Sheets, Formulas. Another example, where text lines are used for sum calculations, can be found in the example on parallel tree structures at Edit Evaluations, Data Sheets, Table Settings. The formula of a text line can be set up by clicking on the formula field. For more information, see Edit Evaluations, Data Sheets, Formulas.

• Copy current line

  Creates a copy of the current line. The option is available if a field, a formula field or a text line is selected when calling the Add action.

• Company Loop 🗲

  Adds the 20000006 ∙ Company table to the Data Sheet to set up cross-company evaluation. When running through the records of the table, a company change is performed for all child tables. The company change can also be deactivated in the table settings if required. The option is available via the More Options... selection if a top-level line is selected when calling the Add action. If a table is selected when the Add action is called, the [Table Name] ⮌ Parent Company Loop 🗲 variant is available and the Company Loop is added as a parent table to the table.

  Tip

  As an example, you can download the evaluation Inventory by Company in the Download Area, which includes a company loop. For more information, see Edit Evaluations, Extended, Download Area.

• Date Loop 🗲

  Adds the 20000007 ∙ Date table to the Data Sheet. A date loop can be useful in connection with child tables, for example, if the date is used for setting a FlowFilter. For the date table it is mandatory to specify a filter for the Period Type and a range filter for the Period Start in the table settings. The option is available via the More Options... selection if a top-level line is selected when calling the Add action. If a table is selected when the Add action is called, the [Table Name] ⮌ Parent Date Loop 🗲 variant is available and the Date Loop is added as a parent table to the table.

  Tip

  As an example, you can download the evaluation Resource Load in the Download Area, which includes a date loop. For more information, see Edit Evaluations, Extended, Download Area.

• Temporary Data Codeunit 🗲

  Adds a Temporary Data Codeunit including its fields and filters to the Data Sheet. The option is available via the More Options... selection if a top-level line is selected when calling the Add action. If a table is selected when the Add action is called, the [Table Name] > Temporary Data Codeunit 🗲 variant is available. With 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. For more information, see Information for Developers, Temporary Data Codeunits.

• [Table Name] Notes ⭮ / Links ⭮

  Adds the 2000000068 ∙ Record Link table to a table in the Data Sheet to output the notes or links of the records. The option is available via the More Options... selection if a table is selected when the Add action is called. Note: [Table Name] = Name of the table to which the option applies. For most records, notes and links can be added in the cards, documents and lists via a FactBox. The notes and links are stored in the Record Link table and are related to the records via a RecordID. The option adds the table and fields and sets all necessary filters and relations.

• Insert Data Sheet from...

  Can be used to add the lines of a Data Sheet to the current Data Sheet. Data sheets can also be copied into themselves. The option is available via the More Options... selection.

Fields

Table/Field	Displays information about the line. The following symbols are displayed or used in the line information: ⭮ Indicates that this is a table. 🗲 Indicates that a special action is performed for the table. Field 🞥 If a field has a fixed 1:1 relationship to a table, it is marked with a 🞥 symbol. In this case, fields from the related table can be added to the Data Sheet directly and without additional table settings. [Field] A field in square brackets indicates that it is an internal field. ≡ Indicates that this is a text line.
Settings	Displays the settings of the line. Click on the field to view or edit the settings. For more information, see Edit Evaluations, Data Sheets, Table Settings, Edit Evaluations, Data Sheets, Field Settings and Edit Evaluations, Data Sheets, Text Settings.
Formula ID	Specifies 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.
Formula	Formulas 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.
Part of a parallel Tree Structure	Specifies whether the line is part of a parallel tree structure. For more information, see Edit Evaluations, Data Sheets, Table Settings.
PivotTable Field	Specifies whether the field is used in a PivotTable. For more information, see Edit Evaluations, PivotTable Sheets, PivotTable.

Actions

• Add

  Inserts a new line. Depending on the selected line from which the action is called, different options are available.

• Edit

  View or set up the line. For more information, see Edit Evaluations, Data Sheets, Table Settings, Edit Evaluations, Data Sheets, Field Settings and Edit Evaluations, Data Sheets, Text Settings.

• Up / Down

  The position or the order of the tables and fields can be changed with Up and Down.

• Subordinate

  If several tables are on the same level, this action can be used to subordinate a table to another table or to move it down one level.

• Superordinate

  If a tables is defined as a child table, this action can be used to remove the subordination or to move it up one level.

• Copy

  Inserts a copy of the current line.

• Sheet Settings

  Displays the general settings of the Data Sheet. For more information, see Edit Evaluations, Data Sheets, Sheet Settings.

• Execute

  Executes the evaluation. This creates an Excel workbook on your device. For more information, see Execute Evaluations, Execute Excel Evaluation.

Deleting a line

• remove only the table and its fields In this case, all subordinate tables and their fields will be preserved and moved up one level.
• delete all In this case, the entire child tree structure is deleted.

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Data Sheets
 Sheet Settings

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Data Sheets
 Table Settings

Fields

Parent Table	Displays whether the table is a child table to a parent table.
Relationship	Specifies 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.
Table	Specifies for which table the settings are specified.
Temporary Data Codeunit	With 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.
Name	Specifies 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.
Sorting	Specifies by which fields the records should be sorted.
Order	Specifies whether the records should be sorted in ascending or descending order.
Disable Company Change	Specifies whether the Company table should perform a company change or not.
Max. No. of Records	Specifies a maximum number of results for a 1:n relationship. If the limit is reached, the search for further records is stopped.
Add Blank Record	Specifies 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 Row	Specifies 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 Inheritance	Specifies 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 Structure	Specifies 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

Field	Specifies 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 with	Specifies 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:
Filter	Specifies 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 ID	Specifies 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 Type	Specifies 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 Filters	Specifies whether different filters for certain users are set for the field. Click on the field to set up the filters.

Field Filters

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.

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Data Sheets
 Date Filter IDs

• D Day
• WD Weekday
• W Week
• M Month
• Q Quarter
• Y Year
• C meaning 'current' and can be specified as a prefix of an ID

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Data Sheets
 Field Settings

Fields

Field	Specifies 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 Type	Displays information about the field.
Field Filters	Specifies 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: Add the Customer table to the Data Sheet. Add the fields No., Name and Sales (LCY) to the Data Sheet. Open the table settings of the Customer table and add the No. field as a filter. Return to the Data Sheet. Open the field settings of the Sales (LCY) field and enter '%1 Current Month' in the Name field. Open the field filters of the field and enter 55 (Date Filter) in the Field No. field and '[-CM]..[CM]' in the Filter field. Return to the Data Sheet. Copy the line you just set up using the Add action, option: Copy current line. Change the name of the copied line to '%1 Last Month'. Change the field filter of the copied line to '[-CM-1M]..[-CM-1D]'. For more information, see Edit Evaluations, Data Sheets, Table Settings.
Name	Specifies 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 Name	Specifies whether the name of the table should be added to the name of the field.
Formula ID	Specifies 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.
Formula	Formulas 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 Field	Specifies 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 Hidden	Specifies 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.

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Data Sheets
 Formulas

Excel Formula / Excel Function

https://support.microsoft.com/en-us/office/294d9486-b332-48ed-b489-abe7d0f9eda9

Fixed References

https://support.microsoft.com/en-us/office/c7b8b95d-c594-4488-947e-c835903cebaa

Dynamic References & Formula IDs

Custom Formula IDs

Example 1

Example 2

• [B:COL] will be replaced e.g. by the Excel column no. of the Balance (LCY) field.
• [B:ROW] will be replaced e.g. by the Excel row no. of the Balance (LCY) field.

Version 1:

Version 2:

Formula Help

Translate

Additional Settings

• Format Formula as

  Specifies whether the formula or the result of the formula should be formatted in Excel as a number or as a date.

• Decimal Places

  Specifies the number of decimal places to display in Excel when the formula is formatted as a number.

• Without 1000 Separator

  Specifies whether a thousands separator should be used or not.

• Array Formula

  If you want the formula to become an array formula in Excel, you need to set the Array Formula field. For more information about array formulas, see: https://support.microsoft.com/en-us/office/e43e12e0-afc6-4a12-bc7f-48361075954d

Excel Formula errors

We found a problem with some content in '...xlsx'. Do you want us to try to recover as much as we can?

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Data Sheets
 Text Settings

Docs  /  Excel Report Builder  /  Edit Evaluations  /  PivotTable Sheets
 PivotTable

Data Sheet Fields

Actions

• Data Sheet Fields can be added to the PivotTable via the Add action or by clicking on the field.

  Note

  • Text lines and internal fields cannot be added to a PivotTable and are therefore not displayed.
  • Fields from a parallel tree structure cannot be added to a PivotTable directly but only indirectly via the first tree structure. Parallel tree structures are displayed red + italic for guidance.

PivotTable Fields

Actions

• Via the Settings action or by clicking on the field, the settings for the field can be displayed or set up. For more information, see Edit Evaluations, PivotTable Sheets, Field Settings.
• The Move to action moves the field to another area.
• The position or the order of the fields can be changed with Up and Down.

Docs  /  Excel Report Builder  /  Edit Evaluations  /  PivotTable Sheets
 Sheet Settings

Layout, Fields

Classic PivotTable Layout	Specifies whether the PivotTable should be created in Classic Layout by default. The Classic Layout enables dragging of fields in the grid. The default setting for the field can be set in the Excel Evaluation Setup.
Grand Totals	Specifies whether to display the grand totals next to the last column resp. at the bottom of the PivotTable. The grand total for rows is displayed next to the last column: The grand total for columns is displayed at the bottom of the PivotTable:

Chart, Fields

Chart	Specifies whether a chart should be created for the PivotTable. The chart will be created in a separate sheet.
Chart Sheet Name	Specifies a name for the chart sheet. If no name is specified, the type of the sheet is used. It is additionally possible to specify the placeholder %1 in the text, which is replaced by the type of the sheet.
Chart Title	Specifies a title for the chart. A title will only be added to the chart if the field contains a value.
Chart Type	Specifies the chart type.

Docs  /  Excel Report Builder  /  Edit Evaluations  /  PivotTable Sheets
 Field Settings

Layout, Fields

Form	Specifies how the field should be displayed when used as a Row Label. Note The setting is ignored for classic PivotTable layouts. The Compact Form displays items from different row area fields in one column. Indents are used to clarify the difference between items from different fields. Row labels take up less space in compact form, which leaves more room for numeric data. Expand and Collapse buttons are displayed so that you can display or hide details in compact form. The Compact form saves space and makes the PivotTable more readable and is therefore specified as the default layout form for PivotTables. The Tabular Form displays one column per field and provides space for field headers. The Outline Form is similar to Tabular Form but it can display subtotals at the top of every group because items in the next column are displayed one row below the current item.
Subtotals at the top of each group	Specifies whether to display the subtotals in the Compact Form and Outline Form above or below the subtotaled rows.
Repeat item labels	Specifies whether to repeat the item and field labels in a PivotTable to visually group the rows or columns together. Repeated labels are shown only when the PivotTable is in Tabular Form. For example, use repeating labels when subtotals are turned off or there are multiple fields for items. In the example shown below, the regions are repeated for each row and the product is repeated for each column.
Blank line after each item label	Specifies whether a blank line should be inserted after each item.

Values, Fields

Summarize Values By	Specifies whether and how the values should be summarized. For more information about sum values in a PivotTable, see: https://support.microsoft.com/en-us/office/9ee73790-646a-42c9-9fc7-e1ca30096d9c
Show Values As	Specifies if the values should be shown in a different way or calculation. For more information about different calculations in PivotTable value fields, see: https://support.microsoft.com/en-us/office/014d2777-baaf-480b-a32b-98431f48bfec

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Other Sheets
 Text Sheet

Actions

• Up / Down

  The position or the order of the text lines can be changed with Up and Down.

• Sheet Settings

  Displays the general settings of the Text Sheet.

• Execute

  Executes the evaluation. This creates an Excel workbook on your device. For more information, see Execute Evaluations, Execute Excel Evaluation.

Sheet Settings

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Other Sheets
 Information Sheet

Fixed Information

• Description of the evaluation
• Code of the evaluation
• Date/Time

Actions

• Execute

  Executes the evaluation. This creates an Excel workbook on your device. For more information, see Execute Evaluations, Execute Excel Evaluation.

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Tips & Tricks
 Output only if child records exist

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Tips & Tricks
 Get month name from date

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Tips & Tricks
 Get data via sheet reference

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Extended
 User Permissions

All Users

Restricted Group

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Extended
 User Assignments

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Extended
 Copy, Export & Import

Copy

• Copy from

  Code	Specifies the code of the existing evaluation to copy from.
  Description	Specifies the description of the existing evaluation.

  Copy to

  Code	Specifies the code for the new evaluation.
  Code already Exists	Specifies whether an evaluation with the code already exists. An additional confirmation is necessary for replacing an evaluation.
  Description	Specifies a description for the new evaluation.

  Copy, Options

  Include User Permissions	Specifies whether the evaluation should be copied with or without User Permissions. For more information, see Edit Evaluations, Extended, User Permissions.
  Keep User Permissions	Specifies whether existing User Permissions of an evaluation should be retained when replacing an evaluation.
  Include User Assignments	Specifies whether the evaluation should be copied with or without User Assignments. For more information, see Edit Evaluations, Extended, User Assignments.
  Keep User Assignments	Specifies whether existing User Assignments of an evaluation should be retained when replacing an evaluation.
  Include User Filters	Specifies whether the evaluation should be copied with or without User Filters.

Export

• Export from

  Code	Specifies the code of the evaluation to be exported.
  Description	Specifies the description of the evaluation to be exported.

  Export to

  File Name	Specifies the name for the file. Note Excel Report Builder files have the file extension .nce. NCE stands for NAVAX Consulting Excel Report Builder.
  Export without Code	Specifies whether the evaluation should be exported without code. If the code is not exported, a code for the evaluation must be specified when the file is imported later.

  Export, Options

  Include User Permissions	Specifies whether the evaluation should be exported with or without User Permissions. For more information, see Edit Evaluations, Extended, User Permissions.
  Include User Assignments	Specifies whether the evaluation should be exported with or without User Assignments. For more information, see Edit Evaluations, Extended, User Assignments.
  Include User Filters	Specifies whether the evaluation should be exported with or without User Filters.

Import

• Import from

  Description

  Specifies the description of the evaluation to be imported. It is also possible to import old NC Cube files. For more information, see Appendix, Import old NC Cube files.

  Import to

  Code	Specifies the code for the new evaluation.
  Code already Exists	Specifies whether an evaluation with the code already exists. An additional confirmation is necessary for replacing an evaluation.

  Import, Options

  Include User Permissions	Specifies whether the evaluation should be imported with or without User Permissions. For more information, see Edit Evaluations, Extended, User Permissions.
  Keep User Permissions	Specifies whether existing User Permissions of an evaluation should be retained when replacing an evaluation.
  Include User Assignments	Specifies whether the evaluation should be imported with or without User Assignments. For more information, see Edit Evaluations, Extended, User Assignments.
  Keep User Assignments	Specifies whether existing User Assignments of an evaluation should be retained when replacing an evaluation.
  Include User Filters	Specifies whether the evaluation should be imported with or without User Filters.
  Include non-existent Users	Specifies whether the user-related settings should also be imported for users that do not exist. Note If the option is enabled, the data should be manually revised and assigned to the correct users after the import.

Docs  /  Excel Report Builder  /  Edit Evaluations  /  Extended
 Download Area

Actions

• Download

  Via this action the evaluation can be downloaded and created. This will open the import page where further settings can be made.

• Preview

  Opens an external website that displays a preview (image or video) of the evaluation.

Docs  /  Excel Report Builder  /  Execute Evaluations
 My Excel Evaluations

Actions

• Execute

  Executes the evaluation. This creates an Excel workbook on your device. For more information, see Execute Evaluations, Execute Excel Evaluation.

• Open in OneDrive

  Executes the evaluation, saves the Excel workbook in your Business Central folder in OneDrive and opens it in a new window so you can manage or share the file. The action is only available if OneDrive is available. For more information, see Execute Evaluations, Execute Excel Evaluation.

• Share

  Executes the evaluation, saves the Excel workbook in your Business Central folder in OneDrive and shares the file. You can also see who it's already shared with. The action is only available if OneDrive is available. For more information, see Execute Evaluations, Execute Excel Evaluation.

• Send by Email

  Executes the evaluation and prepares to send the Excel workbook by Email. For more information, see Execute Evaluations, Execute Excel Evaluation.

• Archive

  Executes the evaluation and saves the Excel workbook in the archive. For more information, see Execute Evaluations, Archive Excel Evaluation.

• Add

  Inserts selected evaluations after the current position. The evaluation can be selected via a page that displays all Excel Evaluations that you are allowed to execute and that are not yet included in your list.

• Up / Down

  The position or the order of the evaluations can be changed with Up and Down.

• Comments

  View the comments for the evaluation.

Docs  /  Excel Report Builder  /  Execute Evaluations
 Execute Excel Evaluation

OneDrive Integration

https://learn.microsoft.com/en-us/dynamics365/business-central/across-onedrive-overview

Docs  /  Excel Report Builder  /  Execute Evaluations
 Evaluation Filters

Docs  /  Excel Report Builder  /  Execute Evaluations
 Archive Excel Evaluation

Docs  /  Excel Report Builder  /  Execute Evaluations
 Evaluation Archive

Actions

• Open

  Opens the archived Excel workbook. This creates an Excel workbook on your device.

• Open in OneDrive

  Saves the archived Excel workbook in your Business Central folder in OneDrive and opens it in a new window so you can manage or share the file. The action is only available if OneDrive is available.

• Share

  Saves the archived Excel workbook in your Business Central folder in OneDrive and shares the file. You can also see who it's already shared with. The action is only available if OneDrive is available.

• Send by Email

  Prepares to send the archived Excel workbook by Email.

Docs  /  Excel Report Builder  /  Execute Evaluations
 Analyze Data in Excel

https://support.microsoft.com/en-us/office/3223aab8-f543-4fda-85ed-76bb0295ffc4

Docs  /  Excel Report Builder  /  Information for Developers
 Runnable Codeunits

• OnShowExecutePage When the execute page is displayed. This position is particularly suitable for displaying users general information about the evaluation. Example:
• OnShowFilterPage When the filter page is displayed. Similar to OnShowExecutePage only directly after the execute page. The call is made even if no filter page is displayed.
• OnBeforeExecute Before the evaluation is executed. This position is particularly suitable for displaying a confirmation and/or for recalculating/updating data.

Example 1

Implementation

codeunit 70171837 "NCE Show Schedule Message"
{
    trigger OnRun()
    begin
        if (CurrentClientType = ClientType::Background) then
            exit;
        Message(ScheduleMsg);
    end;

    var
        ScheduleMsg: Label 'This evaluation should be scheduled.\As soon as the Excel workbook has been created, a message appears in the Report Inbox.', Comment = 'DEU="Diese Auswertung sollte als Plan ausgeführt werden.\Sobald die Excel-Arbeitsmappe erstellt wurde, erscheint eine Meldung im Berichtseingang."';

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"NCE Runnable Codeunit Mgt.", 'OnAddAllowedCodeunitsToRun', '', false, false)]
    local procedure NCERunnableCodeunitMgt_OnAddAllowedCodeunitsToRun(var TempNCERunnableCodeunit: Record "NCE Runnable Codeunit" temporary)
    var
        HelpTxt: Label 'This Codeunit causes the following additional message to be displayed when the evaluation is executed:\\', Comment = 'DEU="Diese Codeunit bewirkt, dass beim Ausführen der Auswertung zusätzliche folgende Meldung angezeigt wird:\\"';
    begin
        TempNCERunnableCodeunit.Add(Codeunit::"NCE Show Schedule Message", HelpTxt + '"' + ScheduleMsg + '"', "NCE Codeunit Call Location"::OnShowExecutePage);
    end;
}

• CodeunitID

  Important

  The first parameter CodeunitID determines which Codeunit it is.

  TempNCERunnableCodeunit.Add(Codeunit::"NCE Show Schedule Message", ...

  The value must match the name of the Codeunit.
• CodeunitHelpText This text is displayed when selecting the Codeunit to Run in the Excel Evaluation Card. Information about the purpose and usage of the Codeunit should be provided here.
• CodeunitCallLocation Determines where the Codeunit is called.

Example 2

Implementation

codeunit 70171836 "NCE Confirm Long Run Time"
{
    trigger OnRun()
    var
        ConfirmManagement: Codeunit "Confirm Management";
    begin
        if (CurrentClientType = ClientType::Background) then
            exit;
        if not ConfirmManagement.GetResponseOrDefault(LongRuntimeMsg, true) then
            Error('');
    end;

    var
        LongRuntimeMsg: Label 'It will take a longer time to execute this evaluation.\Do you want to execute the evaluation anyway?', Comment = 'DEU="Das Ausführen dieser Auswertung wird länger dauern.\Möchten Sie die Auswertung trotzdem ausführen?"';

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"NCE Runnable Codeunit Mgt.", 'OnAddAllowedCodeunitsToRun', '', false, false)]
    local procedure NCERunnableCodeunitMgt_OnAddAllowedCodeunitsToRun(var TempNCERunnableCodeunit: Record "NCE Runnable Codeunit" temporary)
    var
        HelpTxt: Label 'This Codeunit causes the following additional confirmation to be displayed when the evaluation is executed:\\', Comment = 'DEU="Diese Codeunit bewirkt, dass beim Ausführen der Auswertung zusätzliche folgende Bestätigung angezeigt wird:\\"';
    begin
        TempNCERunnableCodeunit.Add(Codeunit::"NCE Confirm Long Run Time", HelpTxt + '"' + LongRuntimeMsg + '"', "NCE Codeunit Call Location"::OnBeforeExecute);
    end;
}

Docs  /  Excel Report Builder  /  Information for Developers
 Temporary Data Codeunits

Docs  /  Excel Report Builder  /  Information for Developers
 Connector

• DownloadEvaluation - creates the Excel workbook on your device.
• GetEvaluation - works in principle like DownloadEvaluation with the difference that the finished Excel workbook is not downloaded, but returned as a Temp Blob. In addition, the file name that the Excel Report Builder would assign to the workbook is returned.

Example 1

Implementation

pageextension 50000 "NVX Customer List" extends "Customer List"
{
    actions
    {
        addfirst(Sales)
        {
            action(NVXCallNCEEvaluationAction)
            {
                ApplicationArea = All;
                Caption = 'Excel Evaluation', Comment = 'DEU="Excel-Auswertung"';
                Image = Start;
                Scope = Repeater;
                ToolTip = 'Executes the CUST_ONGOING_DOCS evaluation. This creates an Excel workbook on your device.', Comment = 'DEU="Führt die CUST_ONGOING_DOCS Auswertung aus. Dadurch wird eine Excel-Arbeitsmappe auf Ihrem Gerät erstellt."';

                trigger OnAction()
                var
                    NCEConnector: Codeunit "NCE Connector";                
                begin
                    NCEConnector.DownloadEvaluation('CUST_ONGOING_DOCS');
                end;
            }
        }
    }
}

Example 2

Implementation

pageextension 50000 "NVX Customer List" extends "Customer List"
{
    actions
    {
        addfirst(Sales)
        {
            action(NVXCallNCEEvaluationAction)
            {
                ApplicationArea = All;
                Caption = 'Excel Evaluation', Comment = 'DEU="Excel-Auswertung"';
                Image = Start;
                Scope = Repeater;
                ToolTip = 'Executes the CUST_ONGOING_DOCS evaluation. This creates an Excel workbook on your device.', Comment = 'DEU="Führt die CUST_ONGOING_DOCS Auswertung aus. Dadurch wird eine Excel-Arbeitsmappe auf Ihrem Gerät erstellt."';

                trigger OnAction()
                var
                    TempNCEConnectorTableFilter: Record "NCE Connector Table Filter" temporary;
                    NCEConnector: Codeunit "NCE Connector";                
                begin
                    Clear(TempNCEConnectorTableFilter);
                    TempNCEConnectorTableFilter."Table No." := Database::"Sales Header";
                    TempNCEConnectorTableFilter.Insert();

                    NCEConnector.DownloadEvaluation('CUST_ONGOING_DOCS', TempNCEConnectorTableFilter);
                end;
            }
        }
    }
}

Example 3

Implementation

pageextension 50000 "NVX Customer List" extends "Customer List"
{
    actions
    {
        addfirst(Sales)
        {
            action(NVXCallNCEEvaluationAction)
            {
                ApplicationArea = All;
                Caption = 'Excel Evaluation', Comment = 'DEU="Excel-Auswertung"';
                Image = Start;
                Scope = Repeater;
                ToolTip = 'Executes the CUST_ONGOING_DOCS evaluation. This creates an Excel workbook on your device.', Comment = 'DEU="Führt die CUST_ONGOING_DOCS Auswertung aus. Dadurch wird eine Excel-Arbeitsmappe auf Ihrem Gerät erstellt."';

                trigger OnAction()
                var
                    SalesHeader: Record "Sales Header";
                    TempNCEConnectorTableFilter: Record "NCE Connector Table Filter" temporary;
                    NCEConnector: Codeunit "NCE Connector";                
                begin
                    Rec.TestField("No.");

                    Clear(TempNCEConnectorTableFilter);
                    TempNCEConnectorTableFilter."Table No." := Database::"Sales Header";
                    TempNCEConnectorTableFilter."Field No." := SalesHeader.FieldNo("Document Type");
                    TempNCEConnectorTableFilter.Filter := StrSubstNo('%1|%2', SalesHeader."Document Type"::Quote, SalesHeader."Document Type"::Order);
                    TempNCEConnectorTableFilter.Insert();
                    
                    Clear(TempNCEConnectorTableFilter);
                    TempNCEConnectorTableFilter."Table No." := Database::"Sales Header";
                    TempNCEConnectorTableFilter."Field No." := SalesHeader.FieldNo("Sell-to Customer No.");
                    TempNCEConnectorTableFilter.Filter := Rec."No.";
                    TempNCEConnectorTableFilter.Insert();

                    NCEConnector.DownloadEvaluation('CUST_ONGOING_DOCS', TempNCEConnectorTableFilter);
                end;
            }
        }
    }
}

Filter

Docs  /  Excel Report Builder  /  Appendix
 System Requirements & Limits

Recommended

Alternatives

• Excel 2013, Excel 2010

• Excel 2007

  • No slicers in PivotTables available

• OpenOffice

  • No slicers in PivotTables available
  • More limited graphical display options

• Android

  For more information, see Install and set up Office on an Android and Excel for Android Phones resp. Excel for Android Tablets

• iPhone or iPad

  For more information, see Install and set up Office on an iPhone or iPad and Excel for iPhone resp. Excel for iPad

Limits

Excel specifications and limits

• A maximum of 500 Excel Columns can be defined per Data Sheet.

Docs  /  Excel Report Builder  /  Appendix
 Comparison with BC Standard and NC Cube

In the following list you will find a comparison with the Business Central Analysis mode, the Business Central Export to Excel functionality and the Dynamics NAV AddOn NC Cube.

	Excel Report Builder	Analysis mode	Export to Excel	NC Cube
Availability	as of BC 14	as of BC 23	all versions	only for NAV
Compatibility	Excel, OpenOffice	Cannot be exported to Excel	Excel	Excel
Technology	Calculation on the server, Excel does not need to be installed for this	Calculation on the client	Calculation on the client, Excel must be installed on the client for this	Calculation on the client, Excel must be installed on the client for this
Independent of language, region and country settings				Partial
Maximum number of lines	unlimited	100.000 (default) Performance problems with large amounts of data due to browser-based system	50.000 (default) Maximum: 1.000.000	1.048.576
Evaluable tables	All	Only if a page of type List exists for the table.	Only if a page of type List exists for the table.	All
Evaluable fields	All	Only displayed	Only displayed	All
Evaluations can be saved		Limited as page view	Limited as page view
Evaluations can be shared with other users		-	-
Evaluation archive		-	-
Evaluations can be scheduled (incl. Report Inbox)		-	-	-
Cross-company evaluations		-	-
Several worksheets per evaluation		-	-	-
Number of tables per evaluation	unlimited	1	1	Base table required
Table relations		-	-
Add the same field more than once		-	-
Filter at field level Example: Comparison of the current sales amount with the amount from last month		-	-
Formulas, Excel formulas and Excel functions		-	-
Fixed and dynamic references		-	-
Totals calculations		-	-
Column formatting, colors, decimal places, data bars, etc.		-	-	Partial
Pivot functionality			-
Multiple pivot sheets per evaluation		-	-
Multiple pivot charts per evaluation		-	-
Pivot sorting, filters, subtotals		-	-
Pivot data slicer		-	-
Classic pivot layout	Configurable	-	-	Configurable
Flexible date filters (placeholders)		-	-
Parallel tree structures Example: Combining data from different tables		-	-	-
Text sheets, information sheets		-	-
OneDrive integration		-	-	-
Connector		-	-	-
Download area		-	-
Excel templates	-	-	-

Docs  /  Excel Report Builder  /  Appendix
 Import old NC Cube files

Following is no longer supported:

• Excel Templates
• Charts
Note: PivotCharts are still supported.
• Cell Comments
• Link/Page Link
• "Output only if" (Data Sheet)

Changed behavior for References in connection with Column Inheritance

Example

Manually convert FlowFilter loops

Docs  /  Excel Report Builder  /  Appendix
 NAVAX License Management

Trial Version

Request License

Activate/Update License

Terms & Conditions

https://www.navax.app/EULA.php?L=en

Data Security

https://www.navax.app/privacyStatement.php?L=en

Submit Rating

Docs  /  Excel Report Builder  /  Appendix
 Installation Notes

Publish and Install the Extension

[Microsoft Learn] Business Central - Publishing and Installing an Extension

Dependencies

• NAVAX Extension Base

  ID: 2f4cb42d-6e2a-4708-a5e2-3ec5f32f20e9 For more information, see [Docs] NAVAX Extension Base

On-Premises

• 70171825 Excel Report Builder by NAVAX
• 1010860 Extension Base by NAVAX

External Addresses

• https://www.navax.app

  For licensing, calling the online help and performing some actions, access to https://www.navax.app must be granted.
  
  Public IP from www.navax.app for setting firewall access:
  94.136.22.236, Port: TCP/443
  
  Checking the connection to https://www.navax.app using PS:
  Test-NetConnection navax.app -port 443
  (PS must be performed with the M-Tier service user)
  
  CRL Servers
  In addition, the following CRL Servers must also be accessible for the certificate check:
  https://certificates.godaddy.com/*
  http://crl.godaddy.com/*
  or their IP: 192.124.249.36

Docs  /  Excel Report Builder  /  Appendix
 Release Notes

Version 24.2.8.0

• Note

  This version is also available as a downgrade version for Business Central 21, 20 and 14.

• Improvements

• • Improvements in all Sheet Settings:
    • The position of the fields has been revised.
    • A Print group with the following settings has been added:
      • Orientation
      • Fit width to pages
      • Rows to repeat at top
      • Columns to repeat at left
      • Page Order
    • The sheet settings of the PivotTable are now also organized in a separate page, as in the other sheets.
    For more information, see Edit Evaluations, Data Sheets, Sheet Settings and Edit Evaluations, PivotTable Sheets, Sheet Settings.
  • Improvements in the PivotTable Field Settings:
    • The position of the fields has been revised.
    • The settings for the Subtotals have been revised.
    • A Print group with the following settings has been added:
      • Page break after each item
    For more information, see Edit Evaluations, PivotTable Sheets, Field Settings.

• Modifications

• • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

Version 24.2.7.0

• Improvements

• • The action Send by Email has been added to the evaluations. For more information, see Execute Evaluations, Execute Excel Evaluation.
  • The field Grand Totals has been added to the Sheet Settings of the PivotTable. For more information, see Edit Evaluations, PivotTable Sheets, PivotTable.

• Modifications

• • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

Version 24.2.6.0

• Improvements

• • The Filters and Relations area in the Table Settings have been revised. For more information, see Edit Evaluations, Data Sheets, Table Settings.

Version 24.2.5.1

• Corrections

• • The PivotTable Field Settings were not handled correctly in certain constellations. As a result, the Excel workbook was corrupted and the following message appeared when opening the workbook: We found a problem with some content in '...xlsx'. Do you want us to try to recover as much as we can?

Version 24.2.5.0

• Improvements

• • The action Translate all Excel Functions has been added to the Excel Report Builder Setup page. The action can be used to translate the Excel Function names in the Formulas of all evaluations into English Excel Function names after upgrading the old NC Cube version to Business Central. For more information, see Setup, Excel Report Builder Setup.

• Modifications

• • Removal of obsolete code parts.
  • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

Version 24.2.4.0

• Business Central Update

• Modifications

• • The symbols of the Add action in the Data Sheet have been improved. For more information, see Edit Evaluations, Data Sheets, Data Sheet.
  • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

Version 21.2.3.0

• Improvements

• • The Filters and Relations area in the Table Settings have been revised. For more information, see Edit Evaluations, Data Sheets, Table Settings.
  • Any control characters in the data are now automatically replaced by spaces.
  • User related filters are no longer deleted when a superordinated table is added.

• Corrections

• • When executing an evaluation with several data sheets, the filters from the different data sheets were not grouped correctly. For more information, see Execute Evaluations, Evaluation Filters.

Version 21.2.2.0

• Improvements

• • Fields of type DateTime are now also formatted as DateTime in Excel if automatic formatting is selected for the field. The DateTime Accuracy field can be used to set whether only the minutes or also the seconds should be transferred to Excel. For more information, see Edit Evaluations, Data Sheets, Field Settings.
  • The fields Format as Number and Format as Date have been replaced by the field Format Formula as. For more information, see Edit Evaluations, Data Sheets, Field Settings.
  • The Text Formatting can now also be set for internal fields. For more information, see Edit Evaluations, Data Sheets, Field Settings.

Version 21.2.1.1

• Corrections

• • The Create Notifications option in the Archive Excel Evaluation action caused an error if multiple users had added the evaluation to the My Excel Evaluations list and the action was scheduled. For more information, see Execute Evaluations, Archive Excel Evaluation.

Version 21.2.1.0

• Improvements

• • A Chart Type can now be specified for PivotTable Charts. For more information, see Edit Evaluations, PivotTable Sheets, PivotTable.

• Modifications

• • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

Version 21.2.0.0

• Important

  For On-Premises an update of the License or the following granules is required:

  • 70171825 Excel Report Builder by NAVAX

• New Product Name

• • The NCE Excel Builder extension has been renamed to Excel Report Builder.

• Improvements

• • You can now add PivotTable Charts (PivotCharts). For more information, see Edit Evaluations, PivotTable Sheets, PivotTable.
  • PivotCharts are now supported when importing old NC Cube files. For more information, see Appendix, Import old NC Cube files.

• Modifications

• • The NCE Excel Evaluation Setup has been renamed to Excel Report Builder Setup.
  • NCE Excel Evaluations has been renamed to Excel Report Builder Evaluations.
  • NCE My Excel Evaluations has been renamed to My Excel Report Builder Evaluations.
  • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

Version 21.1.13.0

• Improvements

• • The Add action in the Data Sheet now inserts new lines at resp. after the current position.

Version 21.1.12.0

• Improvements

• • The field Without 1000 Separator has been added to the Field Settings. For more information, see Edit Evaluations, Data Sheets, Field Settings.

• Modifications

• • The behavior of some pages and changing some field values has been optimized.

Version 21.1.11.0

• Improvements

• • Since there were several requests regarding the PivotTable option Classical PivotTable Layout, the option has been included in the NCE Excel Builder. The option can be set in the Sheet Settings of the PivotTable. Die Option kann in den Blatteinstellungen der PivotTable gesetzt werden. For more information, see Edit Evaluations, PivotTable Sheets, PivotTable.
  • The input area for the formulas has been enlarged. The smaller input area can optionally be activated via the Small Formula Input Area field in the NCE Excel Evaluation Setup. For more information, see Setup, Excel Report Builder Setup.
  • When changing a Formula ID in a datasheet, all formulas in which the ID is used as a :COL or :ROW Formula ID are now also changed or updated.
  • The NCE Excel Evaluation Card page and the NCE Execute Excel Evaluation and NCE Archive Excel Evaluation actions now additionally display the first comment line if there are comments for the evaluation.

• Modifications

• • The Comment field in the Evaluation Archive has been renamed to Archive Comment.

Version 21.1.10.1

• Modifications

• • The behavior of some pages and changing some field values has been optimized.
  • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

Version 21.1.10.0

• Modifications

• • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

• Corrections

• • 1:n relationships did not produce results in certain constellations.
  • Scheduling an evaluation aborted with the following error message: "The permission settings of the evaluation do not allow you to execute the evaluation."

Version 21.1.9.0

• Improvements

• • The NCE Excel Evaluation Setup page has been revised. For more information, see Setup, Excel Report Builder Setup.
  • The Field Settings page has been revised. In addition, the Fill Color and the Font Color can now be specified here for each field. For more information, see Edit Evaluations, Data Sheets, Field Settings.
  • The Text Settings page has been revised. In addition, the Fill Color and the Font Color can now be specified here for each field. For more information, see Edit Evaluations, Data Sheets, Text Settings.
  • Instead of the term Date Filter Parameter, the term Date Filter ID is now used. For more information, see Edit Evaluations, Data Sheets, Date Filter IDs.
  • Added field Fixed Date Filter Calc. Date to the NCE Excel Evaluation Card page. For more information, see Edit Evaluations, General, Edit Excel Evaluation.
  • NCE Excel Evaluations can now be very easily integrated or called from other extensions via the NCE Connector codeunit. For more information, see Information for Developers, Connector.

• Modifications

• • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

Version 21.1.8.0

• Modifications

• • The NCE Excel Builder engine has been optimized based on the Excel row no. check added in version NCE 21.1.6.0.

Version 21.1.7.1

• Quality of Life Update

• Improvements

• • On the Formula page in the Datasheet, the Formula Help action has been added. The action displays an overview of all Excel Functions and Formula IDs used in the current formula.

Version 21.1.7.0

• Improvements

• • On the PivotTable Field Settings page, layout settings for the field can now be specified. For more information, see Edit Evaluations, PivotTable Sheets, Field Settings.
  • The caption of the PivotTable Field Settings page now displays the name of the field.

Version 21.1.6.0

• Important

  For On-Premises an update of the License or the following granules is required:

  • 70171825 Excel Report Builder by NAVAX
  • 1010860 Extension Base by NAVAX

• Business Central Update

• Improvements

• • In order to ensure that only Codeunits that were also created for the NCE Excel Builder can be executed via the Codeunit to Run setting, they must now be additionally included or unlocked via an EventSubscriber. For more information, see Information for Developers, Runnable Codeunits.
  • In the Data Sheet, Temporary Data Codeunits can now be added. With the help of temporary data codeunits, evaluations can be extended with functions that temporarily calculate certain data of the evaluation during execution. For more information, see Edit Evaluations, Data Sheets, Table Settings.
  • A Filter ID can now be specified for the filter in the Filters and Relations area of the Table Settings. This ID can then be used to reference or access the filter value in other filters. For more information, see Edit Evaluations, Data Sheets, Table Settings.
  • If a connection between Business Central and OneDrive exists, the actions Open in OneDrive and Share are now additionally available for the evaluations. For more information, see Execute Evaluations, Execute Excel Evaluation.
  • The input of date filters has been improved.
  • Data Sheets can now be copied into themselves.
  • The options of the Add action in the Data Sheet have been optimized for long table names.
  • When deleting an Excel Evaluation, an additional confirmation dialog is now displayed if archive entries exist for the evaluation.
  • When executing the evaluation, the record counter now displays the total number of records in addition to the number of top records already passed.

• Modifications

• • PivotTable value fields which are not based on a numeric field are now formatted as Default.
  • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

• Corrections

• • The Excel row no. was not determined correctly in certain constellations or was assigned twice. As a result, the Excel workbook was corrupted and the following message appeared when opening the workbook: We found a problem with some content in '...xlsx'. Do you want us to try to recover as much as we can? The error has been fixed and the row no. is now additionally checked when the evaluation is executed.
  • The placeholder %1 in the Table Settings was not replaced by the default table caption. For more information, see Edit Evaluations, Data Sheets, Table Settings.

Version 20.1.5.0

• Improvements

• • Formulas can now be formatted as date in Excel via the Format as Date field. For more information, see Edit Evaluations, Data Sheets, Formulas.
  • The Superordinate and Subordinate actions in the Data Sheet have been improved.

• Modifications

• • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

Version 20.1.4.0

• Improvements

• • When creating a new evaluation, a Data Sheet is now also automatically suggested.
  • When adding fields to a table, they are now inserted before any child tables.
  • If the same table is subordinated to the table once again (e.g.: Customer > Customer), this is suggested as a 1:1 relationship with the primary key fields as a filter.
  • When selecting the "Related fields between table and parent table" option of the Suggest Filter and Relations action in the Table Settings, you can now select for which parent table the related fields should be suggested, if there are several parent tables.
  • New Formula IDs [TOTALVALUE] - Will be replaced by the sum of the field values of all records in the case of a numeric (Decimal, Integer, BigInteger) field. [TOTALRECORDS] - Will be replaced by the number of all records. [RECORDNO] - Will be replaced by a sequential number for the current record. For more information, see Edit Evaluations, Data Sheets, Formulas.
  • If no evaluations have been created yet, you will be asked whether the Download Area should be opened when you open the evaluation overview.
  • The NCE Excel Builder now uses Business Central's Partial Records technology for a further performance boost.

• Modifications

• • The Preview with sample data option is now set to No by default for all calls.

• Corrections

• • For the fields of a field relation (Field 🞥) data from the previous record was displayed for records without a field value.
  • The action Translate Formula did not work correctly in certain constellations. ROUND became ROAND, for example.
  • The Translate Formula action now also recognizes function names written in lowercase letters.

Version 20.1.3.1

• Corrections

• • When importing old NC Cube files, the JOINs were not converted correctly. For more information, see Appendix, Import old NC Cube files.

Version 20.1.3.0

• Improvements

• • When selecting the filters of an option field, multiple options can now be selected and added at the same time.
  • When selecting the filters of a company loop, multiple companies can now be selected and added at the same time.

• Modifications

• • The phrase "by NAVAX" has been removed from the product name.
  • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

Version 20.1.2.0

• Business Central Update

• Improvements

• • Added Codeunit to Run setting to the NCE Excel Evaluation Card page. Here you can specify a codeunit to run when the evaluation is executed. For more information, see Edit Evaluations, General, Edit Excel Evaluation.

• Modifications

• • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

• Corrections

• • Adding additional filter fields in the NCE Excel Evaluation Filters page when running an evaluation was not possible under certain constellations.

Version 19.1.1.0

• Improvements

• • New Formula IDs [PERIOD_TEXT] - Will be replaced by the current period (e.g. 2022/03 March) of a (parent) Date Loop. [PERIOD_DATE] - Will be replaced by the current period (e.g. 03/01/22..03/31/22) of a (parent) Date Loop. For more information, see Edit Evaluations, Data Sheets, Formulas.
  • The Preview in the Download Area is now no longer opened in a separate browser window, but directly in Business Central.

• Corrections

• • When date fields were added to an evaluation, an error message occurred when the evaluation was executed for records with empty date values.

Version 19.1.0.0

• Improvements

• • The default value is now suggested again in the default style fields of the Excel Evaluation Setup if the value is deleted. For more information, see Setup, Excel Report Builder Setup.
  • The Field Search in the table selection has been converted to an action. For more information, see Edit Evaluations, Data Sheets, Data Sheet.
  • When changing a Formula ID in a datasheet, all formulas in which the ID is used can now optionally be changed or updated as well. For more information, see Edit Evaluations, Data Sheets, Formulas.

• Modifications

• • NAVAX Extension Base extension update.
    For more information, see [Docs] NAVAX Extension Base - Release Notes

Version 19.0.0.0

• Initial Version

• Improvements & Modifications compared to previous NAV versions

• • The name of the extension has been changed from NC Cube to NCE Excel Builder.
  • To execute an evaluation or create an Excel workbook, Excel is no longer required and therefore does not need to be installed. However, in order for a user to open and view a created Excel workbook, a corresponding program must be installed. Recommended is Excel 2016 or a newer version. Alternatively, you can use Excel 2013, Excel 2010, Excel 2007, OpenOffice, Office on an Android device, or Office on an iPhone or iPad.
  • It is now possible to have several data sources in an evaluation.
  • Excel Evaluations can now also be scheduled. When the Excel workbook is ready, a message appears in the Report Inbox from where the Excel workbook can be opened.
  • The archiving of an Excel Evaluation can now also be scheduled. When archiving has been performed, a message appears in the Report Inbox. The archived evaluation can then be opened directly via the Report Inbox or via the NCE Excel Evaluation Archive.
  • It is now possible to format 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.
  • Instead of the term Object, the term Sheet is now used.
  • Instead of the term JOIN, terms like Parent / Child Table and Related with are now used.
  • Parallel tree structures are now possible.
  • Tables can be related to each other over several levels. FlowFilter loops became obsolete with the new relation capabilities.
  • A field can now be added more than once as a PivotTable value field.
  • Excel Templates are no longer supported.
  • Charts are no longer supported.
  • Cell Comments, Link/Page Link and "Output only if" (Data Sheet) are also no longer supported.

• Note

  It is possible to import old NC Cube files (file name extension .nccx) via the import action. For more information, see Appendix, Import old NC Cube files.