​Microsoft 365 Business Central: Reporting With Nested Data

Jared Drueco | June 9, 2022

This week I was tasked to create a more complex report script where I needed to extract data from a table indirectly linked to the current report/extension.

Business Central pages are usually linked together such that a filter is placed on the data of the current page based on some field from the previous page. In this post we’re going to look at using these connected paths/links to access data we need for a report.

Linking Different Table Data

Say you are working on the “Customer Statement” report and you need to display the following:

For a specific customer,
Grab all of their posted sales invoices,
For each posted sales invoice,
Grab data on the purchased items

We can think of a report’s dataset as nested containers.

Above is a code snippet of the default code for a customer statement report. We can see that the “Customer” data item/container holds child data item, “Integer”, and each container holds table data fields as columns.

How do we know the hierarchy of parent and child data items? To figure this out follow that path from the “Customer Card” page to the Posted “Sales Invoice Lines” in Business Central

We can see that each page holds some link to the next one. This tells us the way we access the nested data. It is important to note that the data is accessed by the table on the current page which we can see in the page inspector (right panel of images).

Above is a code snippet of the a customer statement extension that grabs data from a specific customer’s posted sales invoice lines.

Finally, in order to access data for the a specific customer we need to use the DataItemTableView and DataItemLink properties. This is the filter that grabs table rows only for a single customer’s posted invoices.

DataItemTableView is what we want to sort the table rows by. In the Posted Sales Invoices page we can see that a filter is set on “Customer No.” which indicates that we should sort the Sales Invoice Header data item by that field(”No.”).

DataItemLink is what defines the link between the child and parent data item. By applying the property:
   DataItemLink = “Sell-to Customer No.” = field(”No.”)

We are only grabbing Posted Sales Invoices (the child data item) whose “Sell-to Customer No.” field matches the Customer’s (the parent data item) “No.” field.