'Ethnicity - All' Headcount for US legislation to include 'Two or more Races'

Question

Based on the current functionality the only way to report on employee headcount by ethnicity is to use 'Ethnicity' which selects a primary ethnicity for each employee (Cannot use in the US because employee's do not select a primary ethnicity) or to use 'Ethnicity - All' which will inflate headcount numbers by counting employees in each ethnicity they select.  As a result, we are attempting to create a custom Ethnicity field to capture employees that select more than one ethnicity as 'Two or more Races'.

I'm using the HCM - Workforce Core SA and have tried a few variations of the formula below and the value shows correctly in a report with Person number but when I attempt to aggregate by other dimensions for Employee Headcount totals, like Business Unit, it does not aggregate correctly.  Any suggestions for how to do this effectively in FDI?

CASE WHEN COUNT(DISTINCT "HCM - Workforce Core"."Ethnicity"."Ethnicity - All" BY "HCM - Workforce Core"."Basic Information"."Person Number")>1 then 'Two or more Races' else "HCM - Workforce Core"."Ethnicity"."Ethnicity - All" end

Chelsea H. · Answer

@Caroline Blanchard thanks for your response - We are exploring this as an option as well as a semantic extension.  I upvoted your idea and will comment we (and I believe many other US based companies) could really benefit from it being standard configuration provided by Oracle.

Caroline Blanchard · Answer

Hi there! We encountered this same issue in the past. Here's a forum post I created about it. We created custom dataset then joined it to Workforce core on person number. This is the query we used: SELECT "HCM - Workforce Core"."Basic Information"."Person Number",

CASE

WHEN COUNT(DISTINCT "HCM - Workforce Core"."Ethnicity"."Ethnicity - All") > 1 AND "HCM - Workforce Core"."Basic Information"."Person Number" IN (SELECT "HCM - Workforce Core"."Basic Information"."Person Number" FROM "HCM - Workforce Core" WHERE "HCM - Workforce Core"."Ethnicity"."Ethnicity - All" = 'I am Hispanic or Latino.') THEN 'I am Hispanic or Latino.'

WHEN COUNT(DISTINCT "HCM - Workforce Core"."Ethnicity"."Ethnicity - All") > 1 THEN 'Two or more races'

WHEN "HCM - Workforce Core"."Ethnicity"."Ethnicity - All" IS NULL THEN 'Not disclosed'

ELSE "HCM - Workforce Core"."Ethnicity"."Ethnicity - All" END AS Ethnicity_Custom,

CASE

WHEN COUNT(DISTINCT "HCM - Workforce Core"."Ethnicity"."Ethnicity - All") > 1 AND "HCM - Workforce Core"."Basic Information"."Person Number" IN (SELECT "HCM - Workforce Core"."Basic Information"."Person Number" FROM "HCM - Workforce Core" WHERE "HCM - Workforce Core"."Ethnicity"."Ethnicity - All" = 'I am Hispanic or Latino.') THEN 'Minority'

WHEN COUNT(DISTINCT "HCM - Workforce Core"."Ethnicity"."Ethnicity - All") > 1 THEN 'Minority'

WHEN "HCM - Workforce Core"."Ethnicity"."Ethnicity - All" = 'White' THEN 'Non-Minority'

WHEN "HCM - Workforce Core"."Ethnicity"."Ethnicity - All" IS NULL THEN 'Not disclosed'

ELSE 'Minority' END AS Ethnicity_Type

FROM "HCM - Workforce Core

Let me know if this works for you, or if you have any questions!! Do be aware that this tends to cause the load times for your canvases to be a bit longer. We follow the IPEDS reporting requirements, so you'll have to tweak the query a bit for your reporting needs.

John Hung-Oracle · Answer

Ah.  Your question is FDI related.  This forum is actually for OTBI.  Let me move this to the FDI forum.  Thanks.