Excel-based reporting tool for Microsoft Dynamics NAV. Current Version: 13.00 Dynamics NAV BC 13/12. For older NAV versions, downgrades are available.
Note
NCC Cube has been completely redesigned for Microsoft Dynamics 365 Business Central and is available as an extension under the new product name Excel Report Builder.
For more information, see [Docs] Excel Report Builder
Manual
Creation date: 2024/04/20 The current version of this manual can be found at:
☰ Contents
Quick Guide
Main Menu Essentially, Cube consists of only two parts. Both are provided within the Cube Menu-Screen. There is also a ListPart for My Cubes available, which can be integrated in the Role Centers - just like My Customers or My Vendors...
Setup Cube requires no special setup. However, we recommend using the two Permission Sets provided by Cube. One Set is designed for Cube Editors (the Cubes part), the other one is designed for Cube Users (the My Cubes part)...
General According to the requirements, a Cube is assembled from various Objects. Each Cube contains at least one Object - the Data Source...
Data Source The Data Source Object is the starting point of each Cube and cannot be deleted. All tables and fields for the Cube are specified in this Object...
PivotTable The PivotTable is a powerful analysis tool in Excel. You can add as many PivotTables as required to the Cube...
Chart Charts are an easy way to visualize the Data Source. You can add as many Charts as required to the Cube...
Excel Templates Cube also supports creating Excel workbooks based on Excel templates...
Permissions Permissions determine which (and how) users have access to the Cube...
My Cubes My Cubes is the place from where users usually execute Cubes. Based on the permission settings of each Cube, only authorized users can see, add and execute a Cube within their list...
Appendix
NAVAX Registration The current registration status of a NAVAX Dynamics NAV AddOn is displayed in the AddOn Setup page in the System group...
Release Notes
Docs / NCC Cube (NAV) / Quick Guide Main Menu
Essentially, Cube consists of only two parts. Both are provided within the Cube Menu-Screen. There is also a ListPart for My Cubes available, which can be integrated in the Role Centers - just like My Customers or My Vendors.
Cubes is the part where Cube Editors can create and edit Cubes.
My Cubes is the place from where users usually execute Cubes. Based on the permission settings of each Cube, only authorized users can see, add and execute a Cube within their list.
Docs / NCC Cube (NAV) / Quick Guide Setup
Cube requires no special setup. However, we recommend using the two Permission Sets provided by Cube. One Set is designed for Cube Editors (the Cubes part), the other one is designed for Cube Users (the My Cubes part).
Click on Create Permissions in the Cube Setup to create or update the Permission Sets NCCUBE-EDIT (for Cube Editors) and NCCUBE-MYCUBES (for Cube Users).
In the Cube Setup, you can also set up Departments and Excel functions incl. their translations. The Excel functions can then be selected in the Formula field in the field list.
Via the Download action in the Cube list, it is possible to download ready-to-use Cubes.
Registration
A NAVAX AddOn can be tested or used free of charge for 30 days after installation. After that the functions of the extension can only be used with a valid registration.
The current registration status of the NAVAX AddOn is displayed in the System group.
For more information, see Anhang, NAVAX Registration.
Docs / NCC Cube (NAV) / Quick Guide General
According to the requirements, a Cube is assembled from various Objects. Each Cube contains at least one Object - the Data Source.
To create a new Cube, follow these steps:
In the navigation pane click Departments and select Cube.
Within the Cube Menu-Screen click on Cubes and then on New.
Enter the Code and the Description for the new Cube in the General section.
The Data Source Object will be added automatically.
Note
Later, after all the Setup has been completed, you can set Settings Locked to protect the Cube from accidental modifications.
Docs / NCC Cube (NAV) / Quick Guide Data Source
The Data Source Object is the starting point of each Cube and cannot be deleted. All tables and fields for the Cube are specified in this Object.
Steps for setting up the Data Source:
Within the Objects list of the Cube select the Data Source Object and click the Edit button.
Specify the base table for the cube in the General section.
Click on Add Fields in the Field List to add fields to the Cube.
If you want to add data from other tables, click on Add JOIN.
If you want to add an empty new line, click on New.
Note
Depending on which line in the Field List is currently selected, the functionality of the buttons will differ. E.g. if you click on Add Fields and a JOIN line is selected, you will be asked, for which table the action should be performed.
JOIN
With JOINs it's possible to add additional data from other tables to the base table. It is also possible to add JOINs within JOINs.
Steps for adding and setting up a JOIN:
Click the Add JOIN button in the Field List of the Data Source and select the table you want to add. Cube will automatically search for Links between the two tables and will suggest them. Nevertheless, the next step should still be executed.
Click the Edit JOIN button in the Field List to set up the JOIN. Check the Key No. (Sorting) and the Links between the tables. If necessary (e.g. to achieve better performance) change the settings.
Docs / NCC Cube (NAV) / Quick Guide PivotTable
The PivotTable is a powerful analysis tool in Excel. You can add as many PivotTables as required to the Cube.
Steps for adding and setting up a PivotTable:
Click the Add button in the Objects list of the Cube and select PivotTable.
Click the Edit button in the Objects list to set up the PivotTable.
Click the Add/Remove button in the PivotTable Field List to add fields from the Data Source to the PivotTable, or to remove them.
Via Edit you can specify the field details like Subtotals.
Note
The Virtual Value Field will be added automatically if necessary.
PivotChart
A PivotChart is the visual representation of a PivotTable. Both are connected to each other.
Select PivotChart in the PivotTable Setup to add a PivotChart to the PivotTable.
If Embedded Chart is selected, the Chart will be added directly in the PivotTable sheet. Otherwise, a new sheet will be added for the Chart.
Docs / NCC Cube (NAV) / Quick Guide Chart
Charts are an easy way to visualize the Data Source. You can add as many Charts as required to the Cube.
Steps for adding and setting up a Chart:
Click the Add button in the Objects list of the Cube and select Chart.
Click the Edit button in the Objects list to set up the Chart.
Click the Add/Remove button in the Chart Field List to add fields from the Data Source to the Chart, or to remove them.
Cube also supports creating Excel workbooks based on Excel templates.
Steps for adding a template:
Click the AssistEdit button in the Import File field and select the template file.
Select a sheet for the Data Source. If no sheet is selected a new sheet will be added for the Data Source.
To use the template, it must be Enabled.
If the template contains external content, which should be automatically updated (e.g. links to external text files) select Refresh Template.
Docs / NCC Cube (NAV) / Quick Guide Permissions
Permissions determine which (and how) users have access to the Cube.
Cube Editors can:
Set Execute permissions to restrict users access.
Set My Cube Selection Allowed to allow adding the Cube to the My Cubes list by the users itself.
Manage the My Cubes selections for the Cube via My Cube Selections.
Docs / NCC Cube (NAV) / Quick Guide My Cubes
My Cubes is the place from where users usually execute Cubes. Based on the permission settings of each Cube, only authorized users can see, add and execute a Cube within their list.
Cube Users can:
Click on Add to add Cubes to their list.
Click on Execute to execute the Cube.
Role Center Integration
A ListPart is available, which can be integrated in the Role Centers - just like My Customers or My Vendors.
This Example shows My Cubes integrated in the Order Processor Role Center (ID 9006).
A developer must do the integration.
ListPart: NCC My Cubes Part (ID 1022120).
The current registration status of a NAVAX Dynamics NAV AddOn is displayed in the AddOn Setup page in the System group.
Fields
Version
Specifies the currently installed version of the AddOn.
Serial No.
Specifies the serial number of the AddOn.
Status
Specifies the current registration status of the AddOn.
Trial Version
A NAVAX AddOn can be tested or used free of charge for 30 days after installation. After that, the AddOn can only be used with a valid registration.
Request Registration
The registration can be requested or checked via the What's New? action. This opens a window.
The following example shows the NAVAX AddOn "NCC Cube".
Fill in the fields in the window and then click Send Registration Request.
Please note that the registration process may take some time.
In the next few days you will receive an email with further information.
Note
For registration, calling the online help and performing some actions, access to https://www.nccube.com and https://www.navax.app must be allowed.
In addition, TLS 1.2 is required.
For more information, see How to get earlier versions of the Dynamics NAV development environment to work with TLS 1.2Public 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)
Activate/Update Registration
As soon as the registration has been completed, you will receive an email and the registration can be activated via the Update Registration action.
The registration is company independent. So it does not matter in which company the action is called.
Note
The registration must be updated once a year via the Update Registration action.
The update is only possible or necessary within the last 30 days before the registration expires, or afterwards. Within the last 30 days before the registration expires, notes are displayed.
Would you like to know what has changed in the extension? Below you'll find an overview of the new features and changes made in the updates.
Note
NCC Cube has been completely redesigned for Microsoft Dynamics 365 Business Central and is available as an extension under the new product name Excel Report Builder.
For more information, see [Docs] Excel Report Builder
NCC Update
The following update codeunits are available:
All files needed for the updates can be downloaded here:
NAV_NCC_Update_20220525.zip
NCC 13.00
Dynamics NAV BC 13/12 2018/11/01
Note
This version is also available as a downgrade version for for older Dynamics NAV versions.
General
An update of the license file is necessary.
It is necessary to add Table 1022133 to the Permission Sets.
Click on Create Permissions in the Cube Setup to create or update the Permission Sets NCCUBE-EDIT (for Cube Editors) and NCCUBE-MYCUBES (for Cube Users).
A registration is necessary. The trial version expires in 30 days.
For more information, see Appendix, NAVAX Registration.
Improvements
NC Cube is now available for Microsoft Dynamics 365 Business Central – OnPremises.
For a complete list of all available versions, see the document Version Matrix. Note that the features or functionality of the solution may vary depending on the installed version, the client you are using, and/or your license model.
The settings in the Excel Custom Formatting field of the Field List will now also be used for PivotTable value fields.
Modifications
All setup functions are now available in the new Cube Setup window.
You can open the setup via the main menu or via the Cube List.
NCC 11.02
Dynamics NAV 2018 2018/07/01
Modifications
The term Area has been renamed to Department.
Corrections
Added missing translations in the captions.
NCC 11.01
Dynamics NAV 2018 2018/03/01
Improvements
Added field JOIN Field Operator to the Cube JOIN Link. So far, fields JOIN fields have been linked to Equality. With the new field, it is now possible to link them to Less or Equal, Less, Greater or Equal, Greater or Not Equal. For example, it is now possible to join all General Ledger Entries to a G/L Register or to join a G/L Register to a General Ledger Entry.
NCC 11.00
Dynamics NAV 2018 2017/12/01
Improvements
NC Cube is now available for Microsoft Dynamics NAV 2018.
Corrections
Added missing translations in the captions.
NCC 10.01
Dynamics NAV 2017 2017/06/01
Corrections
After the last security update for Microsoft Excel (KB4011050), PivotTables and PivotCharts could no longer be created.
NCC 10.00
Dynamics NAV 2017 2017/01/01
Improvements
It is now possible to remove several selected/marked Cubes from the My Cubes list. To do so, select the Cubes and click on Remove. Removing the selected Cubes must only be confirmed once for all Cubes.
Modifications
Removing a Cube from My Cubes must now be confirmed twice.
For the use of some online services a registration is required.
For more information, see Appendix, NAVAX Registration.
Some windows have been redesigned/improved.
NCC 8.03
Dynamics NAV 2015 2015/11/01
Improvements
The Create Cube Permissions Action in the Cube List has been moved to a new menu group called Functions.
Following additional new Actions have been added to the new menu group:
Delete selected Cubes
Can be used to delete several selected/marked Cubes. Deleting the selected Cubes must only be confirmed once for all Cubes.
Import Folder
Can be used to import all Cube Files (*.ncc|*.nccx) from a specific folder.
It is now possible to export several selected/marked Cubes to a specific folder. To do so, select the Cubes and click on Export. For each Cube a separate file will be created.
It is now possible to download several selected/marked Cubes. To do so, select the Cubes and click on Download or Save as.
Cubes which have been provided or updated within the last month, will now be displayed Blue+Bold in the Download window.
Cubes which have been provided or updated within the last 3 months, will be displayed in Blue.
In the Table selection window it is now possible to search for a specific Table No. by using the Table Search Filter field.
Added new Actions for quick and easy filtering of Decimal fields, JOIN fields, FlowFilter and FlowFields to the Field selection window.
Added field Chart Title to the PivotTable Setup and to the Chart Setup. If the field contains a value, a title will be added to the Chart.
Fields will be displayed Bold in the PivotTable Add/Remove Fields and Chart Add/Remove Fields window if they were added.
Actions for adding and removing fields have been added to the PivotTable Add/Remove Fields and Chart Add/Remove Fields window. The available Actions are determined through the selected field.
Deleting/Removing the value field of a PivotTable will now check, if the field is used as sorting and/or filter settings in other fields. If so, an additional confirmation is required.
The Pivot-Settings for a field in the PivotTable Field List will no longer get lost, if the field will be deleted/removed and added again.
Added following Actions to the Cube Formula Editor:
Add Excel Function
Corresponds to the Lookup functionality of the Formula field.
Add Formula ID
Corresponds to the AssistEdit functionality of the Formula field.
Additionally to the special reserved IDs, now also the IDs, which have been defined in the Formula ID field of the Field List, can be selected via AssistEdit in the Formula field.
Modifications
Some windows have been redesigned/improved.
The fields NAV Requirement, Excel Requirement and Additional Requirements have been removed from the Download window. The Information will now be displayed in the new field Requirements.
If Create Table in the Data Source Setup, FastTab Advanced is selected, the Name of the Excel-Table Object will now be set to the Data Source Sheet Name.
NCC 8.01
Dynamics NAV 2015 2015/08/01
Corrections
Fixed an error in the NAV Page Link functionality. In certain situations the company name was not included in the hyperlink.
NCC 8.00
Dynamics NAV 2015 2015/06/26
Improvements
Added Fields Execute Permission, My Cube Selection Allowed, My Cubes Selections and Responsible to the Cube List.
Added following functions to the Cube List:
Create Cube Permissions
Can be used to create or update the default Cube Permission Sets.
Setup Cube Excel Functions
Can be used to set up the Excel Functions and their translations. The Functions can be selected in the Formula field of the Field List. Import/Export and Download functions are available for Cube Excel Functions.
Setup Cube Areas
Can be used to open the Cube Areas.
It is now possible to change the Code for the Cube during the Import.
An estimated remaining execution time will now be displayed during the execution of the Cube.
Following placeholders (Special Filter IDs) have been added to the Table and JOIN Filters:
[COMPANY] - Will be replaced with the current Company Name.
[MYCUSTOMERS] - Will be replaced with My Customers.
[MYVENDORS] - Will be replaced with My Customers.
[MYITEMS] - Will be replaced with My Items.
Following additional placeholder has been added to the JOIN Filters:
[RECORDID] - Will be replaced with the RecordID of the Linked Record.
So now it is possible to create JOINs for tables that are linked only by a RecordID.
Placeholders are only possible for Text, Code, Date and RecordID fields.
It is now possible to select placeholders with AssistEdit in the Filter field (LookUp functionality).
Added new functions Suggest JOIN for Notes and Suggest JOIN for Links to the Field List.
With this functions it is possible to add JOINs for Notes and Links to a table. Depending on which line in the Field List is currently selected, the functionality of the functions will differ. E.g. if you click on Suggest JOIN for Notes and a JOIN line is selected, you will be questioned, for which table the action should be performed.
It is now possible to select Excel Functions via LookUp in the Formula field of the Field List.
Special Formula IDs are now available via AssistEdit.
Added new functions Translate Excel Functions and Translate all Excel Functions to the Field List.
With this functions it is possible to translate Excel Functions, which are used in the Formula field, in a different language. Translate Excel Functions will only translate Functions for the currently selected line. Translate all Excel Functions will translate all Functions of the Field List.
Date-Analysis using FFL (FlowFilter Loop) have been improved.
The FFL Date Interval can be selected/changed when executing a Cube if Show Filter Form is set for the Cube.
Previously, the Date Filter field has been issued in the format FROM...TO (e.g.: 01.01.15..31.01.15).
Sorting the data by date was therefore not possible in Excel.
Depending on the selected FFL Date Interval, the field will now be formatted according to the following rules:
Week: Year4/Week,2
Month: Year4/Month,2 Month Text
Quarter: Year4/Quarter
Year: Year4
Example: Instead of '01.01.15..31.01.15' now '2015/01 January' will be issued.
The new formatting rules only applies to Date Filter fields, where FFL (FlowFilter Loop) is set.
If the old format is required, the Date Filter field must be additionally added to the Field List.
Added new field Opposite Sign to the Field List.
With this field it is possible to reverse the sign of decimal and integer fields.
Added new field Horizontal Alignment (Left, Center, Right) to the Field List.
With this field it is possible to specify the orientation of the field, resp. the Excel column.
Added new field Data Bar to the Field List.
With this field it is possible to add a data bar to the field, resp. to the Excel column.
Added new field Cell Text Wrap to the Field List.
If this field is set, text wrapping is allowed within the cells of the Excel column. For example: The function Suggest JOIN for Notes set the field Cell Text Wrap for the suggested Note field to Yes.
Added new field Information to the Field List. The field shows basic information about the field resp. the JOIN.
Added new field Count/Total to the Field List. The field can only be specified for Decimal fields, or if no Field No. is specified.
If a Field No. is specified, the total value of the field will be calculated.
If no Field No. is specified, the number of records will be calculated.
Rule for 1:n JOINs: If only fields with Count/Total exists for a 1:n JOIN, not the individual records, but only a line with the calculated total values will be printed.
Added new field Excel Custom Formatting to the Field List.
Previously, all Excel cells were formatted as Standard, unless the option Text was selected in the Excel Data Format field. In this case, the Excel cells were formatted as Text.
New functionality:
From now on Text and Code fields are automatically formatted as Text, unless the Formula field contains a value.
If Excel Formatting is enabled, the field will (as before) be formatted as Text. But additionally, it is now possible, to specify a value in the new field Excel Custom Formatting. In this case, the Excel cells will be formatted as Custom and the value, specified in the field Excel Custom Formatting, determines the format.
Examples:
0.00
Formats a number. 2 decimal places will be shown.
dddd, mmmm dd, yyyy
Formats a date. Example: 28.03.2013 becomes Thursday, March 28, 2013
Added new function Move to to the PivotTable Field List in the PivotTable Setup. With this function it is possible to move fields between the PivotTable areas (Row Label, Column Label, Value and Report Filter).
It is now possible to define the sorting for Row Labels and Column Labels in the PivotTable Field List via the Edit Button.
Specify the PivotTable field resp. the criteria for the sorting in the Sort by field. Specify ascending or descending in the Sort field.
It is now possible to define a filter for Row Labels and Column Labels in the PivotTable Field List via the Edit Button.
Specify the PivotTable field resp. the criteria for the filter in the Filter by field. Specify the type of the filter, for example Top Count, in the Filter field. Specify the parameters for the filter in the Filter Value and Filter Value 2 fields.
It is now possible to search for related tables in the Table Selection window that opens via the Add JOIN function. For example: If Add JOIN is called for a Customer Ledger Entry, the Search related Tables function will display tables, which have a direct relation to the Customer Ledger Entry.
The function Show All or the input of Search Filters will remove the Filter.
Added new field 1:n JOIN – Line Break to the Data Source Setup. By default, if you create a new Cube, the field will be set to Yes.
This new field change the way how 1:n JOINs will be issued.
As long as 1:n JOIN – Line Break = No, the 1:n JOIN Data will be issued as so far.
Example:
Cube, Data Source Table: Customer, Fields: No. and Salesperson, 1:n JOIN: Customer Ledger Entries and after that, the Customer Posting Group field of the Customer.
Because the output of the data is also related to the 1:n JOIN – Fill field, the differences will first be shown for 1:n JOIN – Fill = No and after that, for 1:n JOIN – Fill = Yes:
1:n JOIN – Fill = No
Result so far and for 1:n JOIN – Line Break = No:
Result for 1:n JOIN – Line Break = Yes:
Note
1:n JOIN – Line Break = Yes ensures, that no data from the first Entry will be involved in evaluations at the Salesperson level. The same is true for Customer Posting Group evaluations with the last Entry.
1:n JOIN – Fill = Yes
Result so far and for 1:n JOIN – Line Break = No:
Result for 1:n JOIN – Line Break = Yes:
Note
In this case 1:n JOIN – Line Break = Yes ensures, that no data from the last Entry will be involved in Customer Posting Group evaluations.
Added new function Formula Editor to the Field List. The Formula Editor is an alternative input option for the Formula field.
Added new field Hyperlink to the Field List. If, for example, this field is set for fields containing an e-mail address, a web page, a file or other links, a Hyperlink will be displayed in Excel.
The cell value will be used as the address for the Hyperlink. Depending on whether Value or Caption is selected in the Hyperlink field, the cell value or the field caption will be used as Text to display for the Hyperlink.
Modifications
Deleting a Cube must now be confirmed twice.
When you delete Lines in the Field List, the deletion of existing JOINs must no longer be confirmed for each individual JOIN.
Changing the Table No. of the Data must now be confirmed twice.
The Read Permission field in the Table Selection page will no longer be automatically determined when the page is opened.
To check the permissions of the tables the Test Permissions action must be called.
The Key field names, selected in the Data Source and in the JOIN Setup, will now be displayed in the Base Language only. If the design of the table (or the design of the key in the table) will be changed in the Database, the displayed field names in the Key fields will now be updated automatically.
The shortcuts for the Add functions have been changed from Alt+Left to Alt+Ins.
The shortcuts for the Edit functions have been changed from Alt+Right to Ctrl+Shift+E.
The design of the Field List has been changed. All fields, JOINs and JOIN fields will now be displayed resp. managed within the Field List of the Data Source. The Field List in the JOIN Setup page has been removed.
New lines can now be added to the Field List via the Add functions or via New.
Depending on which line in the Field List is currently selected, the functionality of the functions will differ. E.g. if you click on Add Fields and a JOIN line is selected, you will be questioned, for which table the action should be performed.
The value of the Caption field will now be formatted according to the following rules:
If, for a field (Type=Field), a simple JOIN is possible, i.e. the field has a 1:1 relation to a table and fields from this table could be added, the Caption will be shown in Blue. If fields from the related table will be added, the Caption will be shown in Blue+Bold.
If, for a JOIN (Type=JOIN), neither a field nor a link exists, the Caption will be shown in Red+Italic. If a link between the two tables is set, the Caption will be shown in Blue. If fields from the JOIN table will be added, the Caption will be shown in Bold. If the link is set and fields exists, the Caption will be shown in Blue+Bold.
Because of the new design, some fields are no longer required in the Field List. These fields are now hidden in the Field List by default.
Improved the behavior of the Add Fields function in the Field List. As before, fields can be added by selecting them with the Selected field and closing the page with OK. If no fields are selected and the page will be closed with OK, the new behavior will add the currently selected field (default LookUp functionality).
Added field Output only if in the JOIN Setup page. The field is now hidden in the Field List by default. In the JOIN Setup page the field is only editable, when a setting is possible resp. allowed.
The settings for the FFL (FlowFilter Loop) are now managed in a separate window. The window can be opened via the FFL (FlowFilter Loop) function in the Field List.
The field FFL Date Filter Type has been renamed to FFL Date Interval.
The field FFL Date Maximum Value has been renamed to FFL Till Date Filter.
The field Field Formatting in the Field List has been renamed to NAV Formatting.
The field Excel Data Format in the Field List has been renamed to Excel Formatting and is now a Boolean field. The option Text from the old field is equivalent to the value Yes in the new field.
The field AutoFill 1:n JOIN in the Field List has been renamed to 1:n JOIN - Fill. By default, if you create a new Cube, the field will be set to Yes.
Microsoft Script Control is no longer needed.
Corrections
The functionality of the NAV Page Link field has been changed. The hyperlink will no longer be created by using an Excel formula but will now be created as hyperlink to the field. The content of the field will no longer be changed.
In certain situations unnecessary blank lines were exported - For example: JOINs within JOINs without further fields or with filters on the JOIN level.
An error could occur when a PivotTable with more than 65536 lines was created.
Several errors in relation with Charts have been fixed.
It is no longer possible to add virtual fields to a PivotTable or a Chart.
In relation with AutoFill 1:n JOIN, virtual fields caused wrongly line breaks.
NCC 7.01
Dynamics NAV 2013 2013/08/01
Improvements
The Filter Field limitation of 250 characters in the Table Filter, Field Filter, Cube Filter and the JOIN Link was extended to 1000 characters.
If the command =HYPERLINK is used in a formula the text in Excel will be displayed as a Hyperlink now.
Modifications
The Information Sheet Object only contained Filters with Source = Table Filter in Excel. Now also Field Filters and JOIN Filters, where Show in the Filter Form is enabled, will be displayed.
Corrections
The option Based on already created links in other Cubes from function Suggest Link (JOIN Fields) in the Cube JOIN Setup failed with an error.
When entering a Table Search Filter in the table selection window no records were found.
In certain constellations choosing a Filter via Lookup could occur in an error.
NCC 7.00
Dynamics NAV 2013 2013/04/01
Modifications
General
You can now assign Users to Cube Areas. The assignment has no effect on the Cube.
In the Cube Create Permissions window there is a new function Add Users from Area. With this function you can add assigned Users from different areas.
Objects
New Layout:
Each Cube is now composed of objects.
Each Cube contains an object of the type Data Source. The Data Source is basically the old Cube Card.
More objects can be added if required. This allows you to add more than one Chart, PivotTable etc. to a Cube.
Objects can be enabled or disabled with the option Enabled.
The option Active Sheet determines whether the object is the active sheet in Excel after the creation.
Exception: If Template Active Sheet is selected in the Excel Template this sheet will be used.
If nothing is selected the Data Source will be the Active Sheet.
Type displays the object type.
You can specify a name for the object in the Name field. This name will be used for the Excel sheet name. If the name is enclosed in angle brackets <>, the cube will determine the name based on the NAV language (e.g. for Tables). The field works like the Caption field in the Field List. If you delete the value in the field the default value is displayed which can be overwritten at any time.
Information displays details about the object.
The order of objects determines their order in Excel.
Objects can be added and then edited.
File Export Cubes only consider Text and Data Source Objects.
Data Source
The object Data Source is the starting point of each Cube.
The Data Source cannot be deleted.
With Edit you can setup the Data Source. In previous versions, the information of the data source was specified directly on the Cube Card.
On the General Fast Tab you specify the table. Table filter (including user related table filters) are specified within the field Table Filter.
The Order field has been added.
On the Advanced Fast Tab you will find following settings:
In earlier versions of NC Cube the fields AutoFill 1:n JOIN and Remove Field Captions could be set per Cube Type. Both fields are now valid for File Export and Excel Cubes.
It is now possible to freeze rows and columns. You can specify how many rows or columns should be frozen. The fields Row Adjustment and Column Adjustment will be added to the values.
Furthermore you can now format the Data Source as an Excel Table.
If the option Totals Row is activated a Total Row will be added to the Table. The type of the result can be specified for each field in the Field List of the Data Source with the new field Excel Table Totals Row.
The Field List contains all fields, formulas and JOINS for the Data Source.
The Type Formula has been removed. Formulas are now equal to fields but without a Field No..
A new option Virtual Field has been added. If you set this option the field will not be exported.
Virtual Fields cannot be used in PivotTables or Charts.
In relation with Formula ID you can access the values of the Virtual Fields and use them in Formulas.
In this example the No. and Name Field is set to virtual. After that both Fields are used in a Formula.
Virtual fields must be specified before using them. Otherwise they don’t contain a value.
The fields for PivotTable and Chart show whether the field is used there. As long as a Cube only have one PivotTable or Chart object, the content can be changed directly in the Field List as before. If no PivotTable or Chart object exists, it will be automatically created when a field is defined for it. However, the object will not be deleted automatically when no field is defined for it anymore. If more than one possible object exists, the field must be added or removed directly in the corresponding object.
Added new function Suggest XML Tags for XML – File Export Cubes. This function creates (based on the Caption) Formulas for XML formatting.
If the options Create Table and Totals Row in the FastTab Advanced are activated a Total Row will be added to the Table. The type of the result can be specified for each field in the Field List of the Data Source with the new field Excel Table Totals Row.
If a Page is selected in the field NAV Page Link a link to the Record in NAV will be added to the field in Excel.
Removed field Table Filter Date Formula in the Field Filter window.
JOIN:
You can now specify the Sorting Order.
Added new option Existing links between Table and JOIN Table to the "Suggest Link" function. This option is searching for relations between the tables and will suggest them.
Chart
You can now add as many Charts as needed to a Cube.
Removed field Chart Template.
You can now specify the Chart Type and the Chart Layout.
Embedded Charts will be created directly in the Data Source Sheet.
If no fields are specified in the Chart Field List all fields from the Data Source will be added to the Chart. Via Add/Remove you can add and remove fields to the Chart Field List.
Within the Type you can change the position of the field via Up and Down.
PivotTable
You can now add as many PivotTables as needed to a Cube.
With the PivotChart option you can format the PivotTable as a Chart. Embedded Charts will be created directly in the PivotTable Sheet.
Via Add/Remove you can add and remove fields to the PivotTable Field List.
Via Edit you can specify the field details like Subtotals.
The Virtual Value Field will be created automatically.
Via Edit you can move the Virtual Value Field from Column Label to Row Label and vice versa.
Within the Type you can change the position of the field via Up and Down.
Text
Via Edit you can specify text.
The Text object is equal to File Export Header and Footer Text settings in earlier versions.
Depending on whether the object is before or after the Data Source object it will be added as Header or Footer Text.
Text objects now are also allowed in Excel Cubes.
For each Text object a separate Excel sheet (which contains the text) will be created.
Information Sheet
The Information Sheet object is equal to the Create Information Sheet option in earlier versions.
Via Edit you can now specify which information should be displayed on the sheet.
Permissions
Create:
Create Permission – Selected Users
Added 2 new actions to the Selected Users Page:
Add all Users and Add Users from Area
With Add Users from Area you can add assigned Users from different areas.
My Cubes:
For My Cube users is is now possible to determine whether the Cube should be created New or from the last archived file with the new field My Cube Creation Type.
With Selection the user can decide for himself.
The option can be overridden in the My Cubes Selections window.
Companies:
The Company Filter is now specified in the Permissions FastTab.
Characters:
In the Excluded Characters window you can exclude or convert certain characters from the Cube creation.
The PowerPivot Format option in earlier versions is no longer required and therefore removed.
If only a Character is specified it will be excluded. With Convert to you can replace the Character with another Character.
The Replace with Space option allows a conversion to a Space.
Alternatively also the Integer values of the characters can be specified.
Excel
The FastTab Excel now only contains the Template and Print functionality.
All other Excel settings are specified in the respective Cube objects.
Template:
If the option Refresh Template is activated and the Template contains external text file links, no confirmation message from Excel will be displayed.
The external content will be updated automatically.
File Export
The fields Add Date and Add Time can now be specified separately.
This (for example) allows you to create/update only one file per day.
Open File now offers the option to open the file or the folder.
Both can also be set to Ask so that the user can decide whether to open the file/folder after the creation of the Cube.
The Power Pivot Format option has been removed. Removing the quotes can now be handled with Excludes Characters in the Permissions FastTab.
File Export Cubes no longer create File Export Entries. If the Archive is enabled the information will be stored in the Archive Entries.
If a File Export Cube is created with the Job Queue it is now possible to create only an Archive File.
This is useful for File Export since the export so selectively either can create real files or only save the file in the Archive.
The archived file can later be used for creation by users via My Cubes.
Archive
Renamed Log to Archive.
You can now specify if the Date, Time and User ID should be added to the Archive File file name.
Existing archive files will be overwritten automatically.
This makes it possible to create less archive files.
Excel Cubes will be archived as Excel Templates.
Archive files can now be stored Server-Side.
With Save Archive File via Server you can protect the archived files from direct access by the user.
In this case the Client does not need permissions to access the Archive Folder but the folder must be valid for the Server.
Archive Entries now also contain the Destination File information from File Export Cubes.
Additional Information
NC Cube is now based on UTF-8.
I.e. Download, Import/Export and Cube creation now uses UTF-8 for an better international support.
Old NC Cube files (identified by the file extension ncc) can still be imported.
If the previous option Table Filter Date Formula for Field Filters does contain a value it will be skipped and the field Show in the Filter Form for the Field Filter will be enabled. The automatic calculation based on the Table Filter input is no longer possible. Instead use the special filter IDs which are available for Date Filter.
Chart Templates are no longer supported and will be ignored during import.
Exported Cubes now have the new advanced file extension nccx.
You can now export Cubes without Cube Code. In this case you must specify a new Code if you import the Cube file.
Server-Side Archive:
In previous versions the archive folder must be accessible and valid for the Client.
A File Export Cube was first created on the Server, then passed to the Client and copied from the Client to the archive folder.
If the option Save Archive File via Server is enabled, the Server copies the file to the archive folder.
For Excel Cubes the Client will give back the finished Excel to the Server and the Server will store it as an Excel Template in the archive folder.
If these archived Cube files are called via My Cubes, the Server will pass the archive file to the Client.
It is now possible to create Cubes with the Job Queue:
The Object Type to Run must be Codeunit and the Object ID to Run must be 1022109 (NCC Create via Job Queue). In the field Parameter String the Code of the Cube must be specified.
Job Queue Excel Cube:
If an Excel Cube is created via the Job Queue, first the Cube will be created.
If the Print option in the FastTab Excel is set to Print, the Cube will be printed.
If the Archive is activated and the Archive File option is enabled, the Cube will be archived.
Excel will not be opened but the Cube will be created in the background.
Job Queue File Export Cube:
If a File Export Cube is created via the Job Queue, first the Cube will be created.
Options like Show Save As or Open File from the FastTab File Export will be ignored.
If the option Only Archive File is enabled, the file will not be saved in the Export Folder but only in the Archive Folder (In this case the Archive must be enabled).
If the option Only Archive File is not enabled, the file will be saved in the Export Folder and archived only if Archive and Archive File are enabled.
In combination with Save Archive File via Server and My Cube Creation Type different requirements can be covered.
NCC Cube (NAV)
Cubes is the part where Cube Editors can create and edit Cubes.
My Cubes is the place from where users usually execute Cubes. Based on the permission settings of each Cube, only authorized users can see, add and execute a Cube within their list.
Click on Create Permissions in the Cube Setup to create or update the Permission Sets NCCUBE-EDIT (for Cube Editors) and NCCUBE-MYCUBES (for Cube Users).
In the Cube Setup, you can also set up Departments and Excel functions incl. their translations. The Excel functions can then be selected in the Formula field in the field list.
Via the Download action in the Cube list, it is possible to download ready-to-use Cubes.
To create a new Cube, follow these steps:
Steps for setting up the Data Source:
Steps for adding and setting up a JOIN:
Steps for adding and setting up a PivotTable:
Select PivotChart in the PivotTable Setup to add a PivotChart to the PivotTable.
If Embedded Chart is selected, the Chart will be added directly in the PivotTable sheet. Otherwise, a new sheet will be added for the Chart.
Steps for adding and setting up a Chart:
Steps for adding a template:
Cube Editors can:
Cube Users can:
This Example shows My Cubes integrated in the Order Processor Role Center (ID 9006).
A developer must do the integration.
ListPart: NCC My Cubes Part (ID 1022120).
Fill in the fields in the window and then click Send Registration Request.
Please note that the registration process may take some time.
In the next few days you will receive an email with further information.
All files needed for the updates can be downloaded here:
NAV_NCC_Update_20220525.zip
You can now assign Users to Cube Areas. The assignment has no effect on the Cube.
In the Cube Create Permissions window there is a new function Add Users from Area. With this function you can add assigned Users from different areas.
New Layout:
Each Cube is now composed of objects.
Each Cube contains an object of the type Data Source. The Data Source is basically the old Cube Card.
More objects can be added if required. This allows you to add more than one Chart, PivotTable etc. to a Cube.
Objects can be enabled or disabled with the option Enabled.
The option Active Sheet determines whether the object is the active sheet in Excel after the creation.
Exception: If Template Active Sheet is selected in the Excel Template this sheet will be used.
If nothing is selected the Data Source will be the Active Sheet.
Type displays the object type.
You can specify a name for the object in the Name field. This name will be used for the Excel sheet name. If the name is enclosed in angle brackets <>, the cube will determine the name based on the NAV language (e.g. for Tables). The field works like the Caption field in the Field List. If you delete the value in the field the default value is displayed which can be overwritten at any time.
Information displays details about the object.
The order of objects determines their order in Excel.
Objects can be added and then edited.
File Export Cubes only consider Text and Data Source Objects.
On the General Fast Tab you specify the table. Table filter (including user related table filters) are specified within the field Table Filter.
The Order field has been added.
On the Advanced Fast Tab you will find following settings:
In earlier versions of NC Cube the fields AutoFill 1:n JOIN and Remove Field Captions could be set per Cube Type. Both fields are now valid for File Export and Excel Cubes.
It is now possible to freeze rows and columns. You can specify how many rows or columns should be frozen. The fields Row Adjustment and Column Adjustment will be added to the values.
Furthermore you can now format the Data Source as an Excel Table.
If the option Totals Row is activated a Total Row will be added to the Table. The type of the result can be specified for each field in the Field List of the Data Source with the new field Excel Table Totals Row.
The Field List contains all fields, formulas and JOINS for the Data Source.
The Type Formula has been removed. Formulas are now equal to fields but without a Field No..
A new option Virtual Field has been added. If you set this option the field will not be exported.
Virtual Fields cannot be used in PivotTables or Charts.
In relation with Formula ID you can access the values of the Virtual Fields and use them in Formulas.
In this example the No. and Name Field is set to virtual. After that both Fields are used in a Formula.
Virtual fields must be specified before using them. Otherwise they don’t contain a value.
The fields for PivotTable and Chart show whether the field is used there. As long as a Cube only have one PivotTable or Chart object, the content can be changed directly in the Field List as before. If no PivotTable or Chart object exists, it will be automatically created when a field is defined for it. However, the object will not be deleted automatically when no field is defined for it anymore. If more than one possible object exists, the field must be added or removed directly in the corresponding object.
Added new function Suggest XML Tags for XML – File Export Cubes. This function creates (based on the Caption) Formulas for XML formatting.
If the options Create Table and Totals Row in the FastTab Advanced are activated a Total Row will be added to the Table. The type of the result can be specified for each field in the Field List of the Data Source with the new field Excel Table Totals Row.
If a Page is selected in the field NAV Page Link a link to the Record in NAV will be added to the field in Excel.
Removed field Table Filter Date Formula in the Field Filter window.
JOIN:
You can now specify the Sorting Order.
Added new option Existing links between Table and JOIN Table to the "Suggest Link" function. This option is searching for relations between the tables and will suggest them.
You can now add as many Charts as needed to a Cube.
Removed field Chart Template.
You can now specify the Chart Type and the Chart Layout.
Embedded Charts will be created directly in the Data Source Sheet.
If no fields are specified in the Chart Field List all fields from the Data Source will be added to the Chart. Via Add/Remove you can add and remove fields to the Chart Field List.
Within the Type you can change the position of the field via Up and Down.
You can now add as many PivotTables as needed to a Cube.
With the PivotChart option you can format the PivotTable as a Chart. Embedded Charts will be created directly in the PivotTable Sheet.
Via Add/Remove you can add and remove fields to the PivotTable Field List.
Via Edit you can specify the field details like Subtotals.
The Virtual Value Field will be created automatically.
Via Edit you can move the Virtual Value Field from Column Label to Row Label and vice versa.
Within the Type you can change the position of the field via Up and Down.
Create:
Create Permission – Selected Users
Added 2 new actions to the Selected Users Page:
Add all Users and Add Users from Area
With Add Users from Area you can add assigned Users from different areas.
My Cubes:
For My Cube users is is now possible to determine whether the Cube should be created New or from the last archived file with the new field My Cube Creation Type.
With Selection the user can decide for himself.
The option can be overridden in the My Cubes Selections window.
Companies:
The Company Filter is now specified in the Permissions FastTab.
Characters:
In the Excluded Characters window you can exclude or convert certain characters from the Cube creation.
The PowerPivot Format option in earlier versions is no longer required and therefore removed.
If only a Character is specified it will be excluded. With Convert to you can replace the Character with another Character.
The Replace with Space option allows a conversion to a Space.
Alternatively also the Integer values of the characters can be specified.
The FastTab Excel now only contains the Template and Print functionality.
All other Excel settings are specified in the respective Cube objects.
Template:
If the option Refresh Template is activated and the Template contains external text file links, no confirmation message from Excel will be displayed.
The external content will be updated automatically.
The fields Add Date and Add Time can now be specified separately.
This (for example) allows you to create/update only one file per day.
Open File now offers the option to open the file or the folder.
Both can also be set to Ask so that the user can decide whether to open the file/folder after the creation of the Cube.
The Power Pivot Format option has been removed. Removing the quotes can now be handled with Excludes Characters in the Permissions FastTab.
File Export Cubes no longer create File Export Entries. If the Archive is enabled the information will be stored in the Archive Entries.
If a File Export Cube is created with the Job Queue it is now possible to create only an Archive File.
This is useful for File Export since the export so selectively either can create real files or only save the file in the Archive.
The archived file can later be used for creation by users via My Cubes.
Renamed Log to Archive.
You can now specify if the Date, Time and User ID should be added to the Archive File file name.
Existing archive files will be overwritten automatically.
This makes it possible to create less archive files.
Excel Cubes will be archived as Excel Templates.
Archive files can now be stored Server-Side.
With Save Archive File via Server you can protect the archived files from direct access by the user.
In this case the Client does not need permissions to access the Archive Folder but the folder must be valid for the Server.
Archive Entries now also contain the Destination File information from File Export Cubes.
The Object Type to Run must be Codeunit and the Object ID to Run must be 1022109 (NCC Create via Job Queue). In the field Parameter String the Code of the Cube must be specified.
Job Queue Excel Cube:
If an Excel Cube is created via the Job Queue, first the Cube will be created.
If the Print option in the FastTab Excel is set to Print, the Cube will be printed.
If the Archive is activated and the Archive File option is enabled, the Cube will be archived.
Excel will not be opened but the Cube will be created in the background.
Job Queue File Export Cube:
If a File Export Cube is created via the Job Queue, first the Cube will be created.
Options like Show Save As or Open File from the FastTab File Export will be ignored.
If the option Only Archive File is enabled, the file will not be saved in the Export Folder but only in the Archive Folder (In this case the Archive must be enabled).
If the option Only Archive File is not enabled, the file will be saved in the Export Folder and archived only if Archive and Archive File are enabled.
In combination with Save Archive File via Server and My Cube Creation Type different requirements can be covered.
Result for 1:n JOIN – Line Break = Yes:
Result for 1:n JOIN – Line Break = Yes:
