locked
How to eliminate the duplicated Sales Line Items RRS feed

  • Question

  • I created a shipping report in SSRS format.  This particular shipping report needs to be combined several tables (see my table list below).  After joing all tables together, I found out some sales line item will have duplicated lines since the serial number and tracking number.  For example: If a sales order (SO12345) have two line items (A and B).  First line item (item A) have qty shipped for 4 so there will be 4 serial number pulls from "Serial Number" table.  But this Sales order also have two shipping tracking numbers.  So my Line Item # 1 (item A) will repeated be duplicated 4 times due to 4 serial numbers (from serial number table).

    My question is how I can eliminate those duplicated lines but I do need to provide the shipping data with all serial number and tracking number associated with Sales order document?  Can I use "Matrix" format in SSRS report. Please advise. Thanks!

     

    1. Sales Header Table

    2. Sales Line Detail Table

    3. Serial Number for Qty SHipped

    4. Tracking Number Table

     


    JT
    Wednesday, January 25, 2012 9:08 PM

Answers

  • Josephine,

    You can use Grouping Tutorial: Adding Grouping, Sorting, and Formatting to a Basic Report

    If you feel group is not suitable in your scenario then Select your cell & set HideDuplicates Poperty as shown in below image.

    Please refer TextBox.HideDuplicates Property

     

    Thanks,
    Sandip Shinde
    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Marked as answer by Challen Fu Thursday, February 2, 2012 8:54 AM
    Friday, January 27, 2012 7:54 AM
  • Hi,

    I would suggest consider sending 0 or NULL for duplicate values, specially in case of amount or quantity from back end itself using Window function of T-SQL, otherwise it may lead to some wrong calculation on report.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Proposed as answer by Sanjeewan Kumar Friday, January 27, 2012 8:58 AM
    • Marked as answer by Challen Fu Thursday, February 2, 2012 8:54 AM
    Friday, January 27, 2012 8:09 AM

All replies

  • Hi Josephine, 

    Can you provide some sample data?

    I guess you can use matrix and group the columns appropriately.  


    regards Natraj www.BItipsntricks.com Mark as Answer, if it is answer for your Question. Vote as Helpful, if it is helpful to you.
    Thursday, January 26, 2012 1:37 AM
  • Josephine,

    You can use Grouping Tutorial: Adding Grouping, Sorting, and Formatting to a Basic Report

    If you feel group is not suitable in your scenario then Select your cell & set HideDuplicates Poperty as shown in below image.

    Please refer TextBox.HideDuplicates Property

     

    Thanks,
    Sandip Shinde
    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Marked as answer by Challen Fu Thursday, February 2, 2012 8:54 AM
    Friday, January 27, 2012 7:54 AM
  • Hi,

    I would suggest consider sending 0 or NULL for duplicate values, specially in case of amount or quantity from back end itself using Window function of T-SQL, otherwise it may lead to some wrong calculation on report.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Proposed as answer by Sanjeewan Kumar Friday, January 27, 2012 8:58 AM
    • Marked as answer by Challen Fu Thursday, February 2, 2012 8:54 AM
    Friday, January 27, 2012 8:09 AM