Oracle Fusion HCM Analytics

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion Data Intelligence› Oracle Fusion HCM Analytics

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
99
Views
5
Comments
Chelsea H.
Chelsea H. Rank 2 - Community Beginner
edited Nov 6, 2024 4:33PM in Oracle Fusion HCM Analytics

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

Tagged:

Answers

  • John Hung-Oracle
    John Hung-Oracle Mod

    Hi, to get the appropriate HCM expert eyes on this, please post your question in the Reporting and Analytics for HCM forum. For some reason, I cannot move this post there. Thank you.

  • Chelsea H.
    Chelsea H. Rank 2 - Community Beginner

    Hi John - I did go ahead and post it there, but it seems like that might be for OTBI reports? Does this forum also support FDI customers?

    https://community.oracle.com/customerconnect/discussion/820968/ethnicity-all-headcount-for-us-legislation-to-include-two-or-more-races/p1?new=1

  • John Hung-Oracle
    John Hung-Oracle Mod

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

  • Caroline Blanchard
    Caroline Blanchard Rank 6 - Analytics Lead

    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.

  • Chelsea H.
    Chelsea H. Rank 2 - Community Beginner

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