Bullhorn Reporting (Previously Canvas) Best Practices

Canvas reports are a powerful tool that can help you keep tabs on your business. You may find that you need a more robust report than you initially realized, which may take more time to load based on its complexity. When creating a Canvas report, it’s important to consider everything you include as a tradeoff with runtime.

This article will help you to optimize your reports and make the best use of Canvas.

General Guidelines

  • If a data point is not necessary for your report, don’t include it. The more data points you have on a report, the longer the runtime will be.
  • Run your report for shorter time frames. If you need a years’ worth of data, consider running the report per quarter instead.
  • If you wish to export to Excel, the best option is to use the CSV Export option from the appropriate list view rather than a Canvas report.
    • Canvas can take a long time to export to Excel. We recommend you use Bullhorn list views to export to Excel and then ensure your Canvas reports only contain information you couldn't otherwise export via a list view. You can then use Excel to join the two exports based off a primary key such as ID.
  • If your report includes both counts and specific record data, consider using a Drill Through Definition report to view your counts on the main report and use child report(s) to hold specific record data. Runtimes will be much faster than waiting on a single, larger report.
  • ​Group or Filter by the IDs rather than Name. This will be faster becauseCanvas will lookup the results by the primary key. This will also prevent two unrelated persons with the same name from being incorrectly grouped together.

Queries

  • In general, the more queries, joins, and unions in your report, the longer it will take to run. Drill Through reporting would be a great option to avoid excess queries.
  • We have provided sub-packages under the main analysis packages, such as Candidate Placement within the Candidate Analysis Package, for your use in place of Query Joins. Using these whenever possible is a great way to speed up your report.
  • Ensure you clean up your Queries if you decide to delete any columns from your report.
  • If you do need to use query joins, always join on ID fields. Joining on Names can produce results were two person’s data are meshed together.
  • If your report uses query joins/unions and is calculating summaries (such as counts or totals), it is best to do calculate the summaries in the earliest query possible (before the joins / unions) instead of in later queries (after the joins / unions).
  • If your report uses query joins / unions, all data filters should be in the earliest queries possible (before the joins / unions) instead of in later queries (after the joins / unions), even if this means copying the same filters into several queries.
    • In general, the more filters you have on the early queries, the more efficient your report will be. The join / union is usually the most expensive operation in the report. Joins/unions are faster when there are fewer records being joined / unioned.

Filters

Avoid filtering on columns with a complex definition, such as case statements or functions. Instead, build your filter by referencing the data item or the primary key itself.

Copy

Example

[PRESENTATION VIEW - CANDIDATE ANALYSIS].[CANDIDATE].[dateAdded]

Applying Date Filters

  • Make sure your filter expression references the date fields directly. Date fields should never be used as function arguments in filter expressions.
  • Using a <= AND >= expression along with _add_days will ensure you receive results through the entire start and end dates in your range. This will also allow Canvas to use the SQL index to quickly find your results. 
    Copy

    Example 1

    [Data Added] <= current_date AND [Date Added] >= _add_days(current_date,1)
    Copy
    Example 2
    [Date Added] <= ?DateFrom? AND [Date Added] >= _add_days(?DateTo?,1)
  • Using CAST on your dates will cause your report to run slower since it will be looking at the database table contents row by row rather than using the more rapid index lookup.

Edit History

  • Whenever possible, use the Placement History package rather than the Placement Edit History package to help speed up your report.
  • Avoid filtering by Field From and Field To within Edit History packages, as these fields can hold large amounts of data, such as resume contents, and this will considerably slow down your report.