Create report data items and their properties
Creating a logical design for a report means defining the data model, which also involves defining how the data for the report is collected, including:
Tables that are used by the report by defining data items.
Relationships between data items if the report uses more than one table.
Key, sort order, and filters to use with the data items.
Elements that the user can change at run time.
Grouping of data.
Calculation of subtotals and totals.
Typically, data items and columns correspond to fields in a table. When the report is run, each data item is iterated for all records in the underlying table, filters are applied, and the dataset is created. When a report is based on more than one table, you must set relations between the data items so that you can retrieve and organize the data.
You can build the report dataset from data items and columns. A data item is a table. A column can be the following entities:
A field in a table
A variable
An expression
A text constant
You can define a data model by adding data items, which specifies the tables that the report reads. Any table that is listed is read in its entirety when the report runs. You can link and filter data items to limit the number of records that the report reads and to determine how tables are joined.
A data item has the following construct:
dataitem(DataItemName; SourceTableName)
{
}
The SourceTableName is the name of the table and the DataItemName is the name that you give to the data item. The DataItemName must be a Common Language Specification (CLS) compliant name, which means that it can't contain special characters, except for the underscore.
A report can read the same table multiple times if the table is listed multiple times. For example, you can set up a report to read customer records twice: first, the customers who are not blocked, followed by the customers who are blocked.
The listed tables become data items in the report. Data items are record variables that enable the report to access table data by using code. The order in which the data items are listed is the order in which the report reads the data from the tables.
When you add two data items after each other, the tables are concatenated to the dataset in the corresponding columns of each data item.
The following example shows a dataset that contains two data items. The first data item links to the customer table and the second data item links to the vendor table:
dataset
{
dataitem(Customer;Customer)
{
}
dataitem(Vendor;Vendor)
{
}
}
If a data item is indented under another data item (the parent data item), the report reads every record from the parent data item and all records from the indented data item, including any filters or links. In that case, the tables are joined.
The following example shows a dataset that contains two nested data items. The first data item links to the customer table and the second data item links to the sales line table:
dataset
{
dataitem(Customer;Customer)
{
dataitem("Sales Line";"Sales Line")
{
DataItemLinkReference = Customer;
DataItemLink = "Bill-to Customer No." = field("No.");
}
}
}
This concept resembles a nested FOR loop. For example, if the Sales Line table is added as a data item in the data item of the Customer table, the report reads the first customer, and then the relevant sales line information for that customer, before it moves to the next customer.
Each data item has the DataItemLinkReference and the DataItemLink properties. These properties define a master/detail relationship between two tables.
In the DataItemLinkReference property of a data item, select the parent data item to link to (the data item must be a part of the parent data item). After you specify the DataItemLinkReference value, use the DataItemLink property to specify a field from each data item on which to base the link. In the DataItemLink property of the indented data item, specify a link for this data item by matching fields in this data item with fields from the parent data item.
The previous example illustrates how to use this property. In the example are two data items: parent and child. The parent data item is a list of customers, called Customer. The child data item contains a record of all sales orders. In the Customer table, the primary key is the customer number, which is stored in the No. field. In the table that contains sales data, each record contains this customer number as a foreign key in the Bill-to Customer No. field. You can set the value of the DataItemLinkReference property on the child data item to the parent data item. Additionally, you can set the DataItemLink property of the child data item to the following value:
"Bill-to Customer No." = field("No.");
You now have a filter on the records in the child data item. This filter only displays records that pertain to the currently selected customer record.
If you omit the DataItemLink property, then the two tables will be linked by using a cross join, meaning that all records from the first data item will be linked to all records from the second data item. Try to avoid this situation because it will link records from both tables that are not related from a business point of view.
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. (correlates to a left or inner join)
This property has no effect on a data item that doesn't have child data items. If this property is false, and no record exists in the child data item that corresponds to the current record in the parent data item, then the report prints data from the current record in the parent data item, even though no data exists for the child data item. If this property is true, and no record exists in the child data item that corresponds to the current record in the parent data item, then the report doesn't print data from the current record in the parent data item. If more than two data items exist, then the report iterates through each parent-child relationship in the same way.
In the following example, the PrintOnlyIfDetail property has been set on the Customer data item:
dataitem(Customer; Customer)
{
PrintOnlyIfDetail = true;
dataitem("Sales Line"; "Sales Line")
{
DataItemLinkReference = Customer;
DataItemLink = "Bill-to Customer No." = field("No.");
}
}
As a result, when a customer record is retrieved from the Customer table, and if no related sales lines exist for this customer, then the customer record won't be added to the dataset. You can consider setting the PrintOnlyIfDetail property to true, which is similar to implementing an INNER JOIN in a SQL query. Alternatively, setting the PrintOnlyIfDetail property to false, which is the default value, is similar to implementing a LEFT OUTER JOIN in a SQL query.
The DataItemTableView property sets the key on which to sort, the sort order, and the filters for the data item. Often, this property is used to apply a filter on the data item.
For example, if you don't want all records from the Customer table in the dataset but instead only want the non-blocked customers, you can use the following filter:
dataitem(Customer; Customer)
{
PrintOnlyIfDetail = true;
DataItemTableView = where(Blocked=filter(false));
}
The DataItemTableView property affects the following situations:
If you set a key, then the data item does not have a FastTab on the Request page and the users can't select a key for sorting, sort order, or filters for the data item.
If you set a sort order, then this sort order is used for the report, regardless of the sort order that the user selects on the Request page.
If you set a filter, then this filter is not displayed on the request page, but it's used along with any filters that the user specifies on the Request page.
Setting a sort order, a filter, or both, does not prevent users from selecting a sort field on the Request page.
You can use the Ctrl+Space keyboard shortcut in Visual Studio Code to get an overview of the available data item properties.
