nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

All Column Values in the column formula

3193829

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,

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

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

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,

3193829

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

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

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

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

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

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)

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

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

3193829

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

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

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

@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.