SSRS Duplicate Records

Yahoo Archive
1
Hi Jose,

Great post - it was definitely helpful and informative.
Now that I realize the underlying issue with the cross joins, I'll be more aware of this issue in the future.
I was actually able to get my report to work using Grouping, but I'll play around some more with the Select Distinct.

Larry
2

I'm trying to modify the Bill of Lading form in Epicor 10 using the SSRS report builder. I modified the report data definitions, added the report style, edited the query and added the fields. The problem is that I am getting duplicate records; for example, a Bill of Lading that should have 3 lines has 18 (there are 6 identical records for each line.) I'm new to writing SSRS reports and I assume the issue is with the query but can't seem to pinpoint what it is.


="SELECT CAST(T1.BOLNum AS NVARCHAR) AS BOLNum

,T1.Carrier

,T1.CODAmount

,T1.Company

,T1.CommentText

,T1.FreightCharges

,T1.ShipDate

,T1.Plant

,T1.Calc_CustomerAddrList

,T1.Calc_PlantAddrList

,T1.Calc_ShipToAddrList

,T1.ProNumber    , T1.Plant_Name

,T1.ToPlant_Name

,CAST(T2.BOLLine AS NVARCHAR) AS BOLLine

,CAST(T2.BOLNum AS NVARCHAR) AS BOLDetail_BOLNum

,T2.Company AS BOLDetail_Company

,CAST(T2.Packages AS NVARCHAR) AS Packages

,T2.Rate

,T2.Weight

,T2.WeightUOM

,T2.Calc_ClassDesc

,T2.Calc_PkgDesc

,T2.Calc_WeightDflt

,T2.Calc_WeightUomDflt

,T3.Company AS ShipHead_Company 

,CAST(T3.BOLNum AS NVARCHAR) AS ShipHead_BOLNum

,CAST(T3.BOLLine AS NVARCHAR) AS ShipHead_BOLLine

,CAST(T4.ShipComment AS NVARCHAR) AS ShipComment , T3.PackNum AS ShipHead_PackNum ,T4.Company AS ShipDtl_Company ,T4.PackNum AS ShipDtl_PackNum ,T4.OrderNum ,T4.OurInventoryShipQty ,T4.OurJobShipQty ,T4.Packages as ShipDtl_Packages FROM BOLHead_" + Parameters!TableGuid.Value + " T1

 LEFT OUTER JOIN BOLDetail_" + Parameters!TableGuid.Value + " T2

  ON T1.Company = T2.Company AND T1.BOLNum = T2.BOLNum 

LEFT OUTER JOIN ShipHead_" + Parameters!TableGuid.Value + " T3 

  ON T2.Company = T3.Company AND T2.BOLNum = T3.BOLNum AND T2.BOLLine = T3.BOLLine LEFT OUTER JOIN ShipDtl_" + Parameters!TableGuid.Value + " T4  ON T3.Company = T4.Company AND T3.PackNum = T4.PackNum"


Thanks,

Larry


3
Hi Larry,
Its because of some of the linking on your tables. Look at the this topic I sent a few months back and it may help



Jose C Gomez
Software Engineer


T: 904.469.1524 mobile

Quis custodiet ipsos custodes?

