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

Fetch multiple nationalities/citizenships in a single row on OTBI

Received Response
45
Views
5
Comments
Vibha Pandey - Oracle
Vibha Pandey - Oracle Rank 3 - Community Apprentice
in Oracle Fusion HCM Analytics

I have a requirement where I need all the nationalities of a person in a single row. Ideally when we fetch the citizenship we get multiple rows. We have tried RCOUNT and PIVOTTING Results but no luck. Has anyone achieved this already?

image.png image.png
Tagged:

Answers

  • JohnW-Oracle
    JohnW-Oracle Rank 7 - Analytics & AI Coach

    Hi @Vibha Pandey - Oracle ,

    I have had a similar question in the past, but the problem was that all Nationalities were displayed but only the current one was desired. This is explained in a little more detail in Doc ID 3021065.1.

    Diversity Analysis includes 'Country Of Citizenship' and it will show all citizenships (nationalities) by default. If you want to filter out expired citizenships, you can use the filter: Citizenship Status Code = E (expired) (CITIZENSHIP_STATUS).

    Hope that helps.

    Regards,

    John

  • Vibha Pandey - Oracle
    Vibha Pandey - Oracle Rank 3 - Community Apprentice

    Thanks John!

    My client has a population where dual citizenship is common. I need both of them on the report and in one single row. Something like Nationality1, Nationality2.

  • Madhura Saurabh Mirikar
    Madhura Saurabh Mirikar Rank 1 - Community Starter

    Hi @Vibha Pandey - Oracle

    Did you manage to get the multiple nationalities in a single row? I am having a similar use case and not able to achieve the desired output.

  • Bhaskar Konar
    Bhaskar Konar Rank 8 - Analytics & AI Strategist
    https://community.oracle.com/products/oracleanalytics/discussion/comment/72558#Comment_72558

    Hi @Madhura Saurabh Mirikar,

    Welcome to the Oracle Analytics Community!

    As it is an year old post (and if you are not able to receive any answer), I would recommend you to raise a new question with all relevant details like issue details, screenshot (without sensitive data) etc. so that someone can able to help you.

    Thank you.

  • RVohra
    RVohra Rank 7 - Analytics & AI Coach

    I agree it is better to start a new discussion instead of updating a year old post.

    In any case regarding your question, OTBI cannot natively return multiple citizenships/nationalities in a single row for a person. But you can achieve it using BI Publisher (LISTAGG), a Data Model, or a custom SQL-based subject area, because OTBI does not support dynamic row-to-column pivoting or string aggregation.

    Hope it helps!