Baq data is not accurate

Epicor ERP 10
1

I’m new to making BAQs, I can get part of it to work but not all, and just when I think I have it all, I check in depth and its wrong.

The BAQ is meant to show data for a specific customer from 01/01/2021 to present day of;

Part, Part Description(Customers Part name),Total units sold, Price per unit, Total cost per unit, Lbr Cost, Mtl Cost, Bur Cost, Sub Cost, And Profitability%

I’ve managed to get the part, description, units sold to come up accurately

Price per unit, I settled on a Calculated field for Max(unit price)

But the costs do not work, or populate, or they’re incorrect in comparison to what is shown in part advisor.

For the phrase build, I’ve used; InvcDtl, InvHead, Customer and Part
I’ve set table criteria for Customer Name Constant
Also table criteria for Invoicedate > 1/1/2021
I also had them grouped by part and part desc

Am I not doing this right?

I’m extremely new to this and still trying to learn as nobody here seems to know about creating BAQs so I’ve been going in blindly basically learning through trial and error

2

Can you post screencaps of your tables in the Designer, and the script for your calculated field?

3

Capture2
Capture21047×729 41.1 KB

image
image699×556 27.9 KB

4

This is when I thought I got the Total unit cost working right, but was mostly wrong in comparison to part advisor

Capture3
Capture3990×551 29.4 KB

5

Bumping this post in hopes someone can help

6

Part Advisor uses PartCost table that in fact uses your costing method (average,STD etc) According to your current BAQ you are using InvDtl, so you are comparing different numbers. InvDtl will outline just that specific event (invoice) and not the Unit Cost for the part under your inventory value.

1 Like
7

Thank you for the clarification, I should be able to make some headway now.