Skip to main content

Notifications

Microsoft Dynamics NAV (Archived)

3 NAV SQL report advices

Posted on by 55

 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

*This post is locked for comments

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,113 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,918 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans