locked
Enabling SELECT DISTINCT with a parameter RRS feed

  • Question

  • User232815449 posted

    Hello I am trying to avoid duplicating reports and use a parameter in my SSRS report where if selected to true or checked the query will only return distinct records based on the values of one of my columns.  If not checked I want everything.  Is this possible in SSRS?  Anyone have a sample I can use as a guide?

    Thanks for your assistance.

    Thursday, April 24, 2014 2:26 PM

Answers

  • User-1716253493 posted

    Maybe you can combine All Data with extra column checked = 'true' UNION ALL Disctinct Data with extra column checked='false'

    SELECT DISCTINCT ..., checked = 'true' FROM ....
    UNION ALL
    SELECT ...., checked='false' FROM ...

    Then filter the report by checked column

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 24, 2014 10:21 PM

All replies

  • User-993404089 posted

    Can you provide the query? 

    Thursday, April 24, 2014 10:05 PM
  • User-1716253493 posted

    Maybe you can combine All Data with extra column checked = 'true' UNION ALL Disctinct Data with extra column checked='false'

    SELECT DISCTINCT ..., checked = 'true' FROM ....
    UNION ALL
    SELECT ...., checked='false' FROM ...

    Then filter the report by checked column

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 24, 2014 10:21 PM
  • User232815449 posted

    Here is the query I am using which works I just need to add the SELECT DISTINCT switch so the user can choose to get all records or just the distinct records based on the householdID field.

    SELECT        adr.householdID, CONVERT(varchar(10), stu.calendarID) AS CalID, cal.name AS SCHNUM, CONVERT(varchar(10), stu.grade) AS grade, stu.studentNumber, 
                             stu.lastName, stu.firstName, stu.middleName, adr.addressLine1, adr.addressLine2
    FROM            CAMPUS.clarkcounty_convB2.dbo.student AS stu INNER JOIN
                             CAMPUS.clarkcounty_convB2.dbo.v_MailingAddress AS adr ON stu.personID = adr.personID INNER JOIN
                             CAMPUS.clarkcounty_convB2.dbo.Calendar AS cal ON cal.calendarID = stu.calendarID
    WHERE        (stu.calendarID IN (@CalID)) AND (stu.grade IN (@Grade))
    GROUP BY stu.calendarID, cal.name, stu.grade, stu.studentNumber, adr.addressLine1, adr.addressLine2, stu.firstName, stu.middleName, stu.lastName
    ORDER BY grade, stu.lastName

    Friday, April 25, 2014 11:50 AM
  • User232815449 posted

    Your suggestion worked perfectly, here is my finished query:

    SELECT * FROM ( SELECT
                             adr.householdid, CONVERT(varchar(10), stu.calendarID) AS CalID, cal.name AS SCHNUM, CONVERT(varchar(10), stu.grade) AS grade, stu.studentNumber, 
                             stu.lastName, stu.firstName, stu.middleName, adr.addressLine1, adr.addressLine2, 
    		ROW_NUMBER() OVER(PARTITION BY adr.householdid ORDER BY stu.grade) rn
    FROM            CAMPUS.convB2.dbo.student AS stu INNER JOIN
                             CAMPUS.convB2.dbo.v_MailingAddress AS adr ON stu.personID = adr.personID INNER JOIN
                             CAMPUS.convB2.dbo.Calendar AS cal ON cal.calendarID = stu.calendarID
    WHERE        (stu.calendarID IN (@CalID)) AND (stu.grade IN (@Grade)) AND (@Checked = 'True')
    GROUP BY stu.calendarID, cal.name, stu.grade, stu.studentNumber, adr.addressLine1, adr.addressLine2, stu.firstName, stu.middleName, stu.lastName, adr.householdID) A 
    WHERE  rn = 1
    
    UNION ALL
    SELECT        adr.householdID, CONVERT(varchar(10), stu.calendarID) AS CalID, cal.name AS SCHNUM, CONVERT(varchar(10), stu.grade) AS grade, stu.studentNumber, 
                             stu.lastName, stu.firstName, stu.middleName, adr.addressLine1, adr.addressLine2, ROW_NUMBER() OVER(PARTITION BY ADR.HOUSEHOLDID ORDER BY STU.GRADE, stu.lastname) rn
    FROM            CAMPUS.convB2.dbo.student AS stu INNER JOIN
                             CAMPUS.convB2.dbo.v_MailingAddress AS adr ON stu.personID = adr.personID INNER JOIN
                             CAMPUS.convB2.dbo.Calendar AS cal ON cal.calendarID = stu.calendarID
    WHERE        (stu.calendarID IN (@CalID)) AND (stu.grade IN (@Grade)) AND (@Checked = 'False')
    GROUP BY stu.calendarID, cal.name, stu.grade, stu.studentNumber, adr.addressLine1, adr.addressLine2, stu.firstName, stu.middleName, stu.lastName, adr.householdID



    Friday, April 25, 2014 3:19 PM