Creating a Recruiter Activity Report

The following instructions will help you to create a report in Bullhorn Reporting that shows you the Fill Rate, Hit Rate, various average times and percentages for your recruiters in your desired department.

Adding the Count Columns

We are working in the User Activity Analysis package since it contains the fields that we desire in our report. We’re adding fields as you see listed in the above image, however you can select other fields as desired to customize your own report.

Steps

  1. Create a List Report by following the steps in this article: Building and Customizing a List Report in Bullhorn Reporting (Previously Canvas)
  2. In Insertable Objects, under the Sources tab, expand User Activity Analysis > User.
  3. Double-click Name and UserID to add them to the table.
    • We added the UserID to ensure recruiters with the same name will have separate rows on the report. However, we don’t want to see that column, so we’ll hide it next. If you don’t have recruiters with similar names, you can skip adding the UserID and the rest of the steps in this section.
  4. On the table, select the <User ID> rows.

  5. In the Show Proprieties,click the Select ancestor and select List Column.
  6. In the Show Proprieties, under the Conditional section, select Render and choose No.
    • This will hide the column from view on the report.

Adding Note Counts

To show the number of prescreens, which are indicated in the system as a note type, we will add in the note count and then edit the expression for the field so that only notes with a type of prescreen are counted.

Steps

  1. In the table, click the User ID column.
  2. Still under Insertable Objects > Sources > User Activity Analysis, click Note Template Added > Note Template.
  3. Double-click Note Template Count to add it to the table.
  4. In the table, double-click the data rows for Note Template Count.
  5. In the Data item expression - Note Template Count pop-up, in the Expression Definition box, place your cursor at the beginning of the prepopulated expression and type “CASE WHEN "
    • Make sure to include the space after WHEN.
  6. Place your cursor after that space.
  7. Under Available Components expand User Activity Analysis > Note Template Added > Note Template.
  8. Double-click Action to add it to the Expression Definition.
  9. Your cursor should be right after [action]. Leave it here and type " = ".
    • Make sure to include the space before and after the =.
  10. Still under Available Components > User Activity Analysis > Note Template Added > Note Template, right click Note Action and choose Select Value….
  11. In the Select Value pop-up, enter “Prescreen” into the Find field and click Search.
  12. Double-click Prescreen.
  13. Place your cursor after ‘Prescreen’. Press Enter on your keyboard and then type “THEN ”.
    • Make sure to include the space after THEN.
  14. Place your cursor at the very end of the expression, after [ROW COUNT], and type “ END”.
    • Make sure to include the space before END.
  15. The expression should look like this:
    Copy
    CASE WHEN [Note ADDED].[Note].[action] = 'Prescreen'
    THEN [Note ADDED].[Note].[ROW COUNT] END
    • Query Definition: If the note type is 'Prescreen', add to the count.
  16. Click Validate.
  17. Fix any errors that come up.
  18. Click OK.
  19. In the table, select the column header for Note Template Count.
  20. In the Show properties, under Data Item in the Name field, enter "Prescreens".
  21. Still under Insertable Objects > Sources > User Activity Analysis, click Submission Added > Submission.
  22. Double-click Submission Count.
  23. In the table, click the Submission Count column header.
  24. In the Show properties, under Data Item in the Name field, enter "Submissions".
  25. Still under Insertable Objects > Sources > User Activity Analysis, click Client Submission Added > Client Submission.
  26. Double-click Client Submission Count to add it to the table.
  27. In the table, click the Client Submission Count column header.
  28. In the Show properties, under Data Item in the Name field, enter "Client Submissions".
  29. Still under Insertable Objects > Sources > User Activity Analysis, click Interview Owned > Interview.
  30. Double-click Interview Count to add it to the table.
  31. In the table, click the Interview Count column header.
  32. In the Show properties, under Data Item in the Name field, enter "Interviews".

Adding More Count Columns

To view the number of job offers a recruiter has extended, we want a column that will count submissions where the status is or was previously set to “Offer Extended”.

