SQL Query for sales invoices incl. Net, VAT and Gross

Business Central/NAV Technical Forum
1

Our accounts dept. like to use Excel to analyse data in NAV. They do this via SQL views which are then linked into Excel and can be refreshed in Excel.

I have been asked to produce a view (basically a SQL query) for each sales invoice including customer name, document number etc but also the Net, VAT and Gross figures (a column for each).

I am trying to work this using the Customer Ledger Entry table, but I’m not sure where to get each of the figures from (ie which tables). I’m currently using Sales (LCY) as the Net figure, then getting the VAT from VAT Entry table and the Gross from Detailed Cust. Ledg. Entry filtered on Entry Type being one of the following:
1, 3, 4, 5, 6, 7, 8, 9, 12, 13, 14, 15, 16, 17

I got this filter from the FlowField expression for the Amount (LCY) field in the Customer Ledger Entry table.

Please can someone clarify that this is the correct way to get these figures, or is there an altogether more simple way of doing this?

2

This reduces to question already discussed here extensively - how to mimicrize FlowFields in SQL queries… If you do some search, you’ll find a lot of threads about it, but in short:

Use Customer Ledger entry table as source, and include subselects instead of FlowFields. As you already did, you can use as a tip for creating subselect the FlowField definition form Navision - it’s almost ready SELECT statement, you only need to “translate” it to T-SQL.

P.S. Hope you know, that for such reporting you MUST use Ledger tables - NEVER posted documents tables…