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

One Prompt to query against multiple columns in an analysis

Ronald L

I'm trying to figure out a way in OTBI when querying the Natural Account Segment - Value object, to also query against Natural Account Segment - Level 31, Level 30, Level 29, etc… I tried to add a case statment but I'm not entirely sure how we pass the users prompted value into the case in order to search that specific column.

Find more posts tagged with

OTBI

Accepted answers

gclampitt

So you want to enter an account value in a prompt and then return whatever segment it is in?

If so this should work :

Create a prompt and under prompt options - set a presentation variable eg AC_VAR.

Then in your analysis, add a filter for each level and set it to the PV

Level 22 value is equal to (presentation variable) AC_VAR
or
Level 23 value is equal to (presentation variable) AC_VAR
etc

All comments

gclampitt

If you have amended the formula in your analysis - you just need to make the formula in your prompt exactly match.

So if analysis formula is say: Case when "Account" = '11111' then '99999' else "Account" end

then make the formula in the prompt the same.

If thats not what you mean can you provide more detals please ie what is your formula and what are your requirementrs?

Ronald L

Basically, if a user types AllAccts in the prompt, and say level 31 has AllAccts underneath it in the hierarchy, I'd want that AllAccts prompt to query against level 31 value, so I was thinking something like

CASE WHEN value = Level 31 Value

THEN Level 31 Value

WHEN value = 30 value

THEN 30 value

Etc…

Posted a picture below to try and give a better idea into this

gclampitt

So you want to enter an account value in a prompt and then return whatever segment it is in?

If so this should work :

Create a prompt and under prompt options - set a presentation variable eg AC_VAR.

Then in your analysis, add a filter for each level and set it to the PV

Level 22 value is equal to (presentation variable) AC_VAR
or
Level 23 value is equal to (presentation variable) AC_VAR
etc

Ronald L

That was it, thank you very much!