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

All Column Values in the column formula

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

Hi,

I am trying to pass All Column values to the column formula of the report and it sends null values for the variable and the value into the report .i am not able to check any condition using this variable. Is there any way to make this functionality work?

the other option i had was constrained prompt using sql to avoid all column values. Suppose i have Col A, Col B. Col B is constrained on Col A. When i change the values of Col A , the Col B has to change and they are changing but not all the values are selected by default. The previous selection values are only selected by default. is there any work around for this ?

Regards,

K

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    You can easily match the "All Column" value in a presentation variable (set by a prompt) in a formula.

    For example with something like this:

    CASE IFNULL('@{my_pres_variable}{##null_value##}', '##all_cols##')
WHEN '##null_value##' THEN 'nothing selected'
WHEN '##all_cols##' THEN 'all columns selected'
ELSE 'a value selected'
END

    Using the IFNULL function you can set a value in case the "All Column" is selected, and so you can then match this default value to make a condition.

  • 3193829
    3193829 Rank 5 - Community Champion

    Hi Gianni,

    Thanks for the reply.However, i still face some issue with the condition.

    This is my condition.

    case when region='@{reg}' then filter(paint.sales using ((category IN (@{pv}['@']{'TV'}) OR category IS NULL))

    when region='@{reg2}' then filter(paint.sales using ((category IN (@{pv}['@']{'TV'}) OR category IS NULL)) end

    with ifnull

    case when region='@{reg}' then (case ifnull('@{pv}{value}','check') when 'check' then category else '@{pv}[@]{}' end) end

    the above ifnull statement gives all the values of category in a single row if some values are selected in the prompt because of '@{pv}[@]{}' . I need to pass multiple values to my column. can you correct my formula please.

    Regards,

    K

  • 3193829
    3193829 Rank 5 - Community Champion

    Hi,

    I am able use the ifnull condition and display all column values. what would be condition to display only the selected values.

    case ifnull('@{pv}{A}','check') when 'check' then 'display all column values' end - displays all values

    can anyone help please.

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    Use the below statement for the Filter by Convert to SQL. U as Presentation variable name

    <COLUMN> LIKE (CASE WHEN '@{U}{}'='' THEN '%%' ELSE '@{U}' END)

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    Passing Prompt's 'All Column Values' or 'All Choices' to Presentation variable

    When we are using Presentation variables, Prompt functionality is very limited. One of those is using
    option 'All Column Values'.

    Here I'm trying to depict a Tip to make use of option 'All Column Values'.

    To demonstrate the same here I'm defining a Presentation variable as 'U' using Page or Dashboard Prompt

    image

    When I select 'All Column Values' we see the report with 'No Results' message as shown below since the criteria is not set properly

    image

    The Tip what I'm suggesting is to use required to convert the filter to SQL as shown below

    image

    Use the below case statement to so that the condition matches as shown in the image
    <COLUMN> LIKE (CASE WHEN '@{U}{}'='' THEN '%%' ELSE '@{U}' END)

    image

    On the Dashboard page, the prompt with option 'All Column Values' is selected and the report results are as below

    image

    The same works for 10g, the same option is as 'All Choices'

  • 3193829
    3193829 Rank 5 - Community Champion

    Hi Srini,

    I cannot use the presentation variable in the report level. I need to use them in the column formula only. Is there any way for this ?

    Regards,

    Kalpana

  • Neha_Naik004
    Neha_Naik004 Rank 2 - Community Beginner

    SriniVEERAVALLI: your sample images are not visible in this post. Could you please share the example with steps or attach the images again?

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi @Neha_Naik004 ,

    This thread is 9 years old and has been moved across 3 different forums migrations. That's why the pictures aren't visible (they have been lost somewhere).

    The user you mentioned is not active in this forum.

    If you have a question about this topic, post a new question with all the required details (product name and version, what you would like to do, what you tried etc.).

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    @Neha_Naik004 @Gianni Ceresa

    Yeah, I’m not very active here anymore. OTN went through some upgrades that reset my status to beginner. I did reach out to request that they restore it, but unfortunately, they couldn’t do anything about it, which ultimately led me to step away.