General OBIEE

Get Involved. Join the Conversation.

Topic

    May Smith
    Data Model Parameters
    Topic posted November 1, 2019 by May SmithRed Ribbon: 250+ Points 
    32 Views, 4 Comments
    Title:
    Data Model Parameters
    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.

     

    Image:
    Code Snippet:

    Comment

     

    • Julian Challenger

      Moved to OBIEE forum.

    • nathan morgan (at Client)

      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 morgan (at Client)

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

    • May Smith

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

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