none
Select distinct record based on column

    Question

  • Hello All,

    I have a table that has more than one row as Detail for a single invoice number(screenshot below). When I create report based on this table, I get multiple rows for single invoice. I mean Invoice 000027 shows up with 5 different rows on the SSRS report. Is there a way I could display a single row for a single invoice? Any thoughts on this will be greatly appreciated. Thanks.

    Regards,

    Amol

    eport )

    Tuesday, January 14, 2014 7:51 PM

All replies

  • You should create your dataset something like below.

    SELECT C.InvoiceNumber, STUFF ((SELECT DISTINCT ',' + C1.Detail FROM dbo.Claims C1
        WHERE C.InvoiceNumber = C1.InvoiceNumber
        FOR XML PATH(''), ROOT('MyString'), TYPE).value('/MyString[1]','VARCHAR(MAX)'), 1, 1,'') AS Detail
    FROM dbo.Claims C   
    GROUP BY C.InvoiceNumber


    Ione

    Tuesday, January 14, 2014 9:50 PM
  • You need to add group in SSRS Table. Right click on rows of table and "Add Group => Parent Group" and select invoice as parent. Specify your Detail column in Details rows of Table. Your tables group at the bottom pane in BIDS or Visual studio should be similar to below screen capture.

    Regards,

    Sami

    Tuesday, January 14, 2014 10:31 PM
  • Hi,

    1. In SSRS, create a Group by (InvoiceNumber).

    2. Show one group by page.

    Tuesday, January 14, 2014 11:14 PM
  • Hi Amol,

    In your case, we can use matrix to display the data to work around the issue. Please refer to the following steps:
    1. Add a matrix in the report body.
    2. Add InvoiceNumber field in the Rows textbox, add Detail field in the Data text box.
    3. Preview the report, it will display the first Detail data in the matrix.

    In SSRS, values in matrix cells display aggregate values scoped to the intersection of the row and column groups to which the cell belongs. If the field is a Numeric data type, then, it will display summary values. If not, it will display the first data value of the group.

    There is an article about Matrix, you can refer to it.
    http://technet.microsoft.com/en-us/library/dd207149.aspx#AddingMatrix

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Wednesday, January 15, 2014 7:50 AM
  • Thank you all for your suggestions. The issue is how can I get single distinct columns after joining 2 tables (InvoiceHeader & InvoiceDetail). I am using following query in my dataset.

    SELECT DISTINCT I.recid, I.InvNumber, D.Detail, I.CompanyName, I.Addr1, I.SalesRep, I.JobNumber, I.Status, I.InvDate, I.TotalInvAmount
    FROM            InvoiceHeader AS I INNER JOIN
                             InvoiceDetails AS D ON I.InvNumber = D.InvoiceNumber
    WHERE        (I.InvNumber = @InvoiceNumber) AND (I.InvDate >= @StartDate) AND (I.InvDate <= @EndDate) AND (I.CompanyName IN (@Customer)) AND (I.Status IN (@Status)) OR
                             (I.InvDate >= @StartDate) AND (I.InvDate <= @EndDate) AND (I.CompanyName IN (@Customer)) AND (I.Status IN (@Status)) AND (I.JobNumber = @JobNo) OR
                             (I.InvNumber = @InvoiceNumber) AND (I.InvDate >= @StartDate) AND (I.InvDate <= @EndDate) AND (I.CompanyName IN (@Customer)) AND (I.Status IN (@Status)) 
                             AND (@JobNo = '') OR
                             (I.InvDate >= @StartDate) AND (I.InvDate <= @EndDate) AND (I.CompanyName IN (@Customer)) AND (I.Status IN (@Status)) AND (I.JobNumber = @JobNo) AND 
                             (@JobNo = '') OR
                             (I.InvDate >= @StartDate) AND (I.InvDate <= @EndDate) AND (I.CompanyName IN (@Customer)) AND (I.Status IN (@Status)) AND (@JobNo = '') AND 
                             (@InvoiceNumber = '')
    ORDER BY I.InvNumber DESC

    Thanks again.

    Amol

    Wednesday, January 15, 2014 4:50 PM
  • Did you try using my query?

    Ione

    Wednesday, January 15, 2014 4:57 PM
  • Yes, I did try your query. It worked, but my boss wants the first line of the detail to show up in the report. When I ran the query you suggested, the rows did merge per Invoice Number but Detail section was delimited using commas. Following is the screenshot of the query. Thanks.

    Amol

    Wednesday, January 15, 2014 5:16 PM
  • Could you please post the results you expect to see and some sample date, will re-write the query accordingly.

    Thanks.......


    Ione


    • Edited by ione721 Wednesday, January 15, 2014 5:52 PM incomplete
    Wednesday, January 15, 2014 5:30 PM
  • Hi Ione,

    I appreciate your quick response. Following is the screenshot of current report. I want to have single row for Invoice 000027. I mean in the detail section, I need first 1 or 2 lines and get rid of rest of the lines from the report.  Thanks again.

    Sincerely,

    Amol

    Wednesday, January 15, 2014 7:02 PM
  • Sorry your description is very vague and does not make any sense. Please post sample date for Invoice #000027 & screenshot of expected results.

    Thanks...........


    Ione

    Wednesday, January 15, 2014 7:11 PM
  • Hi Ione,

    Following is the sample data for Invoice #000027:

    Query used for above:

    SELECT DISTINCT I.recid, I.InvNumber, D.Detail, I.CompanyName, I.Addr1, I.SalesRep, I.JobNumber, I.Status, I.InvDate, I.TotalInvAmount
    FROM            InvoiceHeader AS I INNER JOIN
                             InvoiceDetails AS D ON I.InvNumber = D.InvoiceNumber
                             ORDER BY InvNumber

    Expected result:

    In the SSRS report, I want to display just one row for Invoice# 000027 as above instead of showing 5 rows as shown in sample data screenshot. I hope that clears the confusion. Thanks.

    Regards,

    Amol

    Wednesday, January 15, 2014 7:42 PM
  • Ok Looks like I get some of the requirement. Form what I understand you need just the 1st record for the Details, Right? What is the criteria for knowing the first record?
    Please explain.

    Ione

    Wednesday, January 15, 2014 7:50 PM
  • Yes, I just need the 1st record for the detail for each Invoice. I am using following query in the  to join 2 tables (InvoiceHeader and InvoiceDetails). InvoiceDetails table has the Detail column

    SELECT DISTINCT I.recid, I.InvNumber, D.Detail, I.CompanyName, I.Addr1, I.SalesRep, I.JobNumber, I.Status, I.InvDate, I.TotalInvAmount
    FROM            InvoiceHeader AS I INNER JOIN
                             InvoiceDetails AS D ON I.InvNumber = D.InvoiceNumber
                             ORDER BY InvNumber

    Let me know if this helps. Thanks.

    Regards,

    Amol

    Wednesday, January 15, 2014 7:58 PM
  • There is no specific criteria for knowing the first record. All my manager want is the report to show first record for the detail. I am joining two tables using above query.
    Wednesday, January 15, 2014 8:18 PM
  • You still have not answered my question what is logic behind identifying the 1st detail record.

    Any way all you have to do is use Row_Number window partition function and partition it over Invoice# & order by <criteria field>.

    Hope this helps...............


    Ione

    Wednesday, January 15, 2014 8:18 PM
  • The logic behind identifying the 1st record is to have 1 row per invoice displayed on the report. At some point we may have 1000 invoices. 
    Wednesday, January 15, 2014 8:48 PM
  • Well my question is for the Invoice #000027 how do you infer "===COVER=== Indigo, Generic Perfect Bound, is the 1st Detail record?

    Ione

    Wednesday, January 15, 2014 9:15 PM
  • Hi Amol_SCI,

    If I understand correctly, you want to dispaly the first recod of the same InvoiceNumber in SSRS report. Please try to use matix to display this data in report to work around the issue. You can refer to the following screenshoot:

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Thursday, January 16, 2014 1:29 AM