SSRS Sales Order Pick List (SOPick) Duplicating Lines when printing multiples

Kinetic ERP
,
1

So I set up some automated printing for Order Pick Lists. I have a function that checks for any new fulfillments to picking based on the timestamp, then it builds an order list (this is a report parameter), and sends them to print. It works great EXCEPT…

I had users complaining that it would print multiple, identical lines sometimes. They sent me some examples, and I printed both reports, and they looked fine. I had them send me pictures, and sure enough, there were dupe lines, and it looked different than what I was printing.

After messing around with it for way too long, I discovered that A) If I print multiple orders in the same print job:

image
image1073×283 8.5 KB

And B) any of the orders have the an PartAlloc record with the same part and bin as another report:

image
image814×123 30.6 KB

It duplicates the rows.

image
image513×130 10.9 KB

I confirmed sending the report as CSV, and sure enough, if I send order 350150, it’s fine, single row. If I send order 350257, same thing, looks great. If I send both at the same time, though… dupes:

image
image603×228 5.28 KB

I’m not sure where to go with this. I’m pretty sure my query in the report itself is good, and the RDD seems to be correct. Any advice? Happy to share any other info that might help.

2 Likes
2

How is your SSRS report grouped?

3

Page > OrderNum > OrderLine

1 Like
5

Post the joins in the RDL

6 
 FROM OrderHed_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T2
     ON T1.Company  = T2.Company 
    AND T1.OrderNum = T2.OrderNum
 LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value + " T3
     ON T2.Company   = T3.Company 
    AND T2.OrderNum  = T3.OrderNum 
    AND T2.OrderLine = T3.OrderLine
 LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " T4
     ON T3.Company     = T4.Company 
    AND T3.OrderNum    = T4.OrderNum 
    AND T3.OrderLine   = T4.OrderLine 
    AND T3.OrderRelNum = T4.OrderRelNum
 LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T5
     ON T1.RptLanguageID = T5.RptLanguageID 
 LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " CUS
     ON T1.Company = CUS.Company 
    AND T1.CustNum = CUS.CustNum 
 LEFT OUTER JOIN ShipTo_" + Parameters!TableGuid.Value + " STO   
     ON T1.Company   = STO.Company 
    AND T1.CustNum   = STO.CustNum 
    AND T1.ShipToNum = STO.ShipToNum
 JOIN (
        SELECT DISTINCT Company, OrderNum, OrderLine, OrderRelNum, PickingQty, PartNum, DimCode, BinNum 
        FROM PartAlloc_" + Parameters!TableGuid.Value + " 
        WHERE PickingQty > 0
    ) PA 
     ON T3.Company   = PA.Company 
    AND T3.OrderNum  = PA.OrderNum
    AND T3.OrderLine = PA.OrderLine
    AND T3.OrderRelNum  = PA.OrderRelNum
 LEFT OUTER JOIN (
        SELECT Company, PartNum, NetWeight FROM Part_" + Parameters!TableGuid.Value + "
    ) PARTS 
     ON PARTS.Company = PA.Company 
    AND PARTS.PartNum = PA.PartNum 
 LEFT OUTER JOIN WhseBin_" + Parameters!TableGuid.Value + " WB
     ON PA.Company = WB.Company
    AND PA.BinNum  = WB.BinNum
7

The SELECT DISTICT on PartAlloc is one thing I tried to fix it. Previously was just a join to PartAlloc.

8

That’s not going to work because you have 2 orders/lines.

9

Two orders and 30+ lines. There are lines on each two that have the same PartNum / BinNum in PartAlloc as on the other order (different line numbers).

10

What data are you getting from WhseBin?

11

Just the Bin Sequence.

12

Take that out and see what happens.

13

Dang, no luck. Same result.

14

Sorry, did you take the join out too? Comment out the whole join

15

I just removed it (and the related field and sort selection).

16

Now take out the Part table. Keep on working your way up until you find what is duplicating it.

17

Also, is that part a sub report? Check that you are sending the right parameters if it is.

18

@jkane @dr_dan Well this is embarrassing. I had added an additional Tablix for some of the PartAlloc rows, and didn’t add a “GroupBy” statement on the details group of the table. Adding Grouping on Bin and OrderNum solved my problem. Thank you both for all the help!

4 Likes
19

We’ve all been there

3 Likes