NAV Report Sql Query Question

stomar_3

Dear Collegs,

I am currently bussy with Navision reports making with one BI tool. In that process I faced with one issue for which do not know exact solution.
Namely, my problem is that customer want report in which will be presented Outstanding Amounts of their customers ordered by Due Date.
Theoreticly it means that they want to see report which all open Invoices and Credit Notes with the Due Date information.

How much I understand NAV database and tabels for this report should to use two tabels :
CRONUS $Cust_ Ledger Entry and CRONUS $Detailed Cust_ Ledg_ Entry .

In the tabel CRONUS $Cust_ Ledger Entry is one column with the name Open which can have values 0 or 1. How much I understand if value is 0 it means that this document (Invoice or Credit Note) is closed and if value is 1 it means that document (Invoice or Credit Note) is Open.

If it translate to my report it means that is neccesery to Select only document for which column Open has value 1.
Something like this :
Select [CRONUS Mexico S_A_$Cust_ Ledger Entry1000000021].[Document No_] As DocNo,
[CRONUS Mexico S_A_$Cust_ Ledger Entry1000000021].[Open] As Status,
[CRONUS Mexico S_A_$Cust_ Ledger Entry1000000021].[Due Date] As DueDate
From [CRONUS Mexico S_A_$Cust_ Ledger Entry1000000021]
Where [CRONUS Mexico S_A_$Cust_ Ledger Entry1000000021].[Open] = 1

Further how much I understand is neccesery to create Left Join with the tabel CRONUS $Detailed Cust_ Ledg_ Entry and to add Open Amounts to all documents which which in column Open has Value 1.
Here starts my problems.
My Question , how can do this, which querry is neccesery to make in CRONUS $Detailed Cust_ Ledg_ Entry to achive to have all correct Open Amounts for ceartin documents (Sales Invoices and Credit Notes).

Is this good way to build this query or not ? How can summarize this Outstanding Amounts in CRONUS $Detailed Cust_ Ledg_ Entry tabel.

Hope that somebody can help.

Thanks in advance.

Karenh

INNER JOIN the Detailed Cust. Ledg. Entry table

On Detailed Cust. Ledg. Entry.Cust. Ledger Entry No. = Cust. Ledger Entry.Entry No.

Use the field Amount (LCY) from the Detailed Cust. Ledg. Entry table