Steps

  1. Still under Insertable Objects > Sources tab > User Activity Analysis, click Submission Added > Submission.
  2. Double-click Submission Count to add it to the table.
  3. In the table, click the Submission Count column header.
  4. In the Show properties, under Data Item in the Name field, enter "Job Offers".
  5. Double-click the data rows for Job Offers.
  6. In the Data item expression - Job Offers pop-up, in the Expression Definition box, place your cursor at the beginning of the prepopulated expression and type “CASE WHEN "
    • Make sure to include the space after WHEN.
  7. Place your cursor after that space.
  8. Under Available Components click User Activity Analysis > Submission Added > Submission History.
  9. Double-click Status to add it to the Expression Definition.
  10. Your cursor should be right after [status]. Leave it here and type " = ".
    • Make sure to include the space before and after the =.
  11. Still under Available Components > User Activity Analysis > Submission Added > Submission History, right-click Status and choose Select Value….
  12. In the Select Value pop-up, enter “Offer Extended” into the Find field and click Search.
  13. Double-click Offer Extended.
  14. Place your cursor after ‘Offer Extended’. Press Enter on your keyboard and then type “THEN ”.
    • Make sure to include the space after THEN.
  15. Place your cursor at the very end of the expression, after [ROW COUNT], and type “ END”.
    • Make sure to include the space before END.
  16. The expression should look like this:
    Copy
    CASE WHEN [SUBMISSIONS ADDED].[SUBMISSION HISTORY].[STATUS] = 'Offer Extended'
    THEN [SUBMISSIONS ADDED].[SUBMISSION ADDED].[ROW COUNT] END
  17. Click Validate.
  18. Fix any errors that come up.

  19. Click OK.
  20. Still under Insertable Objects > Sources tab, click Job Analysis > Job Placement.
  21. Double-click Placement Count twice.
    • We will be adding two placement count columns, one for Hires and one for Starts. Starts are placements that are currently or were previously approved.
  22. In the table, click the Placement Count column header.
  23. In the Show properties, under Data Item in the Name field, enter "Hires".
  24. In the table, click the Placement Count1 column header.
  25. In the Show properties, under Data Item in the Name field, enter "Starts".
  26. Double-click the Starts data rows.
  27. In the Data item expression - Starts pop-up, in the Expression Definition box, place your cursor at the beginning of the prepopulated expression and type “CASE WHEN "
    • Make sure to include the space after WHEN.
  28. Place your cursor after that space.
  29. Under Available Components click User Activity Analysis > Job Owned > Job Placement.
  30. Double-click Status to add it to the Expression Definition.
  31. Your cursor should be right after [status]. After [status] type “ = ‘Approved’” and then press Enter on your keyboard.
    • Make sure to include the space before and after the =.
  32. With your cursor placed at [PRESENTATION VIEW], type “THEN ”.
    • Make sure to include the space after THEN.
  33. Place your cursor at the very end of the expression, after [ROW COUNT], and type “ END”.
    • Make sure to include the space before END.
  34. The expression should look like this:
    Copy
    CASE WHEN [JOB OWNED].[JOB PLACEMENT].[status] = 'Approved'
    THEN [PRESENTATION VIEW - JOB ANALYSIS].[JOB PLACEMENT].[ROW COUNT] END
  35. Click Validate.
  36. Fix any errors that come up.

  37. Click OK.

Creating the Calculations Using Columns

We will be creating 10 calculations for our report, including:

  • Submissions/Pre-Screens
  • Client Submissions/Submissions
  • Interviews/Client Submissions
  • Job Offers/Interviews
  • Starts/Job Offers
  • Fill Rate
  • Hit Rate
  • Avg Time to Internal Submit
  • Avg Time to Client Submit
  • Avg time to Fill

The first five of these are simple division and they will all follow the same steps.

It’s important to note that the order in which you highlight the columns matters since Canvas inputs the first column selected as the dividend and the second column as the divisor.

