Building Expressions in Bullhorn Reporting (Previously Canvas)

Often you will need to create a more robust filter for your report. This article will walk you through an exercise to build a simple placement duration calculation based off a placement’s scheduled end and start dates.

Working with Expressions

  1. Build a List Report using the steps in Building and Customizing a List Report in Bullhorn Reporting (Previously Canvas) and then choose Insertable objects > Source > Placement Analysis > Placement.
  2. Drag and drop the following fields into your table:
    • Placement ID
    • Date Added
    • Scheduled End
  3. From the same Insertable objects > click Placement Job > Job Title.
    • Arrange the columns so they make sense to you. Keep in mind that our calculation will be done on the Scheduled End column.
  4. To access Query Explorer select More > Go To Query.
  5. Click Insertable objects > Source > Placement Job > double-click Start Date so that it shows up under Data Items.
  6. Click Report. Under Pages, click Page1.
  7. Double click the Scheduled End data rows.
  8. In the Data item expression - Scheduled End pop-up, the Expression Definition will be auto-populated with the scheduled dateEnd information. We want to edit this as a calculation of how much time will have elapsed on a placement from start to end by.
  9. Clicking Functions > Business Date/Time Functions.

  10. Place your cursor at the beginning of the existing expression in the Expression Definition box, and then double-click the _days_between function
    1. You’ll notice that it automatically opens a parenthesis before the previous contents, allowing us to add our 2nd variable.
  11. Place your mouse cursor at the end of the expression and type in ",".
  12. Click the Source tab > Placement Analysis > Placement Job > double-click Start Date then type a ")" at the end, to close the expression.
    1. At this point our expression is complete and it should look like this:_days_between ([PRESENTATION VIEW - PLACEMENT ANALYSIS].[PLACEMENT].[dateEnd],[PRESENTATION VIEW - PLACEMENT ANALYSIS].[PLACEMENT JOB].[startDate])
  13. Select Validate to validate the expression.
  14. Correct any errors that show up.
  15. Click OK.

You now have a calculation of how long a placement will last based on its start and end dates.

You can learn more by clicking on functions and reading up on the descriptions provided in Information. You can also research more advanced techniques by searching for “Cognos Workspace Advanced” on your search engine of choice.