Report shows same information several times

• lindavol

  SSC Rookie

  Points: 38

  More actions

  March 19, 2014 at 6:18 pm

  #305250

  Report Builder 3.0

  My report shows the same figures over and over again. How do I get my report to show only one line in the report.

  FROM

  sv00510

  FULL OUTER JOIN SV00701

  ON SV00510.Contract_Number = SV00701.Contract_Number and SV00510.ADRSCODE = SV00701.ADRSCODE

  WHERE

  sv00510.Billable_Total > 1.00

  AND sv00510.Contract_Number IN (N'000790')

  AND SV00701.Service_User_Define_2 IN (N'MC', N'MCC')

  Is there something that I can do in this section to fix that.

  Thank you

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  March 20, 2014 at 1:41 pm

  #1698877

  Several options:

  - add either a DISTINCT to your query, so that it returns one row for each unique set of values

  - add a group to your report and put the columns there.

  - use a CTE to remove the duplicates from your dataset.

  SELECT ..., ROW_NUMBER() OVER (PARTITION BY groupingField) AS rn

  WHERE rn=1;