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.

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

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

Ronald L

That was it, thank you very much!