Previous

Next

Aggregate and sort data in a query

Completed

In a query, you can use a totals method to perform a calculation on a column and return the calculated value in the resulting dataset. This calculation is frequently called aggregation.

Typically, you would use aggregations with grouping to find totals for specified groups of columns. For example, you can sum the values in the Amount field for each G/L account in the G/L Entry table or find the Average Quantity per item in the Item Ledger Entry table. Totals methods in Business Central queries correspond directly to SQL Server aggregate functions.

You can use the following totals methods to complete specific tasks:

• Sum - Calculates the sum of the values of the specified column for all records that are grouped within the dataset.

• Average - Calculates the average value in the specified column in a group.

• Min - Retrieves the lowest value in the specified column in a group.

• Max - Retrieves the highest value in the specified column in a group.

• Count - Returns the number of records from data item tables that represent a group in the dataset.

The ensuing code example shows how the Sum method is used in the Method property of the Qty. (Base) field.

query 7345 "Avail Qty. (Base) In QC Bins"
{
    Caption = 'Avail Qty. (Base) In QC Bins';

    elements
    {
        dataitem(Location; Location)
        {
            DataItemTableFilter = "Directed Put-away and Pick" = const(true);
            dataitem(Warehouse_Entry; "Warehouse Entry")
            {
                DataItemLink = "Location Code" = Location.Code;
                SqlJoinType = InnerJoin;
                ...
                column(Sum_Qty_Base; "Qty. (Base)")
                {
                    ColumnFilter = Sum_Qty_Base = filter(> 0);
                    Method = Sum;
                }
                ...
            }
        }
    }
}

You can order the data in a resulting dataset of a query by any number of columns that are contained in the query. To define the sorting order, set the OrderBy property on the query. To sort on multiple columns, separate each column with a comma.

query 7345 "Avail Qty. (Base) In QC Bins"
{
    Caption = 'Avail Qty. (Base) In QC Bins';
    OrderBy = descending(Sum_Qty_Base);
    ...
}

You can't sort by a column that is not present in the resulting dataset. If you want to order by such a column, you must first add it to the query model and then include it in the OrderBy property.

When retrieving Date or DateTime fields from a Business Central database, you might want to retrieve only the year, month, or day instead of the entire date. This notion is especially true when you group and total the data. For example, you might want to group revenue by customer and by year or find average consumption of a production BOM component by month. You can achieve this task by selecting a date method for a Date or DateTime column in the query.

Date methods include:

• Day

• Month

• Year

When you use SQL server, date and time values are processed by using Coordinated Universal Time (UTC). If a Business Central solution uses a time zone other than UTC, and the field on which you apply the date method has a data type of DateTime, then a difference might occur between the date value that is returned in the dataset for the field and the actual day, month, or year for the field in the table. This situation occurs when the corresponding UTC date for a field falls on the next day or previous day because of the time of day and the time zone of Business Central.

The differences in day, month, or year occur when date and time values are retrieved from the Business Central database table. These values are converted from the regional settings of the Business Central solution to the UTC date and time. The day, month, or year is calculated on SQL server and then returned to the query dataset as an integer. This integer does not consider the regional settings of a Business Central solution.

To avoid this condition, you should use the date method on fields that have a Date data type instead of a DateTime data type, when possible. You can also return the DateTime value and implement post processing for the day, month, and year, as needed.

Continue