Link, order, and indent data items

Completed

The following examples illustrate most of the typical patterns for dataset design in Microsoft Dynamics 365 Business Central reports. Various combinations of these patterns appear in real-life Business Central reports.

The way that you add data items to a report determines how records are added in the runtime dataset of the report. Understanding how the runtime dataset of a report is created helps when you are designing the report layout. Furthermore, creating a good dataset without adding or duplicating unnecessary information is considered a good practice in report design.

This first example demonstrates two data items that are not linked or indented, for example, when you add a data item for customers and another data item for vendors:

        dataitem(Customer; Customer)
        {
            column(CustomerNo; "No.")
            {
            }
            column(CustomerName; Name)
            {
            }
        }
        dataitem(Vendor; Vendor)
        {
            column(VendorNo; "No.")
            {
            }
            column(VendorName; Name)
            {
            }
        }

In this example, the No. and Name columns from the Customer and Vendor tables are added in the dataset by using two data items after one another. The result is the following dataset.

As the preceding example shows, all records from both tables are added in the dataset in their corresponding columns.

The next example demonstrates two data items that are indented:

    dataset
    {
        dataitem(Customer; Customer)
        {
            column(CustomerNo; "No.")
            {
            }
            column(CustomerName; Name)
            {
            }
            dataitem(CustomerLedgers;"Cust. Ledger Entry")
            {
                column(CustomerLedgersCustomerNo;"Customer No.")
                {
                }
                column(CustomerLedgersAmountLCY;"Amount (LCY)")
                {
                }
            }
        }
    }

This change results in the following dataset.

As the preceding example shows, the dataset contains many records. The screenshot only shows the beginning. The actual dataset contains 48 pages of records. Clearly, something is wrong.

If you closely investigate this dataset, notice that the customer records and customer ledger entry records aren't linked or are linked incorrectly. The reason is because the DataItemLink property is missing. The report performs a cross join between the Customer table and the Customer Ledger Entry table, resulting in a large and useless dataset.

The following example changes the dataset and adds a DataItemLink:

        dataitem(Customer; Customer)
        {
            column(CustomerNo; "No.")
            {
            }
            column(CustomerName; Name)
            {
            }
            dataitem(CustomerLedgers; "Cust. Ledger Entry")
            {
                DataItemLinkReference = Customer;
                DataItemLink = "Customer No." = field("No.");
                column(CustomerLedgersCustomerNo; "Customer No.")
                {
                }
                column(CustomerLedgersAmountLCY; "Amount (LCY)")
                {
                }
            }
        }

This change results in the following dataset.

In the resulting dataset, the customer records are linked to the correct customer ledger entries. However, if a customer has no customer ledger entries, it's also added to the dataset, as shown in the first record.

By implementing the PrintOnlyIfDetail property, you can remove those records:

        dataitem(Customer; Customer)
        {
            PrintOnlyIfDetail = true;
            column(CustomerNo; "No.")
            {
            }
            column(CustomerName; Name)
            {
            }
            dataitem(CustomerLedgers; "Cust. Ledger Entry")
            {
                DataItemLinkReference = Customer;
                DataItemLink = "Customer No." = field("No.");
                column(CustomerLedgersCustomerNo; "Customer No.")
                {
                }
                column(CustomerLedgersAmountLCY; "Amount (LCY)")
                {
                }
            }
        }

The result is the following dataset.

The PrintOnlyIfDetail property specifies whether to print data in a report for the parent data item when the child data item doesn't generate output.