Creating a Duplicate Report in Bullhorn Reporting (Previously Canvas)

It’s good practice to confirm your Bullhorn data is clean by searching for and eliminating duplicate records. You can use Bullhorn Reporting to create a report that shows you the duplicate candidates in your system by their email address. This article covers how to create a duplicate report in Bullhorn Reporting.

The following instructions assume you’re starting with a blank report. See Creating a Query Join for more information before you begin.

Creating the Queries

You will create multiple queries first. You need one query to find which email addresses have multiple associated records and a second query to find the information from those duplicate records.

After they are created you will join the two queries to create your list, which will display the duplicate email addresses and their associated records. This is done in multiple steps so you don’t end up with a report of your entire database. Follow the steps below to create your queries.

  1. Select the Reports drop down and select Queries.

  2. Drag a Query to the Canvas report studio, right-click the new query and select Rename Query. Rename the query "Candidate per email".
  3. Double click the new query.
  4. From the Sources tab, expand Candidate Analysis > Candidate, and drag Email 1 onto the Data items field.
  5. From the Toolbox tab, drag Query Calculation onto the Data items field. This tells Canvas to identify which email addresses have duplicates by counting the number of candidates per email address.
  6. Paste the below snippet into the Expression Definition field:
    Copy
    Count (distinct [PRESENTATION VIEW - CANDIDATE ANALYSIS].[CANDIDATE].[userID] for [Email 1])

  7. In the Name field, name your new data item and select OK. Name it “Candidate count per email”.
  8. Drag Candidate count per email to the Detail Filters field.

  9. In the Expression Definition, after [Candidate count per email], type “ > 1” and select OK.
    • This step ensures that when there is more than one candidate record associated to a single email address, it will show on the report.
  10. To tell Canvas to look up specific peices of information needed on each record, select the Report dropdown and select Queries again.
  11. In the Query Explorer select Queries. Drag in a new Query, right-click the new query and select Rename Query. Name it “Candidate Info”.
  12. Double-click the new query.
  13. Add the following items to the Data items field.
    • Email 1
    • Candidate ID
    • Primary Phone
    • Name

Joining the Queries

The queries need to be joined so that when Candidate per email finds a duplicate, Candidate Info will pull the information from those duplicate records. The final result will aggregate in your joined query.

  1. Go back to Query Explorer and select Queries.
  2. Double-click Join. This will create a new query along with a join. Drag Candidate per email and Candidate Info to their respective boxes in the join.

  3. Right-click the new query and select Rename Query. In our example we call it "Duplicate Candidates".
  4. Double-click the new join, select New Link. Make sure the link is set for Email 1 for both queries, and select OK.
  5. Double-click Duplicate Candidates.
  6. Drag Candidate Info to the Data items field.

Creating the List

Follow the steps below to create a list on your report page. It will display information from the joined query.

  1. Go to the Report drop down and select Page1. Or whichever page of your report that you would like it to show on.
  2. From the Toolbox, double-click List.

  3. In the Object and Query Name pop-up, select Duplicate Candidates for the Query Name, and select OK.
  4. From the Data Items tab, select all the fields from Duplicate Candidates and drag them onto the list.
  5. Highlight the Email 1 column body.
  6. Select the Group/Ungroup icon from the Toolbar.
  7. With the same column body still highlighted, go to Sort and choose Descending. This will ensure that entries with blank email addresses show at the end of the report, rather than the beginning.
  8. Click Save and Run your report.

You now have a report that will show you which candidates in your database are duplicates based on their email address. You can further refine your report by making the ID field hyperlink to the candidate records. See Linking to Bullhorn Records in Bullhorn Reporting (Previously Canvas) for more information.