Kim Dallefeld, MVP, MCT, MCP
Business Central’s Analysis Mode in version 26.2.
What Microsoft added to this version for Analysis Mode is nothing short of life changing! While the Analysis Mode feature, in and of itself is amazing, we now can add fields from other tables to the views and use them like they were there all along. In other words, fields from other tables can be used to group and pivot, set filters, freeze left or right, etc.
As you start to add fields, know that only fields that have table relations to other tables are shown. For us that are not technical, this means that the system must know how to find the field you want to add so you can only add fields with predefine relationships. Don’t fear the list is very good.
One of my favorite uses of Analysis Mode is to go to Posted Sales Invoice Lines, apply some filters and get some very good sales analyses.
The following screenshot shows you how to get started adding a column from another table.
Let’s say in my example that I want the customer contact name. I know that the contact name is on the header or I could get from the customer record. In the above screen I would click on Customer and the following list of columns that I can add from the customer record will appear.
I scroll to the desired field or I can select multiple fields and I press OK. Note that the field is added to the far right but can be moved wherever you desire.
After removing some columns, I added a grouping by the customer contact field.
The contact name is not part of the posted sales invoice lines table data but by using the add columns from related tables, the above analysis has subtotals by contact. This is a very simple example of an absolutely outstanding feature.
It takes a bit to get comfortable with Analysis Mode but its well worth your time for the power that is brings. Here’s the cream…..you can’t mess anything up, your analysis model may not work as you thought but you can delete and start again or better yet just make some changes and try again.
Notes:
- Feature Management – you must be upgraded to version 26.2 and you must enable “Feature: Add fields from related tables in analysis mode”
- Co-Pilot will not help build analysis using fields from other tables (yet)
- Calculated columns are not supported
- Make sure that you don’t just ‘highlight’ the field and click OK. You must use the Select More feature and then click OK
- After you select fields to add and close the screen, you will receive the following message:
I did run into this issue after I added the contact field and tried sorting by sales line type of item. I simply removed that filter and problem solved. Someone suggested to filter the posted sales invoice lines first and then more to analysis mode but I have not tried that scenario.
Give these new features a try and watch you reporting world explode into a new universe!!
See the recording of this blog at Using Analysis Views in Business Central.