Steps

  1. Using CTRL on your keyboard click the table's column headers for Submissions and then Prescreens.
  2. Click Insert Calculation > Submissions / Prescreens.
  3. Clicking the corresponding column headers, follow the same two above steps for the below calculations:
    • Client Submissions / Submissions
    • Interviews / Client Submission
    • Job Offers / Interviews
      • It’s important to note that the order in which you highlight the columns matters since Canvas inputs the first column selected as the dividend and the second column as the divisor.
    • Starts / Job Offers
      • It’s important to note that the order in which you highlight the columns matters since Canvas inputs the first column selected as the dividend and the second column as the divisor.
  4. Click one of the column headers you created, for example Submissions / Prescreens.
  5. Click and drag that column to put it next to one of the other calculation columns created above. For example, we are moving Submissions / Prescreens after Interviews / Client Submissions and before Submissions.
  6. Repeat this process until all of the above created calculation columns are grouped together.
    • If you find the columns aren't responding to your clicks, it may be because the entire table still thinks it's selected. Make sure you click the column header first each time. When the table rows change from blue to white, you know you have the column selected correctly.
  7. Holding down CTRL on your keyboard click the data rows for the division columns you created and grouped together.
  8. In the Show properties, under Data , click Data format > More.
  9. In the Data format pop-up, click the Format type drop-down select Percent.
  10. Under Properties, click Number of Decimal Places, then from the drop-down, select 0.
  11. Click OK.
  12. The below calculations are similar in concept but require additional steps:
    • Fill Rate = # Placements / # Jobs
    • Hit Rate = # Placements / # Client Submissions
    • Avg Time to Internal Submit = # days between Job added date and first submission to the job
    • Avg Time to Client Submit = # days between Job added date and Client Submission date
    • Avg time to Fill = # days between Job added date and first placements for the Job.

Creating the Calculations Without Columns

Performing calculations usually requires that columns are added into the report for the necessary data, but we don’t want to add these columns to this report since we don’t need to see this data. The below instructions will show you how to calculate them without having to add in the columns.

Steps

  1. Under Insertable Objects, click the Toolbox > Textual.
  2. Drag a Query Calculation to the end of your list.

  3. In the Data item expression - Data Item1 pop-up, under Available Components, click User Activity Analysis > Job Owned > Job Placement.
  4. Double-click Placement Count.
  5. In the Expression Definition box, place your cursor after [ROW COUNT] and type " / ".
    • Be sure to include the leading and trailing spaces.
  6. With your cursor still here, look back under Available Components > User Activity Analysis > Job Owned, click Job.
  7. Double click Job Count.
  8. The expression should look like this:
    Copy
    [JOB OWNED].[JOB PLACEMENT].[ROW COUNT] / [JOB OWNED].[JOB].[ROW COUNT]
  9. Click Validate.
  10. Fix any errors that come up.

  11. Click OK.
  12. In the table, click the column header for Data Item1.
  13. In the Show properties, under Data Item in the Name field, enter "Fill Rate".
  14. Using CTRL on your keyboard, click the table's column headers for Hires and then Client Submissions.
  15. Click Insert Calculation > Hires / Client Submissions.
  16. Click and then drag the new Hires / Client Submissions column all the way to the end of the report.
    • If you find the column isn't responding to your clicks, it may be because the entire table still thinks it's selected. Make sure you click the column header first. When the table rows change from blue to white, you know you have the column selected correctly.
  17. Click the column header for Hires / Client Submissions.
  18. In the Show properties, under Data Item in the Name field, enter "Hit Rate".
  19. Using CTRL on your keyboard, click the data rows Fill Rate and Hit Rate.
  20. In the Show properties, under Data, click Data format > More.
  21. In the Data format pop-up, click the Format type drop-down select Percent.
  22. Under Properties, click Number of Decimal Places, then from the drop-down, select 0.
  23. Click OK.

Calculating Averages

The remaining calculations are determining the average number of days between one date and another. To accomplish this we will use the _days_between function. It’s important to note that the _days_between function requires us to input the latest date first and the earliest date last.

If you put the data in any other order than the latest date first and the earliest date last, the output will be a negative number.

