Oracle Analytics Cloud and Server

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

Multi select and All column values in Direct Database Request

Received Response
12
Views
3
Comments
2845107
2845107 Rank 4 - Community Specialist

Hi All,

I would like to pass multi select and all column values from prompt to direct database request report on the dashboard. I am able to pass variables and single select values.

Any one has any idea please share

Thanks

Suman.

Answers

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

    Single value syntax: '@{variable}{default value}'

    Multiple values syntax: {@{variable}['@']{'default value'}

    select ... from ... where
    column in {@{variable}['@']{''} /*Some values selected*/
    or '@{variable}' is null /*All column values*/
    or '@{variable}{na}'='na' /*Nothing selected*/
  • 2845107
    2845107 Rank 4 - Community Specialist

    Hi Andrew,

    Thanks for the reply.

    Now I am able to pass single and multiple values but not all column values. Below is my code

    AND st.name in (@{Site_DBR}['@']{'SSP10'}) or (@{Site_DBR}['@']{'SSP10'}) is null

    And when I remove the or part the code works for multi select.

    Any suggestions.

    Thanks

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

    Your code:

    AND st.name in (@{Site_DBR}['@']{'SSP10'}) /*something selected*/
    or (@{Site_DBR}['@']{'SSP10'}) is null /*always false*/
    

    should be:

    AND st.name in (@{Site_DBR}['@']{'SSP10'}) /*something selected*/
    or '@{Site_DBR}' is null /*All column values*/
    or '@{Site_DBR}{SSP10}'='SSP10' /*nothing selected, optional*/