Passing Multiple values in presentation variable is not working — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Passing Multiple values in presentation variable is not working

Received Response
31
Views
7
Comments
Saro
Saro Rank 5 - Community Champion
edited Aug 13, 2024 6:17PM in Oracle Analytics Cloud and Server

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

Answers

  • Manoj Dixit
    Manoj Dixit Rank 6 - Analytics Lead

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

    pastedImage_0.png

  • Andrew Fomin.
    Andrew Fomin. Rank 6 - Analytics Lead

    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
    Saro Rank 5 - Community Champion

    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.
    Andrew Fomin. Rank 6 - Analytics Lead

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

  • Saro
    Saro Rank 5 - Community Champion

    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.
    Andrew Fomin. Rank 6 - Analytics Lead

    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
    Saro Rank 5 - Community Champion

    Thanks Andrew for all your help.

    Regards,

    Saro