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

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

(1) Share

Report

Posted on by M.Fowmy

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

All responses (5)

Answers (0)

Suggested answer

Steven Renders 5,051 Super User 2024 Season 1 on at

Like (0)

Report

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 10 on at

Like (0)

Report

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 5,051 Super User 2024 Season 1 on at

Like (0)

Report

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 10 on at

Like (0)

Report

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 5,051 Super User 2024 Season 1 on at

Like (0)

Report

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 ;-)