Data model with parameter with "Select all"

Giuseppe 1234

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

 

Vikram Agarwal-43618

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

rajArun

Hi Dario,

Is this a BI Report? or a OTBI Analysis?

Thanks,

Arun

Dario_DiFranceschi

Hi Arun,

it's a BI Report based on a datamodel.

 

Dario

Dario_DiFranceschi

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

rajArun

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

Giuseppe 1234

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

rajArun

Hi Dario,

Did you check my response on 08/29? Did that help?

Cheers,

Arun

Giuseppe 1234

I have checked, it's a good idea but we want a multiple choise and a select all option for the report. Any Idea?

rajArun

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-64675

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