Customer Ageing Report SQL query.

(4) Share

Report

Posted on by KP-31070522-0

Hi, I am trying to create the customer ageing report sql query logic but not getting the correct balance when going for the back dates instead of current date.
Below is the SQL query for the raw data and the buckets I have created in Power BI for the Due Date.
 
SELECT
    custtrans.dataareaid AS Company,
    custtrans.accountnum AS customerNo,
    custtable.custgroup AS customerPostingGroup,
    custtrans.currencycode AS currencyCode,
    custtransopen.amountmst AS Balance,
    --CASE
    -- WHEN `` = 'Sales order' THEN custtrans.amountmst ELSE 0
   -- END AS salesLCY,
    custtrans.invoice AS documentNo,
    custtrans.duedate AS DueDate,
    custtrans.transtype_$label AS documentType,
    custtrans.transdate AS postingDate,
    custtrans.voucher AS customerLedgerEntryNo,
    dirpartytable.name AS CustomerName
FROM custtrans
LEFT JOIN custtransopen ON
    custtrans.accountnum = custtransopen.accountnum
    AND custtrans.dataareaid = custtransopen.dataareaid
    AND custtrans.recid = custtransopen.refrecid
LEFT JOIN custtable ON
    custtrans.accountnum = custtable.accountnum AND
    custtrans.dataareaid = custtable.dataareaid
LEFT JOIN dirpartytable ON
    custtable.party = dirpartytable.recid

Categories:

Microsoft Dynamics AX

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Verified answer

André Arnaud de Cal... 303,780 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

Hi,

Somehow your question was created in the old group for Dynamics AX. I have moved it to the actual Dynamics AX forum. Can you confirm your version of Dynamics?

What exactly do you mean by back dates? Do you want to run the report for e.g. end of June? In your query, I don't see a date range. If the date range is set in your Power BI report, then you need to gather other transactional data.

Then you need to have the amount from the CustTrans record as the CustTransOpen table only has information about current open amounts.

The historical amounts can be calculated if you take details from the CustSettlement table into account of your Power BI report. Then you can check on what date the transaction was settled and which payments are marked against what invoices.

Was this reply helpful? Yes No
Sohaib Cheema 49,677 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Andre's suggestion is good.

I also see that you do not have any bucket's logic (such as 1-30 days, 30-60 days, 60-90 days and so on). Looks like you want to get the balance due as of today (that would not be aging report but balance report). For Aging report, it needs a direction (forward, backward) and date criteria.

Was this reply helpful? Yes No
KP-31070522-0 15 on at

Like (0)

Report
Copy link

Link copied!

Hi @André Arnaud de Calavon,

Here back dates means user defined dates it can be June, May or others. And the date range is set in the Power BI. And I am getting all the transactional data in Power BI.

So do I need to consider the CustSettlement transactions also?

Was this reply helpful? Yes No
André Arnaud de Cal... 303,780 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Yes, for reports where you need to have an overview of a situation in the past, you will need the CustSettlement table.

Was this reply helpful? Yes No
Suggested answer

Navneeth Nagrajan 2,550 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi KP-31070522-0,

As Andre mentioned, go ahead and use the Customer settlement table. In addition to this, the field to refer to in customer transactions and customer settlements will be the ReportingCurrencyAmount in PowerBI. Hope this helps.

Was this reply helpful? Yes No