Custom fields represent data unique to your organization. The specific setup, amount, or possible values you use for custom fields may differ greatly from another organization. Because of this unpredictability, we created Business Intelligence Connector to be flexible enough to support any custom field you create.
You can query a specific custom field using the custom field column in the associated table.
How custom fields work on Business Intelligence Connector
Imagine your organization has created two custom fields to store information not offered by default in Greenhouse Recruiting: Employment Type and Salary Range.
When viewing a table, you may expect Employment Type and Salary Range to display as separate rows on the jobs_custom_fields table. However, Business Intelligence Connector abstracts custom fields into two columns: one, labeled Custom field returns the specific field like Employment Type, and the second column called Display value returns its values, like Contract. By joining these two columns, you can query unique values to best fit your 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
To report on custom fields in SQL, 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, creating one column per custom field.
Tableau
To report on custom fields in Tableau, use 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.