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
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 ;-)
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.
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.
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
Here's an example:
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (