OBIEE (Oracle Business Intelligence Enterprise Edition) is a powerful business intelligence platform that allows users to perform a wide range of data analysis and reporting tasks. We are going to focus on the OBI Reporting that is delivered with Taleo Recruiting. The examples will be specific to common use cases on that platform and data set but could be used interchangeably with other OBIEE implementations since the syntax is similar or the same.
Here are some commonly used and useful OBIEE formulas in Taleo Recruiting.
Create an OBIEE formula to find a semi-colon and then present only the data in front of the semi-colon in the displayed data.
This use case in Taleo or any other system came up when a user-defined field (UDF) was created to collect multiple answers in a single field. In this case, it was on the candidate’s record and stored multiple candidate types (categorizations). In OBI it separates the multiple answers by a semicolon. This same logic could be used with other characters separated like commas or pipes or anything that is separating the data.
Value 1 (in front of the separator)
case when SUBSTRING(“Candidate Flex Fields”.”UDF1″ from 1 for LOCATE(‘;’, “Candidate Flex Fields”.”UDF1″)-1) is NULL THEN “Candidate Flex Fields”.”UDF1″ ELSE SUBSTRING(“Candidate Flex Fields”.”UDF1″ from 1 for LOCATE(‘;’, “Candidate Flex Fields”.”UDF1″)-1) end
Value 2 (behind of the separator)
case when SUBSTRING(“Candidate Flex Fields”.”UDF1″ from 1 for LOCATE(‘;’, “Candidate Flex Fields”.”UDF1″)-1) is not NULL THEN SUBSTRING(“Candidate Flex Fields”.”UDF1″ from LOCATE(‘;’,”Candidate Flex Fields”.”UDF1″)+1 for LENGTH(“Candidate Flex Fields”.”UDF1″)) ELSE null end
Evaluate the employer name in two fields and bring back only the one that is populated.
This is definitely a Taleo oddity as it will store employer name in the “regular” employer name field if it is populated through the predefined picklist and in the “other” field if it is entered manually and does not have a match in the pick list.
CASE WHEN “Submission Work Experience History”.”Submission Experience Employer Name (Other)” IS NULL THEN “Submission Work Experience History”.”Submission Experience Employer Name” ELSE “Submission Work Experience History”.”Submission Experience Employer Name (Other)” END
Experience Job Title
CASE WHEN “Submission Work Experience History”.”Submission Experience Job Title (Other)” IS NULL THEN “Submission Work Experience History”.”Submission Experience Job Title” ELSE “Submission Work Experience History”.”Submission Experience Job Title (Other)” END
Education Institution Name
CASE WHEN “Submission Education History”.”Submission Education Institution Name (Other)” IS NULL THEN “Submission Education History”.”Submission Education Institution Name” ELSE “Submission Education History”.”Submission Education Institution Name (Other)” END
Education Program Name
CASE WHEN “Submission Education History”.”Submission Education Program Name (Other)” IS NULL THEN “Submission Education History”.”Submission Education Program Name” ELSE “Submission Education History”.”Submission Education Program Name (Other)” END
OBIEE formula to concatenate two fields together with a space separating them
This example will concatenate the Template Title and Code for display on a requisition report similar to how you see it as a user in Taleo. The pipes (“||”) are placed between each data element. This can be used with any field or text within OBIEE.
“Template Identification”.”Template Job Code”||’ ‘||”Template Identification”.”Template Title (ML)”
These are just a few examples of the many OBIEE formulas that can be used to create calculated fields. Depending on your specific data analysis needs, there may be other functions and formulas that are more appropriate.