On Mon, Nov 9, 2015 at 4:07 PM, larry.jedik@... [vantage] <vantage@yahoogroups.com> wrote:

 
<div>
  
  
  <p></p><p><span>I&#39;m trying to modify the Bill of Lading form in Epicor 10 using the SSRS report builder. I modified the report data definitions, added the report style, edited the query and added the fields. The problem is that I am getting duplicate records; for example, a Bill of Lading that should have 3 lines has 18 (there are 6 identical records for each line.) I&#39;m new to writing SSRS reports and I assume the issue is with the query but can&#39;t seem to pinpoint what it is.</span></p><p><span><br></span></p><p>=&quot;SELECT CAST(T1.BOLNum AS NVARCHAR) AS BOLNum</p><p><span style="white-space:pre-wrap;">	</span>,T1.Carrier</p><p><span style="white-space:pre-wrap;">	</span>,T1.CODAmount</p><p><span style="white-space:pre-wrap;">	</span>,T1.Company</p><p><span style="white-space:pre-wrap;">	</span>,T1.CommentText</p><p><span style="white-space:pre-wrap;">	</span>,T1.FreightCharges</p><p><span style="white-space:pre-wrap;">	</span>,T1.ShipDate</p><p><span style="white-space:pre-wrap;">	</span>,T1.Plant</p><p><span style="white-space:pre-wrap;">	</span>,T1.Calc_CustomerAddrList</p><p><span style="white-space:pre-wrap;">	</span>,T1.Calc_PlantAddrList</p><p><span style="white-space:pre-wrap;">	</span>,T1.Calc_ShipToAddrList</p><p><span style="white-space:pre-wrap;">	</span>,T1.ProNumber    , T1.Plant_Name</p><p><span style="white-space:pre-wrap;">	</span>,T1.ToPlant_Name</p><p><span style="white-space:pre-wrap;">	</span>,CAST(T2.BOLLine AS NVARCHAR) AS BOLLine</p><p><span style="white-space:pre-wrap;">	</span>,CAST(T2.BOLNum AS NVARCHAR) AS BOLDetail_BOLNum</p><p><span style="white-space:pre-wrap;">	</span>,T2.Company AS BOLDetail_Company</p><p><span style="white-space:pre-wrap;">	</span>,CAST(T2.Packages AS NVARCHAR) AS Packages</p><p><span style="white-space:pre-wrap;">	</span>,T2.Rate</p><p><span style="white-space:pre-wrap;">	</span>,T2.Weight</p><p><span style="white-space:pre-wrap;">	</span>,T2.WeightUOM</p><p><span style="white-space:pre-wrap;">	</span>,T2.Calc_ClassDesc</p><p><span style="white-space:pre-wrap;">	</span>,T2.Calc_PkgDesc</p><p><span style="white-space:pre-wrap;">	</span>,T2.Calc_WeightDflt</p><p><span style="white-space:pre-wrap;">	</span>,T2.Calc_WeightUomDflt</p><p>,T3.Company AS ShipHead_Company </p><p>,CAST(T3.BOLNum AS NVARCHAR) AS ShipHead_BOLNum</p><p>,CAST(T3.BOLLine AS NVARCHAR) AS ShipHead_BOLLine</p><p>,CAST(T4.ShipComment AS NVARCHAR) AS ShipComment , T3.PackNum AS ShipHead_PackNum ,T4.Company AS ShipDtl_Company ,T4.PackNum AS ShipDtl_PackNum ,T4.OrderNum ,T4.OurInventoryShipQty ,T4.OurJobShipQty ,T4.Packages as ShipDtl_Packages FROM BOLHead_&quot; + Parameters!TableGuid.Value + &quot; T1</p><p> LEFT OUTER JOIN BOLDetail_&quot; + Parameters!TableGuid.Value + &quot; T2</p><p>  ON T1.Company = T2.Company AND T1.BOLNum = T2.BOLNum </p><p>LEFT OUTER JOIN ShipHead_&quot; + Parameters!TableGuid.Value + &quot; T3 </p><p><span></span></p><p>  ON T2.Company = T3.Company AND T2.BOLNum = T3.BOLNum AND T2.BOLLine = T3.BOLLine LEFT OUTER JOIN ShipDtl_&quot; + Parameters!TableGuid.Value + &quot; T4  ON T3.Company = T4.Company AND T3.PackNum = T4.PackNum&quot;</p><p><span><br></span></p><p><span>Thanks,</span></p><p><span>Larry</span></p><p><span><br></span></p><p></p>

</div>
 


<div style="color:#fff;min-height:0;"></div>