I am trying to a build a table from the value entry table that lists the document number, the sales amount and the COGS. I would like to the table to only show one instance of the document no. (since the value entry table posts by item there are typically multiple items on a document and therefor the document no. is posted multiple times). From there, I would like the total sales and total COGS for each document number. I can build the table but only were the document no. shows up multiple times and this becomes an issue to due the number of transactions (ie, excel craps out). For one month, the total # of lines based on a single listing of each document no. is roughly 20k however it is 190k when the document no. is listed multiple times.
Thanks in advance.
Date
Votes
1 comment
-
Jet Reports Historic Posts The easiest way to do this would be using a Grouping-style report.
However, it sounds like you are using the Table Builder. If that is the format you need, what you would want to do is to use two NAV tables. The first is one where each Document No. only appears once and the second table would be your Value Entry table.
As an example, here I have use the Sales Invoice Header table to show the No. field (which I duplicated with the Document No. field from the Value Entry).
I've also set it so that I will only see No. for which there is a corresponding record in the Value Entry table.
When I run my report, each document only appears once.
I hope that helps get you pointed in the right direction.
Please sign in to leave a comment.