Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013
    Posts
    1

    Unanswered: Records repeat for SSRS 2005 report

    When I run a report for a purchase order, the report duplicates records for product codes.

    For example the purchase order is: P000976, the report display the product code twice when it should only appear once. 45-5540 appears twice.

    P000976 09-17-2012 15,040.00 15,040.00 0.00
    45-5540 "Lordotic Cervical Spacer 10mm
    Lordotic Cervical Spacer 10mm" 20 20 0
    45-5540 "Lordotic Cervical Spacer 10mm
    Lordotic Cervical Spacer 10mm" 20 20 0


    When I put the report's SQL in SQL server and run the sql by seeing where the code cause the additional product code it is this line within the SQL:

    join all_product_codes_VW p on q.distpartno = p.distpartno

    select q.specialrequirement
    , q.distpartno
    , q.toproduce
    , q.prodbegindate
    , q.distributor
    , rc.report_category_name
    , s.productperpo
    , r.ebi_released
    , w.ebi_in_WIP
    , p.distproductname
    , tp.typeprefixdetail
    , tp.cost
    , '1' as ReportTotals

    from all_required_vw q
    left join all_shipped_grafts_new_VW s on (q.distpartno = s.distpartno and q.specialrequirement = s.ponumber)
    left join all_released_Grafts_VW r on q.distpartno = r.distpartno
    left join all_in_WIP_VW w on q.distpartno = w.distpartno
    join all_product_codes_VW p on q.distpartno = p.distpartno
    join setup_tissue_prefix tp on q.typenumber = tp.typeprefix
    join setup_report_category_1 rc on q.distributor = rc.report_category_id

    where q.prodbegindate < @enddate
    and q.completed = '0'
    and rc.report_category_name like '%' + isnull(@tcustomer, '') + '%'

    order by q.prodbegindate, p.distproductname

    This is the SQL for the view for which the join creates the duplicate.

    SELECT COUNT_BIG(*) AS BIG, DistPartNo, DistProductName, Distributor, UMTBProductCode
    FROM dbo.Setup_Distributor_Product_info
    WHERE (Distributor <> '7') OR
    (Distributor IS NULL)
    GROUP BY DistPartNo, DistProductName, Distributor, USSAProductCode

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You haven't provided enough details for us to answer your question.

    The SELECT statement you posted for all_product_codes_VW won't work as posted.

    Maybe someone else has more insight than I do, but I see two possible ways to help you. We can talk you through the steps to solve it yourself, but that could be tedious (several hundred questions and answers). You can send us your full schema, and maybe a very small subset of your data so we can recreate and repair the problem (probably less than a dozen questions and answers). You can pick whichever solution suits you!

    If you want to work through the problem yourself, how do you know which join is causing the problem? Do duplicate rows exist in the underlying view? Are columns missing from the join criteria?

    If you want to go through the schema/data process, please post the actual code as produced by sp_helptext for all of the view definitions in your query as well as every views that they reference. Post the sp_help output for all of the tables used by those views. This is likely to be a lot of output, but we need it (and maybe more) in order to get to the bottom of your problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •