Custom fields are unique to your organization - the specific setup, amount, or possible values may differ greatly between Greenhouse customers. Because of this unpredictability, we've specifically created the Business Intelligence Connector to be flexible enough to support any custom field you might create, while still giving you full access to your data.
To maximize your custom fields through Business Intelligence Connector, you will need to call the specific custom fields from the custom field row in the respective tables.
An example
For this example, imagine your organization has created two custom fields to store values not offered by default in Greenhouse Recruiting: Employment Type and Salary Range.
When viewing a standard table, you may expect Employment Type and Salary Range to display as separate rows on the jobs_custom_fields table.
However, Greenhouse abstracts these values into two dimensions: one, labeled Custom Field that allows you to select the specific value. A second dimension called Display Value allows you to return those values. By joining these two dimensions, you're able to query unique values to best fit your data reporting needs.
Note: If you want to report on multiple custom fields at once, you will need to separate distinct custom fields from each other, which you can then use in your analysis.
SQL
In SQL, you can break the custom fields table into multiple tables, one per distinct custom field. Then, you can use these temporary tables in your SQL statement.
For example, you can create a table representing Employment Type via the following SQL query:
SELECT job_id, display_value as "Employment Type"
FROM job_custom_fields
WHERE custom_field = "Employment Type"
Using this pattern, you can create individual tables for each custom field, and then include them in your FROM statement to join the tables together and create 1 column per custom field.
Tableau
In Tableau, you can accomplish this by using the following formula structure that combines a Level of Detail Expression with a CASE expression.
{ FIXED [ID# of the custom field object] :
max( CASE [Custom Field]
when "name of custom field"
then [Display Value]
end)
}
After you've created the custom field, it will appear in your list of Dimensions, and you can add it to your report.
