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

All responses (4)

Answers (1)

André Arnaud de Cal... 296,460 Super User 2025 Season 2 on at

Like (0)

Report

Customer Ageing Report SQL query.

Hi,

Yes, for reports where you need to have an overview of a situation in the past, you will need the CustSettlement table.
KP-31070522-0 15 on at

Like (0)

Report

Customer Ageing Report SQL query.

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?
Sohaib Cheema 46,877 User Group Leader on at

Like (1)

Report

Customer Ageing Report SQL query.

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.
Verified answer

André Arnaud de Cal... 296,460 Super User 2025 Season 2 on at

Like (2)

Report

Customer Ageing Report SQL query.

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.

Community site session details

Customer Ageing Report SQL query.

Helpful resources

News and Announcements

Quick Links

Announcing our 2025 Season 2 Super Users!

Ramesh Kumar – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Customer Ageing Report SQL query.

Helpful resources

News and Announcements

Quick Links

Announcing our 2025 Season 2 Super Users!

Ramesh Kumar – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates