Open any
client's finance folder and you will find it. Six versions of the same
spreadsheet. Customer Aging - Oct.xlsx, Customer Aging - Oct (2).xlsx, Customer
Aging FINAL.xlsx, Customer Aging FINAL copy.xlsx. All exported from Business
Central.
Nobody built
that folder on purpose. It exists because someone once needed to group customer
entries by salesperson and month, could not do it on the list page, and hit
Open in Excel. Then they needed it again next month.
What
actually happens
Hit “Enter
analysis mode” and the page changes shape. The normal action bar is replaced by
an analysis bar, and the screen splits into two halves.
On the left is the data area, with a summary bar along the bottom and the analysis views bar across the top. On the right are two panes: Columns and Analysis filters.
Nothing you do
here touches the underlying data, and nothing you do changes the page for
anyone else. That is worth saying to a nervous client before you let them click
anything.
Analysis
views are the point. The bar at the top starts with one view called Analysis 1.
Each view holds its own columns, its own filters, its own pivot arrangement.
You might have one for aged balances, one for your top twenty customers, one
filtered to overdue items only. They persist between sessions; they are yours
alone, and you can rename, duplicate, move, or delete them. Analysis 1 cannot
be deleted, only renamed, and Delete All leaves it standing.
The Columns
pane has rules. Row Groups accept non-numeric fields only: text, date, time.
Values accept only fields that can be summed. Drag something into the wrong
area and the client refuses.
One subtlety
worth knowing: if you have personalized the page to add a field, it shows up in
the Columns pane with its checkbox cleared. It is there; it is just not
switched on.
Date
hierarchies are generated for you. For each
date field in the dataset, Business Central creates three additional fields
named after it: Posting Date Year, Posting Date Quarter, and Posting Date
Month. Analysing Customer Ledger Entries, you also get the same trio for
Document Date, Due Date, and every other date on the page.
The hierarchy is not the field you expand into. It is three fields you stack in Row Groups, and stacking them is what produces the expandable years, quarters, and months with subtotals at each level.
Pivot mode
does what you expect. Toggle it on and a Column Labels area appears alongside
Row Groups and Values. Rows down the side, labels across the top, sums in the
middle. Same model as Excel PivotTable, deliberately.
Note: Columns that only have a few possible values are the best candidates for use in column Values.
It reaches into related tables. “Add columns from” option on the Analysis context menu lets you pull fields in from tables related to the page's source table, and group by them.
And you can export the definition as JSON. Not the data, the analysis itself: columns, filters, arrangement. Which means an analysis view can be packaged into an extension and shipped to a client rather than described in a training document.
Analysis mode needs execute permission on system object 9640, Allow Data Analysis mode, normally granted through the DATA ANALYSIS - EXEC permission set. Most full users have it. Team Member and other limited roles often do not, and permission sets built years ago certainly do not. When a client says the button is not there, check this first. Five minutes, fixes it for a whole department.
Two other reasons
can be missing. Developers can switch it off per page with the
AnalysisModeEnabled property, so if it is absent on exactly one page, go
looking there. And analysis mode is not supported on lists that use
indentation, which means Chart of Accounts and the G/L Account List do not have
it at all. That is unfortunate, because the Chart of Accounts is the first
place a finance person will try. Have the answer ready.
Date
hierarchies use the calendar year. Not your client's fiscal year.
The
generated Year, Quarter, and Month fields are built on the normal calendar.
They do not know about any fiscal calendar defined in Business Central.
If your
client's fiscal year starts in April, their Q1 in analysis mode is January to
March and their Q1 in every financial report they have ever run is from April
to June. No errors. The numbers are correct for the calendar periods they
describe. They just do not match the numbers in the meeting.
Also worth
knowing: the hierarchy only generates for fields of type Date. Datetime fields
do not get one.
Calculated
fields are the ones computed on the page rather than read from the database:
running totals, percentages, conditional counts. They stop displaying in two
situations. When the list goes over 100,000 rows, and whenever you add fields
from a related table.
Try it
yourself
Ten minutes,
and you will have something worth showing a client. This is roughly Microsoft's
own aged receivables example with the sharp edges labelled.
1.
Open
Customer Ledger Entries and choose Enter analysis mode.
2.
In
the Columns pane, clear every column at once using the checkbox beside the
Search field. Start from nothing.
3.
Turn
on Pivot Mode.
4.
Drag
Customer Name into Row Groups and Remaining Amount into Values.
5.
Drag
Due Date Month into Column labels. Twelve columns, safely inside the
cardinality limit.
6.
Use
Analysis filters to narrow to one year. Note that this filter lives on the
view, not the page, so your other views are untouched.
7.
Rename
the view to Aged Accounts by Month.
Then, to see
the hierarchy properly:
8.
Add
a second view. Put Posting Date Year, Posting Date Quarter, and Posting Date
Month into Row Groups in that order, and Remaining Amount into Values.
9.
Expand
a year, then a quarter. Watch the subtotals appear at each level and the record
count beside each group.
10.
Use
Copy link from the view's dropdown. In the dialog, look at the Company field:
you can link to your current company or deliberately not link to any company at
all. Recipients get prompted to name their own copy of the view.
