Hello,
I have an odd case here where the raw data being returned from one of my queries does not match the data shown when I run the report. The report is duplicating some lines, but when I analyze the query there are no duplicates. I’m completely stumped…can anyone think of what would cause this?
Thank you for reading!
Here are the report results for a specific customer with the duplicate invoice:
Here is the data returned from the query for that same customer:
Depending on how your Joins are set up, what you see as the results of the query can be different than what is passed into SSRS.
One thing you can do is to save the report for a day (that is an option in Epicor when you go to run the report), and then go look in the Report Database at the tables that were created with for that report. If you only see the data you are expecting, then you know it is an issue somewhere in the RDL. If you are seeing more data than you were expecting, then it is most likely within the query.
Can you post the query that is shown in the BAQ Designer?
Thanks, Jason.
It’s not pretty due to the way Epicor handles split reps, but here it is…
select
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[InvcHead].[SalesRepList] as [InvcHead_SalesRepList],
([Ice].num_entries(InvcHead.SalesRepList, '~')) as [Calculated_CountSalesRep],
(1) as [Calculated_RepNum],
([Ice].entry(1, InvcHead.SalesRepList+'~~~~~', '~')) as [Calculated_SalesRep],
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
[InvcHead].[InvoiceAmt] as [InvcHead_InvoiceAmt],
[InvcHead].[InvoiceBal] as [InvcHead_InvoiceBal],
[InvcHead].[PONum] as [InvcHead_PONum],
[InvcDtl].[RepRate1] as [InvcDtl_RepRate1],
[InvcDtl].[RepSplit1] as [InvcDtl_RepSplit1],
[InvcDtl].[InvoiceLine] as [InvcDtl_InvoiceLine],
[InvcDtl].[OrderNum] as [InvcDtl_OrderNum],
[InvcDtl].[OrderLine] as [InvcDtl_OrderLine],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[CashDtl].[HeadNum] as [CashDtl_HeadNum],
[CashDtl].[TranDate] as [CashDtl_TranDate],
[CashDtl].[TranAmt] as [CashDtl_TranAmt],
[CashDtl].[TranType] as [CashDtl_TranType],
[CashDtl].[CheckRef] as [CashDtl_CheckRef],
[InvcDtl].[PartNum] as [InvcDtl_PartNum],
[InvcDtl].[OurOrderQty] as [InvcDtl_OurOrderQty],
[InvcDtl].[OurShipQty] as [InvcDtl_OurShipQty],
[InvcDtl].[UnitPrice] as [InvcDtl_UnitPrice],
((case when InvcDtl.PricePerCode = 'C' then 100.0 else
(case when InvcDtl.PricePerCode = 'M' then 1000.0 else
1.0 end) end)) as [Calculated_CostPer],
((case when InvcDtl.PricePerCode = 'C' then InvcDtl.UnitPrice / 100.0 else
(case when InvcDtl.PricePerCode = 'M' then InvcDtl.UnitPrice / 1000.0 else
InvcDtl.UnitPrice end) end)) as [Calculated_TrueUnitPrice],
(InvcDtl.UnitPrice / CostPer * InvcDtl.OurOrderQty) as [Calculated_InvoiceLineTotal],
[InvcDtl].[DiscountPercent] as [InvcDtl_DiscountPercent],
[InvcDtl].[Discount] as [InvcDtl_Discount],
(case when InvcHead.InvoiceAmt = 0.0 then 0.0
else CashDtl.TranAmt / InvcHead.InvoiceAmt
end) as [Calculated_LineCAPercent],
(((TrueUnitPrice * InvcDtl.OurShipQty) - InvcDtl.Discount) * LineCAPercent) as [Calculated_LineCA],
(LineCA * (InvcDtl.RepRate1 / 100.0) * (InvcDtl.RepSplit1 / 100.0)) as [Calculated_AmountDue]
from Erp.InvcHead as InvcHead
inner join Erp.InvcDtl as InvcDtl on
InvcHead.Company = InvcDtl.Company
and InvcHead.InvoiceNum = InvcDtl.InvoiceNum
and ( InvcDtl.ProdCode <> 'RETURNS' )
inner join Erp.Customer as Customer on
InvcHead.Company = Customer.Company
and InvcHead.CustNum = Customer.CustNum
inner join Erp.CashDtl as CashDtl on
InvcHead.Company = CashDtl.Company
and InvcHead.InvoiceNum = CashDtl.InvoiceNum
and ( CashDtl.TranType = 'PayInv' )
where (InvcHead.SalesRepList <> '')
union
select
[InvcHead2].[InvoiceNum] as [InvcHead2_InvoiceNum],
[InvcHead2].[SalesRepList] as [InvcHead2_SalesRepList],
([Ice].num_entries(InvcHead2.SalesRepList, '~')) as [Calculated_CountSalesRep],
(2) as [Calculated_RepNum],
([Ice].entry(2, InvcHead2.SalesRepList+'~~~~~', '~')) as [Calculated_SalesRep2],
[InvcHead2].[InvoiceDate] as [InvcHead2_InvoiceDate],
[InvcHead2].[InvoiceAmt] as [InvcHead2_InvoiceAmt],
[InvcHead2].[InvoiceBal] as [InvcHead2_InvoiceBal],
[InvcHead2].[PONum] as [InvcHead2_PONum],
[InvcDtl2].[RepRate2] as [InvcDtl2_RepRate2],
[InvcDtl2].[RepSplit2] as [InvcDtl2_RepSplit2],
[InvcDtl2].[InvoiceLine] as [InvcDtl2_InvoiceLine],
[InvcDtl2].[OrderNum] as [InvcDtl2_OrderNum],
[InvcDtl2].[OrderLine] as [InvcDtl2_OrderLine],
[Customer2].[CustID] as [Customer2_CustID],
[Customer2].[Name] as [Customer2_Name],
[CashDtl2].[HeadNum] as [CashDtl2_HeadNum],
[CashDtl2].[TranDate] as [CashDtl2_TranDate],
[CashDtl2].[TranAmt] as [CashDtl2_TranAmt],
[CashDtl2].[TranType] as [CashDtl2_TranType],
[CashDtl2].[CheckRef] as [CashDtl2_CheckRef],
[InvcDtl2].[PartNum] as [InvcDtl2_PartNum],
[InvcDtl2].[OurOrderQty] as [InvcDtl2_OurOrderQty],
[InvcDtl2].[OurShipQty] as [InvcDtl2_OurShipQty],
[InvcDtl2].[UnitPrice] as [InvcDtl2_UnitPrice],
((case when InvcDtl2.PricePerCode = 'C' then 100.0 else
(case when InvcDtl2.PricePerCode = 'M' then 1000.0 else
1.0 end) end)) as [Calculated_CostPer],
((case when InvcDtl2.PricePerCode = 'C' then InvcDtl2.UnitPrice / 100.0 else
(case when InvcDtl2.PricePerCode = 'M' then InvcDtl2.UnitPrice / 1000.0 else
InvcDtl2.UnitPrice end) end)) as [Calculated_TrueUnitPrice],
(InvcDtl2.UnitPrice / CostPer * InvcDtl2.OurOrderQty) as [Calculated_InvoiceLineTotal],
[InvcDtl2].[DiscountPercent] as [InvcDtl2_DiscountPercent],
[InvcDtl2].[Discount] as [InvcDtl2_Discount],
(case when InvcHead2.InvoiceAmt = 0.0 then 0.0
else CashDtl2.TranAmt / InvcHead2.InvoiceAmt
end) as [Calculated_LineCAPercent],
(((TrueUnitPrice * InvcDtl2.OurShipQty) - InvcDtl2.Discount) * LineCAPercent) as [Calculated_LineCA],
(LineCA * (InvcDtl2.RepRate2 / 100.0) * (InvcDtl2.RepSplit2 / 100.0)) as [Calculated_AmountDue]
from Erp.InvcHead as InvcHead2
inner join Erp.InvcDtl as InvcDtl2 on
InvcHead2.Company = InvcDtl2.Company
and InvcHead2.InvoiceNum = InvcDtl2.InvoiceNum
and ( InvcDtl2.RepSplit2 > 0 and InvcDtl2.ProdCode <> 'RETURNS' )
inner join Erp.Customer as Customer2 on
InvcHead2.Company = Customer2.Company
and InvcHead2.CustNum = Customer2.CustNum
inner join Erp.CashDtl as CashDtl2 on
InvcHead2.Company = CashDtl2.Company
and InvcHead2.InvoiceNum = CashDtl2.InvoiceNum
where (InvcHead2.SalesRepList <> '' and InvcHead2.SalesRepList like '%~%')
union
select
[InvcHead3].[InvoiceNum] as [InvcHead3_InvoiceNum],
[InvcHead3].[SalesRepList] as [InvcHead3_SalesRepList],
([Ice].num_entries(InvcHead3.SalesRepList, '~')) as [Calculated_CountSalesRep],
(3) as [Calculated_RepNum],
([Ice].entry(3, InvcHead3.SalesRepList+'~~~~~', '~')) as [Calculated_SalesRep],
[InvcHead3].[InvoiceDate] as [InvcHead3_InvoiceDate],
[InvcHead3].[InvoiceAmt] as [InvcHead3_InvoiceAmt],
[InvcHead3].[InvoiceBal] as [InvcHead3_InvoiceBal],
[InvcHead3].[PONum] as [InvcHead3_PONum],
[InvcDtl3].[RepRate3] as [InvcDtl3_RepRate3],
[InvcDtl3].[RepSplit3] as [InvcDtl3_RepSplit3],
[InvcDtl3].[InvoiceLine] as [InvcDtl3_InvoiceLine],
[InvcDtl3].[OrderNum] as [InvcDtl3_OrderNum],
[InvcDtl3].[OrderLine] as [InvcDtl3_OrderLine],
[Customer3].[CustID] as [Customer3_CustID],
[Customer3].[Name] as [Customer3_Name],
[CashDtl3].[HeadNum] as [CashDtl3_HeadNum],
[CashDtl3].[TranDate] as [CashDtl3_TranDate],
[CashDtl3].[TranAmt] as [CashDtl3_TranAmt],
[CashDtl3].[TranType] as [CashDtl3_TranType],
[CashDtl3].[CheckRef] as [CashDtl3_CheckRef],
[InvcDtl3].[PartNum] as [InvcDtl3_PartNum],
[InvcDtl3].[OurOrderQty] as [InvcDtl3_OurOrderQty],
[InvcDtl3].[OurShipQty] as [InvcDtl3_OurShipQty],
[InvcDtl3].[UnitPrice] as [InvcDtl3_UnitPrice],
((case when InvcDtl3.PricePerCode = 'C' then 100.0 else
(case when InvcDtl3.PricePerCode = 'M' then 1000.0 else
1.0 end) end)) as [Calculated_CostPer],
((case when InvcDtl3.PricePerCode = 'C' then InvcDtl3.UnitPrice / 100.0 else
(case when InvcDtl3.PricePerCode = 'M' then InvcDtl3.UnitPrice / 1000.0 else
InvcDtl3.UnitPrice end) end)) as [Calculated_TrueUnitPrice],
(InvcDtl3.UnitPrice / CostPer * InvcDtl3.OurOrderQty) as [Calculated_InvoiceLineTotal],
[InvcDtl3].[DiscountPercent] as [InvcDtl3_DiscountPercent],
[InvcDtl3].[Discount] as [InvcDtl3_Discount],
(case when InvcHead3.InvoiceAmt = 0.0 then 0.0
else CashDtl3.TranAmt / InvcHead3.InvoiceAmt
end) as [Calculated_LineCAPercent],
(((TrueUnitPrice * InvcDtl3.OurShipQty) - InvcDtl3.Discount) * LineCAPercent) as [Calculated_LineCA],
(LineCA * (InvcDtl3.RepRate3 / 100.0) * (InvcDtl3.RepSplit3 / 100.0)) as [Calculated_AmountDue]
from Erp.InvcHead as InvcHead3
inner join Erp.InvcDtl as InvcDtl3 on
InvcHead3.Company = InvcDtl3.Company
and InvcHead3.InvoiceNum = InvcDtl3.InvoiceNum
and ( InvcDtl3.RepSplit3 > 0 and InvcDtl3.ProdCode <> 'RETURNS' )
inner join Erp.Customer as Customer3 on
InvcHead3.Company = Customer3.Company
and InvcHead3.CustNum = Customer3.CustNum
inner join Erp.CashDtl as CashDtl3 on
InvcHead3.Company = CashDtl3.Company
and InvcHead3.InvoiceNum = CashDtl3.InvoiceNum
where (InvcHead3.SalesRepList <> '' and InvcHead3.SalesRepList like '%~%')
union
select
[InvcHead4].[InvoiceNum] as [InvcHead4_InvoiceNum],
[InvcHead4].[SalesRepList] as [InvcHead4_SalesRepList],
([Ice].num_entries(InvcHead4.SalesRepList, '~')) as [Calculated_CountSalesRep],
(4) as [Calculated_RepNum],
([Ice].entry(4, InvcHead4.SalesRepList, '~')) as [Calculated_SalesRep],
[InvcHead4].[InvoiceDate] as [InvcHead4_InvoiceDate],
[InvcHead4].[InvoiceAmt] as [InvcHead4_InvoiceAmt],
[InvcHead4].[InvoiceBal] as [InvcHead4_InvoiceBal],
[InvcHead4].[PONum] as [InvcHead4_PONum],
[InvcDtl4].[RepRate4] as [InvcDtl4_RepRate4],
[InvcDtl4].[RepSplit4] as [InvcDtl4_RepSplit4],
[InvcDtl4].[InvoiceLine] as [InvcDtl4_InvoiceLine],
[InvcDtl4].[OrderNum] as [InvcDtl4_OrderNum],
[InvcDtl4].[OrderLine] as [InvcDtl4_OrderLine],
[Customer4].[CustID] as [Customer4_CustID],
[Customer4].[Name] as [Customer4_Name],
[CashDtl4].[HeadNum] as [CashDtl4_HeadNum],
[CashDtl4].[TranDate] as [CashDtl4_TranDate],
[CashDtl4].[TranAmt] as [CashDtl4_TranAmt],
[CashDtl4].[TranType] as [CashDtl4_TranType],
[CashDtl4].[CheckRef] as [CashDtl4_CheckRef],
[InvcDtl4].[PartNum] as [InvcDtl4_PartNum],
[InvcDtl4].[OurOrderQty] as [InvcDtl4_OurOrderQty],
[InvcDtl4].[OurShipQty] as [InvcDtl4_OurShipQty],
[InvcDtl4].[UnitPrice] as [InvcDtl4_UnitPrice],
((case when InvcDtl4.PricePerCode = 'C' then 100.0 else
(case when InvcDtl4.PricePerCode = 'M' then 1000.0 else
1.0 end) end)) as [Calculated_CostPer],
((case when InvcDtl4.PricePerCode = 'C' then InvcDtl4.UnitPrice / 100.0 else
(case when InvcDtl4.PricePerCode = 'M' then InvcDtl4.UnitPrice / 1000.0 else
InvcDtl4.UnitPrice end) end)) as [Calculated_TrueUnitPrice],
(InvcDtl4.UnitPrice / CostPer * InvcDtl4.OurOrderQty) as [Calculated_InvoiceLineTotal],
[InvcDtl4].[DiscountPercent] as [InvcDtl4_DiscountPercent],
[InvcDtl4].[Discount] as [InvcDtl4_Discount],
(case when InvcHead4.InvoiceAmt = 0.0 then 0.0
else CashDtl4.TranAmt / InvcHead4.InvoiceAmt
end) as [Calculated_LineCAPercent],
(((TrueUnitPrice * InvcDtl4.OurShipQty) - InvcDtl4.Discount) * LineCAPercent) as [Calculated_LineCA],
(LineCA * (InvcDtl4.RepRate4 / 100.0) * (InvcDtl4.RepSplit4 / 100.0)) as [Calculated_AmountDue]
from Erp.InvcHead as InvcHead4
inner join Erp.InvcDtl as InvcDtl4 on
InvcHead4.Company = InvcDtl4.Company
and InvcHead4.InvoiceNum = InvcDtl4.InvoiceNum
and ( InvcDtl4.RepSplit4 > 0 and InvcDtl4.ProdCode <> 'RETURNS' )
inner join Erp.Customer as Customer4 on
InvcHead4.Company = Customer4.Company
and InvcHead4.CustNum = Customer4.CustNum
inner join Erp.CashDtl as CashDtl4 on
InvcHead4.Company = CashDtl4.Company
and InvcHead4.InvoiceNum = CashDtl4.InvoiceNum
where (InvcHead4.SalesRepList <> '' and InvcHead4.SalesRepList like '%~%')
union
select
[InvcHead5].[InvoiceNum] as [InvcHead5_InvoiceNum],
[InvcHead5].[SalesRepList] as [InvcHead5_SalesRepList],
([Ice].num_entries(InvcHead5.SalesRepList, '~')) as [Calculated_CountSalesRep],
(5) as [Calculated_RepNum],
([Ice].entry(5, InvcHead5.SalesRepList, '~')) as [Calculated_SalesRep],
[InvcHead5].[InvoiceDate] as [InvcHead5_InvoiceDate],
[InvcHead5].[InvoiceAmt] as [InvcHead5_InvoiceAmt],
[InvcHead5].[InvoiceBal] as [InvcHead5_InvoiceBal],
[InvcHead5].[PONum] as [InvcHead5_PONum],
[InvcDtl5].[RepRate5] as [InvcDtl5_RepRate5],
[InvcDtl5].[RepSplit5] as [InvcDtl5_RepSplit5],
[InvcDtl5].[InvoiceLine] as [InvcDtl5_InvoiceLine],
[InvcDtl5].[OrderNum] as [InvcDtl5_OrderNum],
[InvcDtl5].[OrderLine] as [InvcDtl5_OrderLine],
[Customer5].[CustID] as [Customer5_CustID],
[Customer5].[Name] as [Customer5_Name],
[CashDtl5].[HeadNum] as [CashDtl5_HeadNum],
[CashDtl5].[TranDate] as [CashDtl5_TranDate],
[CashDtl5].[TranAmt] as [CashDtl5_TranAmt],
[CashDtl5].[TranType] as [CashDtl5_TranType],
[CashDtl5].[CheckRef] as [CashDtl5_CheckRef],
[InvcDtl5].[PartNum] as [InvcDtl5_PartNum],
[InvcDtl5].[OurOrderQty] as [InvcDtl5_OurOrderQty],
[InvcDtl5].[OurShipQty] as [InvcDtl5_OurShipQty],
[InvcDtl5].[UnitPrice] as [InvcDtl5_UnitPrice],
((case when InvcDtl5.PricePerCode = 'C' then 100.0 else
(case when InvcDtl5.PricePerCode = 'M' then 1000.0 else
1.0 end) end)) as [Calculated_CostPer],
((case when InvcDtl5.PricePerCode = 'C' then InvcDtl5.UnitPrice / 100.0 else
(case when InvcDtl5.PricePerCode = 'M' then InvcDtl5.UnitPrice / 1000.0 else
InvcDtl5.UnitPrice end) end)) as [Calculated_TrueUnitPrice],
(InvcDtl5.UnitPrice / CostPer * InvcDtl5.OurOrderQty) as [Calculated_InvoiceLineTotal],
[InvcDtl5].[DiscountPercent] as [InvcDtl5_DiscountPercent],
[InvcDtl5].[Discount] as [InvcDtl5_Discount],
(case when InvcHead5.InvoiceAmt = 0.0 then 0.0
else CashDtl5.TranAmt / InvcHead5.InvoiceAmt
end) as [Calculated_LineCAPercent],
(((TrueUnitPrice * InvcDtl5.OurShipQty) - InvcDtl5.Discount) * LineCAPercent) as [Calculated_LineCA],
(LineCA * (InvcDtl5.RepRate5 / 100.0) * (InvcDtl5.RepSplit5 / 100.0)) as [Calculated_AmountDue]
from Erp.InvcHead as InvcHead5
inner join Erp.InvcDtl as InvcDtl5 on
InvcHead5.Company = InvcDtl5.Company
and InvcHead5.InvoiceNum = InvcDtl5.InvoiceNum
and ( InvcDtl5.RepSplit5 > 0 and InvcDtl5.ProdCode <> 'RETURNS' )
inner join Erp.Customer as Customer5 on
InvcHead5.Company = Customer5.Company
and InvcHead5.CustNum = Customer5.CustNum
inner join Erp.CashDtl as CashDtl5 on
InvcHead5.Company = CashDtl5.Company
and InvcHead5.InvoiceNum = CashDtl5.InvoiceNum
where (InvcHead5.SalesRepList <> '' and InvcHead5.SalesRepList like '%~%')
Hey Jason, don’t waste any more time than you already have on this one…I found it much easier to modify the existing report than to try and troubleshoot this beast of a query. That should have been my approach from the beginning.
Thank you for taking a look though, it’s greatly appreciated!
Ryan - My apologies for not responding to your post with the query earlier today. My phone started ringing off the hook and my day got real busy, real quick.
I am glad to hear you were able to go about it in a different way. I have had my fair share of ‘duplicate rows’ pop up over the years and they can be an absolute pain to try to figure out the cause of it.
No apology necessary, I basically dumped the BAQ equivalent of War and Peace on you! That query was created with the help of a consultant and is way outside of my SQL abilities anyways, so the chances of me figuring out how to prevent the duplicates was slim to none…the smart route was definitely manipulating their report.
Thanks again!