Dynamics Community Forum Thread Details

Dear collegs,

I face with 3 NAV SQL report issues for which am not sura that know good solution. Hope that somebody can help me with that.

My Questions are :

1. Open (outstanding) amount report for ceartin date (report which will show Open Entries which was at ceartin date). Namely, is neccesery to make one report which will show what was status with Open Entries at 1/4/2010. For instance if for an invoice with posting date 28/02/2010 with due date 28/04/2010, and has payment received in Navision on 18/04/2010 then that invoice in report should be showed as open Entry. Acctually this report should be some kind of simulation of 01/04/2010. In Navision now exists report with the name Customer Detailed Aging report at which is possible to chosee Open amounts (in options tab) but that report will show status of this momment (and for me is neccesery the same report with the status of 1/4/2010). To achieve this I did next : make one query

Select [Navision$Cust_ Ledger Entry].[Customer No_] As
        CustNo, [Navision$Cust_ Ledger Entry].[Document No_] As
        DocNo, [Navision$Cust_ Ledger Entry].[Due Date] As DueDate,
        DATEDIFF(day, '3/31/2010 12:00 AM',
        [Navision$Cust_ Ledger Entry].[Due Date]) As DaysLate,
        [Navision$Cust_ Ledger Entry].[Entry No_] As Entry,
        [Navision$Cust_ Ledger Entry].[Posting Date] As PostingDate
      From [Navision$Cust_ Ledger Entry]
      Where [Navision$Cust_ Ledger Entry].[Posting Date] <= '4/1/2010'

After that I did one LEFT INNER JOIN with the tabel DETAILED CUSTOMER LEDGER where match column was Cust_ Ledger Entry No_ (from Detailed customer Ledger) = Entry No (from Customer Ledger entry)
Something like this :

Select [Navision$Detailed Cust_ Ledg_ Entry].[Amount (LCY)] As Amount,
[Navision$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_] As Entry,
[Navision$Detailed Cust_ Ledg_ Entry].[Posting Date] As PostingDate
From [Navision$Detailed Cust_ Ledg_ Entry]
Where [Navision$Detailed Cust_ Ledg_ Entry].[Posting Date] <= '4/1/2010'

Here I made one filter which say pickup only entries created until 4/1/2010
I would like to ask you, is this good query for this what I want, will be with this query possible to simulate status of 1/4/2010 or not. Also maybe would like to add, is acctually possible to make this kind of reports in NAV.

2. Need to have one report in which Amount of Sales Orders will be presented in Local currency , for that I made this from Sales Line table I multiple Line Amount with the Currency factor.
[Navision$Sales Line].[Line Amount] * [Navision$Sales Header].[Currency Factor] .
Is this good way to present Sales Order in local currency or shoud to use data from some other tabel.

3. Also need to make one VAT report and query for that. I achive that on the way that say VAT amount is Amount including VAT minus Amount. Is this good way to do this or should to use some other data from other tabels.

Hope that can get answers on questions.

Thanks in advance.

Regards