Skip to main content

Notifications

Announcements

No record found.

Small and medium business | Business Central, N...
Suggested answer

Summarize GL Entry Table by GL Account, Year/Month and Amount

Posted on by 10

Hi,

I have tried and failed building  an API Query in Business Central SaaS to summarize the "G/L Entry" . I need to use this Query in Power BI as the data source for financial reporting.
The challenge I faced is not being able to summarize it at that granularity, The Posting Date column of the table can not be converted to month end date and summarized.

Basically, I need the summary the GL Entry by monthly:   GL Account, EndofMonth, and Amount 

Appreciate your help

Fowmy

  • Suggested answer
    Steven Renders Profile Picture
    Steven Renders 5,051 Super User 2024 Season 1 on at
    RE: Summarize GL Entry Table by GL Account, Year/Month and Amount

    Correct AL queries are limited. Pages too. The only way to do it in AL is via a page api that uses a query as a source and then groups the data in a temp table via some AL programming, but imho that will be very slow…

    The best is the query with groups as I suggested in combination with eom column in PQ and then a group in PQ.

    What you want is simply not possible using a query object in AL.

    I feel your pain ;-)

  • M.Fowmy Profile Picture
    M.Fowmy 10 on at
    RE: Summarize GL Entry Table by GL Account, Year/Month and Amount

    The Aggregation of SUM on Amount groups by other columns in the Query. The problem is if I choose GL Account, Posting Date and Apply SUM on Amount it will not summarize by month, it will only summarize by each date.

    Example: If I have 10 entries for GL account 1000 for Dates 1/1/2023 to 01/10/2023, I need to get a single line

    GL Account: 1000

    Date: 01/31/2023

    Amount: XXX

    Even If I apply Method on Posting Date as Year and another columns for Posting Date by Month , It doesn't work.

    I believe Query capabilities are very much limited here.

  • Suggested answer
    Steven Renders Profile Picture
    Steven Renders 5,051 Super User 2024 Season 1 on at
    RE: Summarize GL Entry Table by GL Account, Year/Month and Amount

    The

    column(amount; Amount)

               { Method = Sum; }

    creates a group by on all other columns...

    learn.microsoft.com/.../devenv-query-totals-grouping

    Setting an aggregate method on a column will automatically group the resultant data set by the other columns in the query. Records that have matching values for the other columns are grouped together into a single row in the results. The aggregate method is then applied against the group and a summary value returned in the row. It's similar to the GROUP BY clause in SQL SELECT statements.

    Query objects don't support calculated colums in AL.

    Or you can develop a Page API, where you add the EOM column:
    learn.microsoft.com/.../devenv-develop-custom-api

    However, then there's no grouping, as Page APIs don't support grouping.

  • M.Fowmy Profile Picture
    M.Fowmy 10 on at
    RE: Summarize GL Entry Table by GL Account, Year/Month and Amount

    Thanks for the reply,

    By pulling all the records into Power Query, it increase the number of records to be imported into VertiPaq resulting in slow refresh process and bigger file size. The solution I am looking for is to summary it at source level, I mean in the Query itself like we do in SQL grouping by EOMONTH function on Posting Date.

    Thanks again

  • Steven Renders Profile Picture
    Steven Renders 5,051 Super User 2024 Season 1 on at
    RE: Summarize GL Entry Table by GL Account, Year/Month and Amount

    Here's an example:

    query 50202 generalLedger
    {
        APIGroup = 'powerBI';
        APIPublisher = 'xxx';
        APIVersion = 'v1.0';
        EntityName = 'generalLedger';
        EntitySetName = 'generalLedger';
        QueryType = API;
        DataAccessIntent = ReadOnly;

        elements
        {
            dataitem(gLEntry; "G/L Entry")
            {
                column(systemModifiedAt; SystemModifiedAt)
                { }
                column(glAccountNo; "G/L Account No.")
                { }
                column(documentType; "Document Type")
                { }
                column(documentNumber; "Document No.")
                { }
                column(sourceType; "Source Type")
                { }
                column(sourceNo; "Source No.")
                { }
                column(sourceCode; "Source Code")
                { }
                column(dimensionSetID; "Dimension Set ID")
                { }
                column(postingDate; "Posting Date")
                { }
                column(amount; Amount)
                { Method = Sum; }
                column(creditAmount; "Credit Amount")
                { Method = Sum; }
                column(debitAmount; "Debit Amount")
                { Method = Sum; }
            }
        }

        trigger OnBeforeOpen()
        begin

        end;
    }
    Remove/Add any columns you might need.
    For en EOM dates, do that in Power Query. Add a calculated column in PQ that calculates the EOM, and then a group.
    easy peasy lemon squeezy ;-)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans