Work with data analysis mode
Data analysis mode
In data analysis mode, the page is divided into two areas:
Main area - Consists of the data area, summary bar, and analysis views bar.
Data manipulation area - Consists of two panes: Columns and Analysis Filters.
Data area
The data area shows the rows and columns of the list page query and it summarizes data. The data area provides a versatile way to control the layout of columns and a quick way to get a summary of the data. For columns that contain numeric values, the last row shows the sum of all values in the column unless you define row groups. In this case, the sums appear as a subtotal for the groups.
You can complete the following actions in the data area:
Move a column by selecting it and dragging it to where it makes the most sense in your analysis.
Sort on a column by selecting the column header. To sort on multiple columns, select and hold the Shift key while selecting the column headers that you want to sort on.
Right-click the column or hover your cursor over it and select the menu icon to access several actions that you can do on columns, such as:
Pin a column to the left or right of the data area so that it doesn't move off the screen when you scroll. To do so, select Pin column > Pin left to pin the column part to the left of the data area.
Define data filters directly on the column definition instead of going to the Analysis Filters pane. You can still review details about related data and for each line, and you can open the card to learn more about a given entity.
Use the data area to interact with the data. For columns that contain numeric, summable values, you can get descriptive statistics on a set of fields by marking them. The statistics appear in the status bar along the lower part of the page.
Filter on all columns, including totals such as sum or count.
Export data in Excel or CSV format. Right-click the data area or on a selection of cells to export. The exported data reflects what you see on the screen. To keep full functionality in Excel, remove added related fields first, and if the list exceeds 100,000 rows, set filters to reduce the number of rows before you export.
Summary bar
The summary bar is on the lower part of the page and displays statistics about the data in the list page or query. As you interact with columns whose values can be summed, like selecting multiple rows in a column that displays amounts, the data updates.
The different numbers in the totals area are as follows:
Rows - The number of selected rows as a part of the total number of available rows.
Total rows - The number of rows in the unfiltered list or query.
Filtered - The number of rows that display as a result of the filters that you apply to the list or query.
Average - The average value in all selected summable fields.
Count - The number of selected rows.
Min - The minimum value in all selected summable fields.
Max - The maximum value in all selected summable fields.
Sum - The sum total of all values in the selected summable fields.
Columns pane
The Columns pane is one of two panes that work together to define your analysis. The other area is the Analysis Filters pane. You would use the Columns pane to summarize the data. Use the Columns pane to define which columns you want to include in the analysis.
Search/check or clear all boxes - Search for columns. Select the checkbox to select or clear all columns.
Checkboxes - This area includes a checkbox for each field in the list's or query's source table. Use this area to change what columns are shown. Select a checkbox to show a column for the field on the page; clear the checkbox to hide the column.
Row groups - Use this area to group and sum data by one or more fields. You can only include non-numeric fields, such as text, date, and time fields. Often, you'd use row groups in pivot mode.
Values - Use this area to specify fields that you want a sum total for. You can only include fields that contain numbers that you can add together. For example, you can't sum the text, date, or time fields.
To move a field from one area to another, select the grab icon next to the column in the list and then drag the field into the target area. You can't move a field into an area where's it's not allowed.
If you use personalization to add or remove fields on a list page, your choice of visibility is reflected in the Columns pane. An added field has its checkbox cleared. To include that field in an analysis definition, select the checkbox in the Columns pane.
Add fields from related tables
From Business Central version 26.2, you can add fields from tables that are related to the page's source table. For example, when you analyze the Customer page, you can add fields from the Location table and then group data by those related fields.
To add related fields, select Add columns from in the Analysis context menu and then choose one of the suggested related tables. In the Insert column(s) from dialog, select the fields that you want, which are then added to the Columns pane and the data area. To see all table suggestions, choose Other source. Use Remove related columns to remove related fields again.
To use this capability, you need the Add Related Fields permission set. In
versions earlier than 27.0, an administrator must also turn on the
Add fields from related tables in analysis mode feature key on the
Feature Management page. Keep in mind that all calculated fields are lost
when you add related fields.
Analysis Filters pane
In the Analysis Filters pane, you can set further data filters on columns to limit the entries in the list. Set filters on columns to limit the entries in the list and subsequent sums to only those entries that you're interested in based on a criteria that you define. For example, you're only interested in data for a specific customer or sales orders that exceed a specific amount. To set a filter, select the column, choose the comparison operation from the list (such as Equals or Starts with), and then enter the value.
The other filters only apply to the current analysis view. As a result, you can define exactly the extra data filters that you need for a specific analysis.
Analyze data with Copilot
Setting up an analysis view manually — choosing columns, applying filters, grouping rows — can take time when you just need a quick answer. Copilot's analysis assist feature lets you describe what you want in plain language and generates the analysis view layout for you.
For example, entering a prompt in item ledger entries like "show vendors by location sorted by cost amount of purchases" sets up the rows, columns, and sort order automatically, so you can focus on interpreting results rather than configuring the view.
To get started, open a list page and select Copilot > Analyze list in the action bar, or enter analysis mode first and then select Copilot > Create new analysis. Type your prompt in the dialog, select Generate, and Copilot creates a new analysis view with a suggested layout. If you need help with writing a prompt, select Prompt guide and choose one of the suggested options.
If the result isn't quite right, enter follow-up instructions in the Add more details about the analysis box and then select the Adjust arrow — Copilot remembers your previous instructions and builds on them. You can also adjust the layout manually or select Edit prompt to start over with a new prompt. Use Back and Next to review the different layouts that you created for the analysis view. When you're satisfied with the result, select Keep it to save the analysis view, or select Discard if you don't want to keep it.
Copilot can also include fields from related tables. For instance, prompting "group items by vendor's country/region" pulls in country/region data from the Vendor table automatically, saving you the manual step of personalizing the page first. Copilot performs better when you mention the related table that you want the extra fields from.
Note
Analysis assist is validated and supported in selected languages only. An administrator must activate the Analyze list capability on the Copilot & agent capabilities page and grant you the permissions that are required for analysis mode.
Analysis views
In the analysis views area in the upper part of the page, you can create different configurations (columns and analysis filters) on separate views, where you can manipulate data on the views independently of each other. At least one analysis view, called Analysis 1, always exists by default. Adding more views is beneficial for saving frequently used analysis configurations on a dataset. For example, you might have views for analyzing data in the pivot mode and other views that filter to a subset of rows. Some views might show a detailed view with many columns, while others only display a few key columns.
Some useful tips for working with multiple analysis views include:
To add a new analysis view, select the plus (+) sign next to the last analysis view.
Select the dropdown arrow on an analysis view to access a list of actions that you can do on a view, such as rename, duplicate, delete, and move.
Rename - This option gives the analysis view a new name and lets you add a description. The description appears as a tooltip when you hover over the view, which makes it easier to remember its purpose.
Delete - This option deletes the analysis view that you currently have open.
Delete All - This option deletes all analysis views that you add, except the default Analysis 1 view.
You can't completely remove the Analysis 1 view, but you can rename it by using the Rename action and then clear the changes that you made by using Delete or Delete All.
The analysis views that you add and configure remain until you delete them. Therefore, if you return to the data analysis mode again, the views appear exactly as you left them. The views that you set up are only visible to you. Other users can only view the views that they set up.
You can copy analysis views. Copying can be useful if you want to experiment with changing a view without changing the original or if you want to create different variations of the same analysis.