Dynamics 365 Business Central: Using the Date Method to retrieve only the Day, Month, and Year Data in Queries [Group by Year-month]

Dynamics 365 Business Central

Hi, Readers.
I was asked last week that a customer needs the data to be listed under the Year-Month group, is there any easy way to do it in Business Central?

For example: For data in Item Ledger Entries

Item No.YearMonthSales QuantitySales Amount
1928-S20219102000
1960-S20219112001
1988-S20219122002
1928-S202110132003
1960-S202110142004
1988-S202110152005

So in this post, I would like to talk about how to use the Date Method to retrieve only the Day, Month, and Year Data in Queries.

When you have fields in a table that contain dates, you can use a Date method to retrieve only the year, month, or day instead of including the date in the resulting dataset of a query.

This is relatively simple and more efficient, and will increases the performance of Business Central.

To set up a Date Method on a query column, set the Method Property to Day, Month, and Year.

Property Value of Method Property:

ValueDescription
DayGets the day from the date expression for the field in the query column. The day is returned as an integer, in the range of 1 to 31, which represents the day of the month. If the day in the date expression is 0, then 1 is returned.
MonthGets the month from the date expression for the field in the query column. The month is returned as an integer, in the range of 1 to 12, where 1 represents January and 12 represents December. If the month in the date expression is 0, then 1 is returned.
YearGets the year from the date expression for the field in the query column. The year is returned as an integer. If the year in the date expression is 0, then 1900 is returned.
SumAdds the values of all fields for the specified column within a group.
CountReturns the number of rows that are included in the dataset within a group. Note: The Count method is associated with the DataItem and not with a specific column, so the DataSource property must be blank.
AverageCalculates the average value of the fields in the column within a group.

When averaging fields that have an integer data type (such as Integer or BigInteger), integer division is used. This means that result is not rounded, and the remainder is discarded. For example, 5÷2=2 instead of 2.5 (or 2 1/2).
MinRetrieves the lowest value of fields in the column within a group.
MaxRetrieves the highest value of fields in the column within a group.

Let’s go into some more details:

Year Method

The Year method gets the year from the date expression of a field value in the query column. The year is returned as an integer. If the year in the date expression is 0, then 1900 is returned.

The Result:

Month method

The Month method retrieves the month from the date expression of a field value in the query column. The month is returned as an integer, in the range of 1 to 12, where 1 represents January and 12 represents December. If the month in the date expression is 0, then 1 is returned.

Note: Same months in different years will be totaled.

For example, the Sales Quantity of 1896-S in February is -30.

This includes data for February 2020 and February 2021.

Day Method

The Day method retrieves the day from the date expression of a field value in the query column. The day is returned as an integer, in the range of 1 to 31, which represents the day of the month. If the day in the date expression is 0, then 1 is returned.

Note: The same as Month method, same day in different years and months will be totaled.

Group by Year-Month

Let’s return to the question, how to use the Date Method to group by Year-Month?
The hierarchy of the dataitem is important in Queries because it will determine the sequence in which data items are linked, which in turn will control the results.

So you can add another dataItem and link them with Posting Date. Set Method=year in the upper level and Method=Month in the lower level.

For example:

Source Code: for reference only

query 50102 ItemLedgerEntriesGroupByMonth
{
    QueryType = Normal;
    Caption = 'Item Ledger Entries Group By Month';
    QueryCategory = 'Item List';

    elements
    {
        dataitem(Item_Ledger_Entry; "Item Ledger Entry")
        {
            DataItemTableFilter = "Entry Type" = const(1), "Source Type" = const(1);//Entry Type::Sales, Source Type::Customer
            column(Item_No_; "Item No.")
            {
                Caption = 'Item No.';
            }
            column(Posting_Date_Year; "Posting Date")
            {
                Caption = 'Year';
                Method = Year;
            }
            dataitem(Item_Ledger_Entry_Month; "Item Ledger Entry")
            {
                DataItemLink = "Item No." = Item_Ledger_Entry."Item No.", "Posting Date" = Item_Ledger_Entry."Posting Date";
                DataItemTableFilter = "Entry Type" = const(1), "Source Type" = const(1);//Entry Type::Sales, Source Type::Customer
                column(Posting_Date_Month; "Posting Date")
                {
                    Caption = 'Month';
                    Method = Month;
                }
                column(Quantity; Quantity)
                {
                    Caption = 'Sales Quantity';
                    Method = Sum;
                }
                column(Sales_Amount__Actual_; "Sales Amount (Actual)")
                {
                    Caption = 'Sales Amount (Actual)';
                    Method = Sum;
                }
            }
        }
    }
}

We can use Excel to do a data check.

PS:
1. You can only use the date methods on fields of the Date and DateTime data type.

2. I have created three simple examples that I hope will help: GitHub | DataGroupByMonth

Test Video:

Find out more about Retrieving Date Data in Queries from Microsoft Docs.

Other information about Queries:
QueryCategory property – Smartlist? (Execute Queries from Pages)

Using the query as a data source for a page (Query.Open Method)

END

Hope this will help.

Thanks for reading.

ZHU

コメント

タイトルとURLをコピーしました