Oracle Analytics and AI

OBIEE Data Model Parameters

I have transitioned an analysis to a data model using the OBIEE connection. It's been requested to add parameters to the data model and report. I'm used to building them in any other connections, but the OBIEE connection is a little different. I'm running into an issue where the parameter is accepted, and I tried setting it to all values passed but now when the report runs its stopping at 600 rows when it should contain over 24,000 when all values are passed in the parameter selection. There's two parameters and the lines are:

AND "Employee"."Employee Name" in (:P_Name)
AND "Labor Schedules Details"."Status" in (:P_Status)

Any help in resolving this issue is greatly appreciated!

Thank you,

Sean

Find more posts tagged with

OBIEE

Parameter

Data Models

Accepted answers

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/64485#Comment_64485

Hi @Sean-B-01,

Can you please try the your conditions like below one?

AND

(

"Employee"."Employee Name" IN (:P_Name) 

OR (CASE WHEN ('null') in (:P_Name) THEN 1 END =1)

)


AND

(

"Labor Schedules Details"."Status" IN (:P_Status) 

OR (CASE WHEN ('null') in (:P_Status) THEN 1 END =1)

)

I tried in my instance and it is working for me.

Hope this help.

Thank You!

All comments

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/24757/obiee-data-model-parameters

Hi @Sean-B-01,

Quick Question:

How the prompts been defined: Can you please share screenshots?

Is it 'NULL Value Passed' or 'All Values Passed' under 'Can Select All'?

If possible attach your report catalog so that we can replicate the issue in our instance which will help to find any potential solution.

Thank You!

Sean-B-01

Hi @Bhaskar Konar, currently they are set as All Values Passed and I changed the number of values to display in List to 999 but still no luck. I would like for it to be NULL value passed so that the parameter isnt required and all values can be returned. I'll post screenshots and attach the catalog items below this message.

Thank you,

Sean

https://community.oracle.com/products/oracleanalytics/discussion/comment/64470#Comment_64470

Hi Sean,

Can you please choose NULL values passed instead of All values passed?

And modify your conditions like below:

AND ( "Employee"."Employee Name" in (:P_Name) OR (LEAST(:P_Name) IS NULL))

AND ("Labor Schedules Details"."Status" in (:P_Status) OR (LEAST(:P_Status) IS NULL))

Thanks.

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/64469#Comment_64469

Hi @Sean-B-01,

Please use the following code instead of the existing 'AND'

AND

(

"Employee"."Employee Name" IN (:P_Name)

OR LEAST(:P_Name) IS NULL

)


AND

(

"Labor Schedules Details"."Status" IN (:P_Status)

OR LEAST(:P_Status) IS NULL

)

Also make the Prompt like following:

Hope this help.

Thank You!

Sean-B-01

Hey @Bhaskar Konar

I tried applying the query you sent. LEAST is not a defined function in my environment, I attempted replacing it with MIN but now its not returning any rows.

Is there a way I can define the LEAST function or any other possible solutions you may have? Thank you for your help with this.

Thank you,

Sean

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/64476#Comment_64476

Hi @Sean-B-01,

Sorry to hear it has not work in your instance.

It works for us with Oracle Fusion HCM / ERP Cloud and BI Publisher using OTBI SAs as a data model.

Can you please try the following to see if it works?

AND

(

"Employee"."Employee Name" IN (:P_Name) 

OR 'All' IN (:P_Name || 'All')

)


AND

(

"Labor Schedules Details"."Status" IN (:P_Status) 

OR 'All' IN (:P_Status || 'All')

)

I was trying with your data model but I don't have few DFFs so I need exclude them. I'll check and shall try to modify the DM and test in my instances and shall keep you posted.

Thanks for your time.

Sean-B-01

Hey @Bhaskar Konar

I applied the changes you made and have the parameters set as NULL Value Passed still do data being returned. I tried it with All values passed as well and its still cutting off at 600 rows when it should be in the 20,000s when running for all.

Thank you,

Sean