Data Model Parameters

User_F1ZIP

Summary

Data Model Parameters

Content

I am building a data model for an RTF letter using ‘Oracle BI EE’ as the Data Source. The data model requires
parameters but when I try to add the SQL code, I get no results. Are there any
examples of how to write the SQL for parameters when using Oracle BI EE as the data source?

I have attached a simplified version of the report with the SQL that I have tried for the parameters. I do not get any error messages but no data is returned.

 

Code Snippet

select     "Worker"."Employee Name" as "Employee Name",
     "Worker"."Employee Email Address" as "Employee Email Address",
     "Worker"."Person Number" as "Person Number",
     "Worker"."Assignment ID" as "Assignment ID",
     "Worker"."Assignment Name" as "Assignment Name",
     "Worker"."Assignment Number" as "Assignment Number",
     "Worker"."Assignment End Date" as "Assignment End Date",
     "Worker"."Assignment Start Date" as "Assignment Start Date",
     "Worker"."Assignment Status" as "Assignment Status",
     "Worker"."Bargaining Unit" as "Bargaining Unit" 
 from   "Workforce Management - Worker Assignment Real Time"."Worker" "Worker"
where "Worker"."Bargaining Unit" = :p_union

Julian Challenger-Oracle

Moved to OBIEE forum.

Nathan CCC

Yes it should work using variables in the where clause in the logical sql in your data model as the parameters in the data model as you have done. See image below.

Did you get any rows when you hard code the value where "Worker"."Bargaining Unit" = 'All' in the SQL?

An alternative option is to add a data set of type "Oracle BI Analysis", link your data model to an analysis in the catalog, NOT use parameters in the data model, put the "report" that uses this "data model" on a dashboard, then use a "dashboard prompt", so the user goes to a dashboard page, selects from dashboard prompts, and if the analysis has a filter on that column as "is prompted", the analysis will run with a where clause, then send only the filtered data to the data model, for the report. Limitation of this option is that you will not be able to run this report from Tools/"Scheduled Processes". 

Nathan CCC

Just twigged. You are using a list of values. On your parameter you select option to pass "Can select all" True with option "NULL value passed" not "All values Passed". If so then you need to change your where clause to handle what to do if the parameter is null (All). Condition x = null is always false so will return no data. Here is a solution using the IN clause because i have allowed multiple selection and select all option set to "All values passed" (rather than NULL value passed).

User_F1ZIP

Thank you -  I tried this with bargaining unit and it worked perfectly.

However when I tried it with assignment number, I got these results