Oracle Transactional Business Intelligence

Products Banner

Turnover by Ethnicity - Report Assistance



We are currently running into issues creating a Turnover report by Ethnicity due to how ethnicity is handled in Oracle. This is causing employees to be duplicated if they have selected more than one race.

I've included the code snippet below which was suggested in the community (When person count is greater than 1 then 'Two or more races'), but the report is not providing the desired results. The report ends up duplicating the same values multiple times (see screenshot below). I've attempted to set up a pivot table so that it would only provide one value and remove all the duplicates, but had no luck. It seems as though you need to include the Person Number on the report in order for the formula to work and display them as 'two or more races', but in doing so it creates the duplicate lines of data. So I'm not able to find a solution to stop this from occurring.

I have included the XML file below:

Any help/direction would be appreciated. Thank you, Andrew

Version (include the version you are using, if applicable):

Oracle Fusion Cloud Applications 23A (

Code Snippet (add any code snippets that support your topic, if applicable):

CASE WHEN COUNT(DISTINCT "Worker"."Employee Ethnicity" by "Organization Hierarchy"."Level 03 Organization Name", "Worker"."Person Number") > 1 THEN 'Two or More Races' ELSE "Worker"."Employee Ethnicity" END