This is a strange thing I have never come across before. In a BAQ, I am looking at the Order and Job tables, and summing the open value of sales order releases, rolled up by job number. Only one value in this data is throwing me off. Take a look at this screen recording. You can see I am stepping through the BAQ grid looking at each value. When I click on the value over 11million, the 11million disappears and I am left with just the 522k that I expect. I have no idea where the extra 11 million came from. It is not in the underlying data at all. As you can see, the value changes depending on whether I am in the field or not. I don’t even know where to start with this!
The BAQ looks like this:
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
select
[JobProd].[JobNum] as [JobProd_JobNum],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[Customer].[CustID] as [Customer_CustID],
(sum(OrderRel.OurReqQty- OrderRel.OurJobShippedQty- OrderRel.OurStockQty)) as [Calculated_TotalOpenQty],
(sum(SubQuery3.Calculated_OpenValue)) as [Calculated_TotalOpenValue]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
and ( OrderDtl.OpenLine = true )
inner join Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
and OrderDtl.OrderNum = OrderRel.OrderNum
and OrderDtl.OrderLine = OrderRel.OrderLine
and ( OrderRel.OpenRelease = true )
inner join Erp.JobProd as JobProd on
OrderRel.Company = JobProd.Company
and OrderRel.OrderNum = JobProd.OrderNum
and OrderRel.OrderLine = JobProd.OrderLine
and OrderRel.OrderRelNum = JobProd.OrderRelNum
inner join Erp.JobHead as JobHead on
JobProd.Company = JobHead.Company
and JobProd.JobNum = JobHead.JobNum
inner join (select
[OrderDtl1].[Company] as [OrderDtl1_Company],
[OrderDtl1].[OrderNum] as [OrderDtl1_OrderNum],
[OrderDtl1].[OrderLine] as [OrderDtl1_OrderLine],
[OrderDtl1].[UnitPrice] as [OrderDtl1_UnitPrice],
(sum(OrderRel1.OurReqQty- OrderRel1.OurJobShippedQty -OrderRel1.OurStockShippedQty) * OrderDtl1.UnitPrice) as [Calculated_OpenValue]
from Erp.OrderDtl as OrderDtl1
inner join Erp.OrderRel as OrderRel1 on
OrderDtl1.Company = OrderRel1.Company
and OrderDtl1.OrderNum = OrderRel1.OrderNum
and OrderDtl1.OrderLine = OrderRel1.OrderLine
and ( OrderRel1.OpenRelease = true )
where (OrderDtl1.OpenLine = true)
and (OrderRel1.ReqDate >= @StartDate and OrderRel1.ReqDate <= @EndDate)
group by [OrderDtl1].[Company],
[OrderDtl1].[OrderNum],
[OrderDtl1].[OrderLine],
[OrderDtl1].[UnitPrice]) as SubQuery3 on
OrderDtl.Company = SubQuery3.OrderDtl1_Company
and OrderDtl.OrderNum = SubQuery3.OrderDtl1_OrderNum
and OrderDtl.OrderLine = SubQuery3.OrderDtl1_OrderLine
inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
where (OrderHed.OpenOrder = true and not OrderHed.CustNum in (16, 263, 288, 317, 406))
and (OrderRel.ReqDate >= @StartDate and OrderRel.ReqDate <= @EndDate)
group by [JobProd].[JobNum],
[OrderDtl].[PartNum],
[Customer].[CustID]
having (sum(OrderRel.OurReqQty- OrderRel.OurJobShippedQty- OrderRel.OurStockQty)) > 0
order by OrderDtl.PartNum
Notably, I am filtering the releases for req dates in a given range. I am also excluding a handful of specific customers.
Does anything here stand out to you? Why is the BAQ field showing one value but holding another? What’s going on with that?
