Every few months this report becomes an active discussion in the VISUAL forums. It usually starts off with a user requesting some guidance as they attempt to recreate the VISUAL gross profit report found under the Post Manufacturing Journals.
They soon come to realize that this is no easy feat as it involves numerous tables and complex queries.
Why are users wanting to recreate this report when they could just use the one in VISUAL? Well I’m sure they have good reasons….
- better access and distribution of report;
- format/layout of report;
- additional fields (data) to include;
- facilitate export of data to excel;
- they have nothing else better to do! 🙂
In any case, they’ve taken on the task.
So what makes this report so challenging? It’s largely because it ties to the financials for the period. For instance, you cannot just take the costs from the INVENORY_TRANS table as it only contains the current costs (regardless if they are posted or not). You actually need to pull the costs from the distribution tables which may contain multiple entries for the one shipment (that account for cost changes through the periods).
So if you are wanting to recreate this report, you’ll need to look at the following tables: