General OBIEE

Get Involved. Join the Conversation.

Topic

    Giuseppe Botta
    Data model with parameter with "Select all"Answered
    Topic posted August 28, 2017 by Giuseppe BottaSilver Medal: 2,000+ Points, tagged Analyses, BI Publisher, BI Publisher Data Models, BI Publisher Report Templates, OTBI 
    722 Views, 10 Comments
    Title:
    Data model with parameter with "Select all"
    Summary:
    Data model with parameter with "Select all"
    Content:

    Hi,

    we created a data model based on several analisys and we want to add a parameter which filters by Employee Name, with the possibility to filter both for single employee name and all employee names.

    How do we do that? Which is the where clause we have to put in?

     

    Thank you,

     

    Dario

     

    Best Comment

    Arun Raj

    Hi Dario,

    Lets say that you have the below SELECT statement.

    SELECT * from EMPLOYEE

    where emp_num = :PARAM

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Choose the option "pass null value" and modify the SELECT statement to :

    SELECT * from EMPLOYEE

    where emp_num = NVL(:PARAM, emp_num);

    This will only work if the user is allowed to select 1 Value or All Values. It will not work if you are using "IN". It will not work if the user has option to select multiple values from the List of Values. 

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Thanks,

    Arun

    Comment

     

    • Vikram Agarwal

      Hi Dario,

      Its a 3 step process. 

      Step 1: In data Model under List of Values create a new "List of Value" say XXLOV with type as SQL Query and Data Source as  ApplicationDB_HCM. Then in SQL Query write the query which will fetch all your employee name (For Ex: SELECT EMPLOYEE_NAME FROM TABLE_NAME)

      Step 2: In data Model under Parameters create a new Parameter XXPARAM with parameter type as Menu and List of values as "XXLOV" created in STEP 1

      Step 3: In the actual query add a condition employee_name = : XXPARAM (created in STEP 2)

      Let me know if this helps.

      Regards,

      Vikram

      • Dario Di Franceschi

        Hi Vikram and thanks for the reply,

        I already follow the steps you mentioned, the fact is that when I create a new parameter (Menu as parameter type) I need to select in the "Option" section "Select all" and one of the options between "pass all values" or "pass null value".

        If I select the first one, if in the report I select "Select all" in the filter (with condition like employee_name IN (:PARAM)), the parameter receives a limited number of values (default is 100 and max number 999 i think) and I need to pass all values which are way more.

        If I select the second one I need to make a condition like (employee_name IN (:PARAM) or :PARAM IS NULL)) which seems to have sense but it doesn't work.

         

        Any idea on this?

         

        Thank you,

         

        Dario

        • Arun Raj

          Hi Dario,

          Lets say that you have the below SELECT statement.

          SELECT * from EMPLOYEE

          where emp_num = :PARAM

          -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

          Choose the option "pass null value" and modify the SELECT statement to :

          SELECT * from EMPLOYEE

          where emp_num = NVL(:PARAM, emp_num);

          This will only work if the user is allowed to select 1 Value or All Values. It will not work if you are using "IN". It will not work if the user has option to select multiple values from the List of Values. 

          ------------------------------------------------------------------------------------------------------------------------------------------------------------------------

          Thanks,

          Arun

    • Arun Raj

      Hi Dario,

      Is this a BI Report? or a OTBI Analysis?

      Thanks,

      Arun

    • Giuseppe Botta

      Thanks to all for all reply. Are there any other suggestion? Thanks

    • Arun Raj

      Hey Dario,

      Try this out. I didnt get a chance to try it. But let me know if this works.

      1. Create an additional Parameter. I am calling it :DRIVER_PARAM. This will have 3 values associated with it "ALL", "MULTIPLE","SINGLE".

      2. For the :PARAM value set, dont select the option "Select all"

      3. Parameter Selection

      • The user will first select a value from DRIVER_PARAM value set. If he/she wants all the values to to be passed, he/she will select "ALL" and the submit the report.
      • If the user wants to select a single value, he/she will select SINGLE from the list of values and then select the particular value from the :PARAM Value Set and submit it
      • If the user wants to select multple values, he/she will select MULTIPLE from the first list of values and then select multiple values from the 2nd value set and submit the report.

      4. I modified the query as below

      SELECT * from EMPLOYEE

      where DECODE(:DRIVER_PARAM,'ALL',1,2) = 1

      AND emp_num IN (SELECT emp_num from employee)

      UNION

      where DECODE(:DRIVER_PARAM,'ALL',1,2) = 2

      AND emp_num IN (:PARAM)

       

      Thanks,

      Arun

    • Nirmal

      Hi Dario, 

      We will following the below apparoach to select ALL values in the LOV. This will help you to handle the ALL paramerter values.

      Step 1:
      You have to modify the where clause in the dataset query as below,

      WHERE 
      (
      "Administration"."Employers"."Employer Name" IN (:EMP_NAME)
      OR
      'ALL' IN (:EMP_NAME)
      )

      :EMP_NAME is my parameter name. OR condition will check the ALL static value.

      Step 2:
      Write a similar union query in the "List of Values" section,
      Example:
      SELECT 'ALL' s_1 FROM "Administration"
      UNION
      SELECT "Administration"."Employers"."Employer Name" s_1 FROM "Administration"

      Step 3:
      Setup the parameter options as shown in the attached screenshot.

      Thanks

      Nirmal

      www.dataterrain.com