Passing Multiple values in presentation variable is not working

Saro

Hi friends,

Im in obiee 11.1.1.7.150120. And im applying below sql query in my sql results of my column prompt

SELECT "Fact1-payroll"."Employee Number" FROM "8 - HR-Payroll" 
where "Fact1-payroll"."Period Name" = '@{v_from_payroll_period}{10 2015 Calendar Month}' 
and EVALUATE('to_char(%1,%2)' as char, "Fact1-payroll"."Pyroll Date", 'DD-MON-YYYY')||'('||EVALUATE('to_char(%1)' as char, "Fact1-payroll"."Request Id")||')' IN (@{v_from_payroll_run}['@'])  
and "Fact1-payroll"."Employee Number" not in( SELECT "Fact1-payroll"."Employee Number" FROM "8 - HR-Payroll" where "Fact1-payroll"."To Period Name" = '@{v_to_payroll_period}{11 2015 Calendar Month}' and EVALUATE('to_char(%1,%2)' as char, "Fact1-payroll"."Pyroll Date", 'DD-MON-YYYY')||'('||EVALUATE('to_char(%1)' as char, "Fact1-payroll"."To Request Id")||')' IN (@{v_to_payroll_run}['@']) 

If u can see in the above query im passing multiple values for my evaluate expression with this syntax (@{v_from_payroll_run}['@']), (@{v_to_payroll_run}['@']) for my two columns, but the multiple values are not passing. But i can pass the single value for my evaluate expression with this normal syntax '@{v_to_payroll_run}'

What could be the issue with the multiple value presentation variable syntax. Do i miss something. Kindly advice.

Thanks in advance.

Regards,

Saro

Manoj Dixit

Hi,

I assume that the issue is when more than one date value is passed, it only accepts a single one.

I have tried to simulate the issue though it's based on a simple scenario. I have a column prompt that can accept one or more than one date value. I have an analysis that based on the value(s) input displays data for those days.

The filter is an Advanced SQL Filter having where clause

"Dates"."Day" in (@{p_days}{CURRENT_DATE})

Andrew Fomin.

What error did you get? Passing multiple values to presentation variable function itself works. There should be some error message that explains the problem.

Saro

Hi Manoj/Andrew,

Thanks for your replies. As my multi select presentation prompt is working fine with the below query

SELECT "Fact1-payroll"."Employee Number" FROM "8 - HR-Payroll"  where EVALUATE('to_char(%1,%2)' as char, "Fact1-payroll"."Pyroll Date", 'DD-MON-YYYY')||'('||EVALUATE('to_char(%1)' as char, "Fact1-payroll"."Request Id")||')' IN (@{v_from_payroll_run}['@']) and "Fact1-payroll"."Employee Number" not in( SELECT "Fact1-payroll"."Employee Number" FROM "8 - HR-Payroll" where EVALUATE('to_char(%1,%2)' as char, "Fact1-payroll"."Pyroll Date", 'DD-MON-YYYY')||'('||EVALUATE('to_char(%1)' as char, "Fact1-payroll"."To Request Id")||')' IN (@{v_to_payroll_run}['@']))

The problem is with the "All column values" in the prompt. If i select "All Column Values" in the prompt it is not working(indeed it selects all the run's in the prompt after selecting "All column values" but the results are not appearing). Instead if i select all the payroll run's manually in choice list without selecting "All Column Values", then it is working. But it is not working if i select "All column values" in the prompt.

What could be the issue with this in the selecting the "All column Values" to the presentation variable which accepts the multple values.

Thanks,

Regards,

Saro

Andrew Fomin.

Take a look at this topic: how to pass values to a presentation variable including All Column Values

Saro

Hi Andrew,

Thanks for the quick response. From that referred link i can see like you have passed this syntax extra {'na'} for handling the "All column values" in the prompt, so you mean to include that to my syntax like below for the "All column values" to work.

SELECT "Fact1-payroll"."Employee Number" FROM "8 - HR-Payroll"  where EVALUATE('to_char(%1,%2)' as char, "Fact1-payroll"."Pyroll Date", 'DD-MON-YYYY')||'('||EVALUATE('to_char(%1)' as char, "Fact1-payroll"."Request Id")||')' IN (@{v_from_payroll_run}['@']{'na'}) and "Fact1-payroll"."Employee Number" not in( SELECT "Fact1-payroll"."Employee Number" FROM "8 - HR-Payroll" where EVALUATE('to_char(%1,%2)' as char, "Fact1-payroll"."Pyroll Date", 'DD-MON-YYYY')||'('||EVALUATE('to_char(%1)' as char, "Fact1-payroll"."To Request Id")||')' IN (@{v_to_payroll_run}['@']{'na'}))

Kindly advice.

Thanks,

Regards,

Saro

Andrew Fomin.

Right.

If there is no default value for a variable, '"Products"."LOB" in (@{p_lob}['@'])' becomes SQL like '"Products"."LOB" in () when 'All column values' was selected, which is not well-formed SQL. 'na' prevents it and makes statement correct - '"Products"."LOB" in ('na')'. This statement is always false while I want it to be true, so I complement it with "or '@{p_lob}' = ''" which is translated into ''=''.

So, for any 'normal' values I get '"Products"."LOB" in ('Product1','Product2') or 'Product1,Product2'='''. And for All column values I get  '"Products"."LOB" in ('na') or ''='''

Saro

Thanks Andrew for all your help.

Regards,

Saro