Cascading LOV Webi Xi3.1 - overridden SQL for LOV for top level

[Former Member]

Hi,

We have a requirement to implement cascading prompts and filter the list of values at top level.

Example: I have a hierarchy like Object 1 at top level and Object 2 and Object 3 (same level) below level 1.

Object 1 has an overridden LOV because of user security implemented like User AB can see only USA and Canada. Hence, I cannot create cascading LOV in Universe because it does not take overridden LOV. Tried creating custom hierarchy on these three objects and setting cascading LOV but overridden LOV does not work.

Object 1 comes from table Country and Object 2 and Object 3 come from table Zone.

User Security Table

User id	Country (Object 1)	Role
90012	USA	Distributor
90012	Canada	Distributor
90457	USA	Distributor

If User 90012 has selected USA on report, then he should see zones only under USA.

Please let us know how to implement this.

1. Thanks.

[CarlyThomas]

How is your override created at the Universe level, is it a row level restriction?

I'm a little confused on how the hierarchy is setup.

Are Object2 & Object3 something like  "State" (or region) then "City"?  Or are you describing a hierarchy on another object?

What fix level is your XI 3.1?

Thanks,

Carly

[Former Member]

It is like  Object1 --> Object 2 and also, Object1 --> Object 3. We require all three as prompts. Object 1 has user security and it is restricted by @variable(BOUSER').

Derived tables logic works in this condition but it is throwing a context issue because of context set up for one of the underlying table used in query of derived table.

[former_member205840]

HI

Do you have Country field in Zone table (mostly you will have) ?  Can you make a join from Country table to Zone table ?

I think you can create a derived table using these join and create a cascading prompt on this.

-Sastry