Collection of popular ms dynamics (navision6) queries for ms SQL server ?


I m new with NAV, for my work I have to setup sql queries to retrieve data from navision…

I need queries for a lot of areas: aging reports, inventory stock, sales, etc…

I have found this page:

this is a collection of popular queries but for MS dynamics GP ( not nav :frowning: ) so my question is do you know if there is an equivalent for ms dynamic nav ???



Hi Stefan,

there is actually a report pack on the partnersource that you can download.


Actually i might have been wrong but its on google :slight_smile:

Of course its a bit outdated but gives you a start.


Thank you,

this “report pack” is very usefull.

but I still need some queries for my project:

query1: accounts balance, with this I must build the “company anual report”, so I need all the bank accounts with the balance by date

query2: items in stock (warehouse): by items/date

query3: daily sales report

query4: aging reports (I was able to do an aging report for the company customers, now I need to do the same but for the company providers)

well if someone can help I will appreciate a lot.

Thanks to all



If we do everything, what do you do? will get paid freely?

the above examples given to learn from them and try what you need…

Hi Stefan ,

You probably need to work it out based on the existing reports … via object designer and code view.

What you mentioned , reports are there in NAV …

Some help for your queries (tables)

  1. G/L Accounts , G/L Entry

  2. Item ledger entry (value entry)

  3. Sales invoice and Sales CR. Memo

  4. Cust. Ledger entry , Vendor Ledger entry


well, I m not looking for problems, don’t want to ofence you,

my problem is that I have no budget to pay a “nav consultor”, I already was able to do some other queries, but my main problem is that I don’t have lots of time to investigate and to do my “missing queries” as my deadline is ending on next Monday…

On my side if I can help someone freely by just posting some sql queries that I already have I will do it free of charge, I m not interested on getting money, as I already have a Job…

For me it makes me happy if I can share some knowlegde to others, the problem is that as I m new in navision I don’t have the knownledge.



Hi Faludigabor,

thank you for your help.

I don’t know how the object designer and the code view work… as you can imagine i m pretty new with navision…

Let me explain my job:

  • I have to do a lot of reports to automate data visualisation in excel, my manager wants a lot of reports working with some ERP’s:




I already have done lots with A3 ERP and some others with SAGE, now I m working to adapt theses reports to Navision…

So I have a big MS SQL Server 2008r2 loaded with lots of databases (from A3 ERP, SAGE, Navision…) and I need to build the queries needed by the excel reports for each of theses ERP’s

That’s why I m on this forum asking help.

As I said I not looking for trouble and please excuse me if some of the readers are offensed… I know most people only wants to help with money, so just need to found an Angel :wink:



Your first statement “I m new with NAV” tells me you have no business being anywhere near a task of this magnitude.

you 're right, it’s a big challenge for me.

There is a long road you need to walk down. People here are busy with other work and expect you to do your homework. So do I :).

IF your boss expects you to deliver in 2 days w/o help , experience or budget that is a different topic :-).

Normally the Data structure is business and technial knowledge not sth that can be explained through a forum… you need some guidance for that.


Correct and you need to step up to the challenge. Don’t expect someone to do it for you. You need to “get your hands dirty”. If you have specific questions about specific parts, then ask them. I’m sure you will find lots of people willing to offer assistance and advice. But do not expect anyone to do your job for you.

What is the purpose of this whole exercise? The fact that you’re during this for multiple systems leads me to believe you are not an end-user.

all of you are right, it s true that I have to put my hands dirty and learn more deeper the navision data structure to be able to do my queries… but as I said I have a very short time… but I will do it and also ask a delay to my boss.

babrown, it s true that I m not an end user…

The goal is:

my company sels a product who acts like an interface between excel and databases, it’s business intelligence tool but 100% based on excel reports…

The product is able to connect to all databases using obdc and workds like this:

  1. the product has the databases connections defined in it ( xx erp’s)

  2. the user who has the product is able to connect to a web site and download BI reports (sales activity, inventory, company balance, etc…). Each excel report has a meta tag that indicates what kind of query he needs and for which ERP’s, each report has also some macros to do manipulations (build pivot table, apply layout, etc)

  3. from the product, the user import the downloaded excel template, the product goes then to our “universal query repository” and downloads the needed queries, then the product retrieves the data, copy the data to the excel template, executes the macros, and save the template as a final excel report.

  4. the product can also send the reports by email, post them to a ftp, and can also schedule the production of reports…

that’s all