Oracle BIP parameter - Missing Right Parenthesis error

Question

I'm working on a report using Oracle BIP and part of the report are the prompts that allow multiple selection. Every time I select more than one option in the prompt it always give out a Missing Right Parenthesis error.

For context, the values in the prompt dropdown are the lookup table description/meaning and once selected they are used to filter the table using the corresponding code from the lookup table. See snippet below of the block that's causing the error. The line that is causing the error from this block is the line that has "meaning LIKE"

AND AR.status = Decode(:penstatus, 'ALL', AR.status,
                                      NULL, AR.status,
                                      ((SELECT lookup_code
                                        FROM   fnd_lookup_values
                                        WHERE
                       lookup_type = 'ORA_WLF_ASSIGN_RECORD_STATUS'
                       AND LANGUAGE = Userenv('LANG')
                       AND enabled_flag = 'Y'
                       AND Trunc(SYSDATE) BETWEEN
                           start_date_active AND end_date_active
                       AND meaning LIKE :penstatus)))

I tried switching it to an IN but for some reason the it's causing the report to not return data like the value in the prompt is not matching to anything. Can anyone share what's the possible cause of this and the solution to resolve this? Thank you!

Answer 1

If you need multiple selection parameters then the simplest option would be to use IN() for condition in Where clause of your DataModel's sql. Lets say that you are selecting all rows from a table that has ID listed/selected in your parameter of type Menu. You create a dataset of your DataModel with:

Select *
From
        (  Select 1 "SOME_ID", 'A name' "SOME_NAME", 10 "SOME_VALUE" From Dual Union All
           Select 2, 'B name', 20 From Dual Union All
           Select 3, 'C name', 30 From Dual
        )
Where SOME_ID IN(:Param_ID) 

Now you need to define parameter named Param_ID. Since it is a multi value parameter you create List of Values for it either as Fixed list of label-value pairs or as a resulting dataset of labels and values derived from an sql Select command like here:

Select Distinct SOME_OTHER_NAME, SOME_OTHER_ID
From    (  Select 1 "SOME_OTHER_ID", 'An alternative name for A' "SOME_OTHER_NAME" From Dual Union All
                 Select 2, 'An alternative name for B' From Dual Union All
                   Select 3, 'An alternative name for C' From Dual 
             )

Next you create the parameter based on List of values from above...

For this to work you need to mark the "All Values Passed" option under "Can select all" check box. The result could be tested now on tab Data of your DataSet. If we leave the paramater to have "All" as selection and just click on View button we wwill get all three records from DataSet and there will be the content of parameter list selected --> PARAM_ID ([3,2,1]):

If we change selection from "All" to just first and last label from the list then first and last row will be selected and the parameter will change to --> PARAM_ID([3,1]):