Oracle Communities

Oracle Analytics and AI

Child Item

How to fetch the lookup codes and lookup Types created in Fusion HCM into OTBI report

Hello All,

we are using a OTBI analysis (Subject Area - Workforce Learning - Learning Records Real Time) to fetch the Learning Records for a few particular courses. so, we have hardcoded courses what we want to fetch. to avoid the hardcoding of the courses and offering we want to use custom lookup values (lookup_code) created in fusion HCM.

How to fetch these lookup values and lookup types in the OTBI report.

appreciate if some one can help me through this.

Find more posts tagged with

OTBI

OTBI Reports

OTBI (Oracle Transactional Business Intelligence)

Accepted answers

All comments

RVohra

I donot think you can directly select lookup values in OTBI from standard HCM subject areas.

Go to BI Publisher (BIP) → Create a Data Model with Query like below

SELECT LOOKUP_TYPE,
LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'XX_LEARNING_COURSES'
AND ENABLED_FLAG = 'Y';

Save it as a BIP Dataset (XML or CSV output).

In OTBI, create a data model join (via BI Publisher “combined data model” feature) or schedule this BIP extract and use it as a lookup table in a subject area filter.

Please update here if it works.

Hope it helps!

NARESHD

Really thanks for this,

But may i request you to help me understand this step you mentioned:

"In OTBI, create a data model join (via BI Publisher “combined data model” feature) or schedule this BIP extract and use it as a lookup table in a subject area filter."

a navigation to understand how to do this step..
thank you
Karthik

RVohra

Let me see when I can get some time on it. If you are in urgency, then you might need to still pursue with Oracle.

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/71797#Comment_71797

Hi @RVohra,

Can you please elaborate on the following and give some examples?

"In OTBI, create a data model join (via BI Publisher “combined data model” feature) or schedule this BIP extract and use it as a lookup table in a subject area filter."

If you can share some examples and steps for the following, it'll be very helpful:

How can we join Data Model in OTBI?
How can we use a BIP Extract in the OTBI Subject Area filter?

Thank you.

KarthikDamamkar

https://community.oracle.com/products/oracleanalytics/discussion/comment/71857#Comment_71857

Hello RVohra,

sure. FYI our OTBI analysis also has prompts. so request you when ever you give us an example, please include how to handle prompts too.

thanks to @Bhaskar Konar for precisely putting up the question which i really wanted to ask.

thank you

RVohra

Hello @Bhaskar Konar, I do not have the required permissions to work on it but see if you have access and try to create the dataset from the FND_LOOKUP_VALUES_VL. Refer the attached screenshot.

If above does not work, then you have to extract FND LOOKUP data and push it to custom object and then use it in the OTBI.

Hope it helps!

RVohra

Hi Karthik,

I do not have the required permissions to explore it, see if you can try it out in your env.

If it still does not work then need to explore the Custom Subject Area. Thanks

Abhisek Choudhury

Hi…Same problem for me also…unable to create prompt from custom fndlookup and join with expense subject area…