Steps

  1. Create another Query Calculation column as per Creating the Calculations Without Columns.
  2. In the Data item expression - Data Item1 pop-up, click the Functions tab.
  3. Under Available Components, click the Business Date/Time Functions.
  4. Double-click f(x)_days_between.
  5. In the Expression Definition box, place your cursor at the end of the current expression.
  6. Still in the Data item expression - Data Item1 screen, click on the Source tab > User Activity Analysis > Submission Added > Submission.
  7. Double-click Date Added.
  8. In the Expression Definition box, place your cursor at the end of the current expression and type a ",".
  9. Still in the Data item expression - Data Item1 screen >Source tab > User Activity Analysis, click Job Owned > Job.
  10. Double-click Date Added.
  11. In the Expression Definition box, place your cursor at the end of the current expression and type a ")".
  12. The expression should look like this:
    Copy
    _days_between ([SUBMISSIONS ADDED].[SUBMISSION ADDED].[dateAdded],[JOB OWNED].[JOB].[dateAdded])
  13. Click Validate.
  14. Fix any errors that come up.

  15. Click OK.
  16. In the table, click the data rows for Data Item1.
  17. In the Show properties, under Data, click Data format > More.
  18. In the Data format pop-up, click the Format type drop-down select Number.
  19. Under Properties, click Number of Decimal Places, then from the drop-down, select 0.
    • This allows the result to show whole numbers.

  20. Click the Data Item1 column header.

  21. In the Show properties, under Data Item in the Name field, enter "Avg Time to Submit (Days)".

  22. Repeat all of the above steps for the remaining Calculations:

    • Avg Time to Client Submit (Days)

      Copy 
      _days_between ([SENDOUT ADDED].[SENDOUT ADDED].[dateAdded],[JOB OWNED].[JOB].[dateAdded])

    • Avg Time to Fill (Days)

      Copy 
      _days_between ([JOB OWNED].[JOB PLACEMENT].[dateAdded],[JOB OWNED].[JOB].[dateAdded])

Creating the Prompts

We want to allow our users to select a date range and department for the report.

We are creating three prompts, one each for the start and end of the date range and one for department.

Steps

  1. Select the Report drop-down and select the Prompt Pages folder.
  2. From the Toolbox, double click Page.
  3. Under the Prompt Pages section, double click Prompt Page1.
  4. Under Insertable Objects > Toolbox, click Prompting.
  5. Double-click Date Prompt.
  6. In the Prompt Wizard - Date prompt, under Create a new parameter, type “start” and select Finish.
  7. Drag another Date Prompt to the right of the first one and in the Create a new parameter field type “end” and select Finish.
  8. Drag a Value Prompt to the right of the date prompt you just created.
  9. In the Create a new parameter field type in “Department” and click Next.
  10. Select the three dots to find the Package item.
  11. In the Choose Package Item screen expand User Activity Analysis and then User and double-click Primary Department.
  12. Change the Operator to in and click Next.
  13. Click Finish.

Tying the Prompts to the Rows

We want to apply the date prompts to the dates we used in our calculations so that only relevant results show on our report.

