Eliminating Duplicate Rows

The DISTINCT keyword will help you clean up your report output by eliminating duplicate rows from the output.

1.  Go to the central list of custom reports (Menu, Reports, Create/Edit Custom Reports) and select the Create icon located in the upper right corner of the list to create a new report.

2.  Enter New Intakes in Period in the Report Name field.

3.  Enter the following query:

SELECT tblClient.colClientID, colFirstName, colLastName
FROM tblClient
INNER JOIN tblIntake ON tblClient.colClientID = tblIntake.colClientID
WHERE colRequestDate >= @StartDate
AND colRequestDate <= @EndDate
AND tblIntake.colProgramID IN (SELECT colProgramID FROM RelatedProgramIDs(@ProgramID))

4.  Select the Preview button to preview the results:

Just like the example above shows client #25 listed three times and client #48 listed twice, your query output may also have some duplicate rows.  Let’s add the DISTINCT keyword to the query to eliminate this.

5.  Close the query preview page to return to the query-building page, and revise your query as follows:

SELECT DISTINCT tblClient.colClientID, colFirstName, colLastName
FROM tblClient
INNER JOIN tblIntake ON tblClient.colClientID = tblIntake.colClientID
WHERE colRequestDate >= @StartDate
AND colRequestDate <= @EndDate
AND tblIntake.colProgramID IN (SELECT colProgramID FROM RelatedProgramIDs(@ProgramID))

6. Select the Preview button to preview the results:

The duplicate records are eliminated so that each client is only returned once.  This change will be reflected in your published report, making the report easier to read for users.

Close the query preview page, and close the custom report query file.  Continue to “Adding Click-Through Links” to learn how to add a click-through link to your report.