It's one thing to know how to use Jet, then it's another when figuring out what data to use.
I have seen some sample reports to get sales figures from using the Customer table and the Sales ($) or Sales (LCY) field (although these fields are not in my database but that's another question).
I have seen other sample reports use the Item Ledger Entry Table.
The Customer table/Sales seems to easily line up with data that I see in NAV for the customer in the customer statistics section of the customer record.
So I guess my question is…is there any documentation on what tables to use for certain types of reports. Some cheat sheet or something?
Date
Votes
1 comment
-
Jet Reports Historic Posts Hello vrinner,
This is an excellent question that has been asked many times over the years. I haven't found a cheat sheet like you are looking for - mostly because each report is unique and depending on how you do business, the table could be different. For instance, some companies ask for a sales report….
Do they means sales that have posted or are also still on order?
Do you need to have item level details?
Are sales calculated based on when the invoice posts, when it ships, or when it is paid?
Are you looking for gross sales or net?
What does net sales mean?
Does that take out freight, commissions, etc.?
The answers to these questions can lead you through using different tables (Cust. Ledger Entry, Sales Header, Sales Line, Sales Invoice Header, Sales Invoice Line, Item Ledger Entry, Value Entry, G/L Entry, etc.) Picking the right table is more of an art than a science. Answering the business questions above helps guide you to the right table(s). The good news is that NAV has a very predicable table structure. For every "master" table (Customer, Vendor, Item, G/L Account) there is always an associated ledger table that contains the transactions for that entity (Cust. Ledger Entry, Vendor Ledger Entry, Item Ledger Entry, G/L Entry - for the master tables listed before).
The good news is that there are many resources out there for you. I have attached a Jet Report that lists all the tables and fields from the NAV database. You can open this and run it against your system to see what is available. This can be found included in the Jet Report Player (available at: http://www2.jetreports.com/l/3692/2012-02-16/qt2q8) if you haven't already downloaded it.
I have also attached a section of the Jet Reports: Report Writing Workshop. This is a training used mostly in the US to teach users how to build reports using Jet Essentials. In the NAV version of the training, there is a short section in the Appendix that describes the tables the training uses. For that reason it isn't complete but it does explain some of the main tables I use for building reports.
I would also recommend talking to your NAV folks. When you install NAV, you can also install an SDK (depending on your version of NAV). There are some useful pieces of information that can be found in that documentation - if you like to find things on your own. Or, you can always see if they can spend some time with you to educate you on the tables YOU should focus on because they understand the database and how you do business.
I hope all of this helps! Good luck and you can always ask questions here to see if we can help with a particular report.
SLK
Please sign in to leave a comment.