How are custom fields stored in Greenhouse Analytics?

Custom fields are stored differently in GHA than other dimensions, given that they differ across Greenhouse customers and we cannot predict what custom fields you will create.

For example, assume that there are two job custom fields: Employment Type and Salary Range. Rather than seeing two dimensions called [Employment Type] and [Salary Range] in the job_custom_fields table, you will see one dimension called [Custom Field] that will allow you to select from "Employment Type" or "Salary Range", and another dimension called [Display Value] that will return the values of the dimension you've selected.

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. 

See below for two examples using SQL and Tableau.

Example 1: In SQL, you can accomplish this by breaking the custom fields table apart 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.

 

Example 2: In Tableau, you can accomplish this by using the following formula structure that combines a Level of Detail Expression with a CASE expression (see the screenshot below for an example).

{ 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.

Screen_Shot_2017-05-01_at_4.00.29_PM.png

Have more questions? Submit a request

Comments

Powered by Zendesk