Steps

  1. Select the Report drop-down and select Page 1.
  2. Select entire table by using 3 dots on the upper left corner.
  3. From the Filter drop-down select Edit Filters....
  4. Select the plus icon in the bottom left hand corner and choose Advanced then click OK.
  5. Under Available Components click User Activity Analysis > Note Template Added > Note Template.
  6. Double-click Date Added.
  7. Type “ between ?start? AND ?end?”.
  8. The expression should look like this:
    Copy
    [Note ADDED].[Note].[dateAdded] between ?start? AND ?end?
    • Query Definition: This is telling Bullhorn Reporting to only pull notes added between the chosen date range. This will apply to all columns where we used a note type. A note type is defined as the note action selected when the note was entered into the system.
  9. Click Validate.
  10. Fix any errors that come up.

  11. Click OK twice.
  12. Create another advanced filter, using the same Advanced Filter steps above.
  13. Under Available Components click Client Submission Added > Submission.
  14. Double-click Date Sent.
  15. Type “ between ?start? AND ?end?”.
  16. The expression should look like this:
    Copy
    [SENDOUT ADDED].[SENDOUT ADDED].[dateAdded] between ?start? AND ?end?
    • Query Definition: This is telling Bullhorn Reporting to only pull client submissions added between the chosen date range. This will apply to all columns where we used client submissions.
  17. Click Validate.
  18. Fix any errors that come up.

  19. Click OK twice.
  20. Create another advanced filter, using the same Advanced Filter steps above.
  21. Under Available Components click Interview Owned > Interview.
  22. Double-click Date Added.
  23. Type  “ between ?start? AND ?end?”.
  24. The expression should look like this:
    Copy
    [INTERVIEW OWNED].[INTERVIEW OWNED].[dateAdded] between ?start? AND ?end?
    • Query Definition: This is telling Bullhorn Reporting to only pull interviews added between the chosen date range. This will apply to all columns where we used interviews.
  25. Click Validate.
  26. Fix any errors that come up.

  27. Click OK twice.
  28. Double-click the Submissions data rows.
  29. Before the existing contents of Expression Definition type “CASE WHEN ” and press Enter on your keyboard.
  30. Place your cursor right after WHEN and expand Submission Added and then Submission and double click Date Added.
  31. Place your cursor after [dateAdded] and type “ between ?start? AND ?end? THEN ” and place your cursor at the end of the expression and type “ END”.
  32. The expression should look like this:
    Copy
    CASE WHEN [SUBMISSIONS ADDED].[SUBMISSION ADDED].[dateAdded] between ?start? AND ?end? 
    THEN [SUBMISSIONS ADDED].[SUBMISSION ADDED].[ROW COUNT] END
    • Query Definition: This is telling Bullhorn Reporting to only pull submissions added between the chosen date range. This will apply to all columns where we used submissions.
  33. Click Validate.
  34. Fix any errors that come up.

  35. Click OK.
  36. Double-click the data rows for Hires.
  37. Type “CASE WHEN ” at beginning of the existing expression and press Enter on your keyboard.
    • Make sure to include the space after WHEN.
  38. Place your cursor after WHEN.
  39. Under Available Components click Job Owned > Expand Job Placement.
  40. Double click Date Added.
  41. Type " between ?start? AND ?end? THEN ".
    • Make sure to include the space after THEN.
  42. The expression should look like this:
    Copy
    CASE WHEN [JOB OWNED].[JOB PLACEMENT].[dateAdded] between ?start? AND ?end? 
    THEN JOB OWNED].[JOB PLACEMENT].[ROW COUNT] END
    • Query Definition: This is telling Bullhorn Reporting to only pull placements added between the chosen date range. This will apply to all columns where we used placements.
  43. Click Validate.
  44. Fix any errors that come up.

  45. Click OK.
  46. Double-click the data rows for Starts.
  47. Place your cursor after "Approved".
  48. Press Enter on your keyboard.
  49. Type "AND ".
  50. Under Available Components click Job Owned > Job Placement.
  51. Double click Start Date.
  52. Type " between ?start? AND ?end?".
  53. The expression should look like this:
    Copy
    CASE WHEN [JOB OWNED].[JOB PLACEMENT].[status] = 'Approved'
    AND [JOB OWNED].[JOB PLACEMENT].[dateBegin] between ?start? AND ?end?
    THEN [JOB OWNED].[JOB PLACEMENT].[ROW COUNT] END
    • Query Definition: This is telling Bullhorn Reporting to only pull jobs added between the chosen date range. This will apply to all columns where we used placements.
  54. Click Validate.
  55. Fix any errors that come up.

  56. Click OK.
  57. Don’t forget to Save.

You have now finished your Recruiter Activity report!

For more information about advanced Bullhorn Reporting techniques you can search for “Cognos Workspace Advanced” on your desired search engine.

To learn the basics of writing an expression, see Building Expressions in Bullhorn Reporting (Previously Canvas) .