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?