OR between groups in Row-Restrictions, priority - Universe Design

[Former Member]

Old School Univers Design, no SAP source, but plain Oracle

Two Questions here

1 - Priority in rowrestrictions

from documentation:

u201CYou can specify which restriction to apply to a user that belongs to multiple groups using a universe. For example a user belongs to two groups, Sales with a restriction to view 5000 rows of data, and Marketing to view 10000 rows. When the user refreshes a report, the restriction associated with the lowest level group is applied. In the example above, if the Sales group had order 1 and Marketing had order 2, the restriction from marketing (10000) would be used.u201D

I read in the documentation for Rowrestrictions, that the restriction on top of the order list will be the one kicking in, in case of a huser having two conflicting restrictions.

Does anyone have experience with this ?

Does it work ?

My experience is that its not working. Both rowrestrictions gets into the SQL with AND between as shown

AND ( FLEX_SEGMENT5.FLEX_VALUE IN (030,033,090,805,041,062,048)

AND FLEX_SEGMENT5.FLEX_VALUE IN (041,048,062)

the first line is my new restriction with priority 1,

the second line is the other restriction with priority 6

2 - OR between groups

there is a Restriction option in Manage Rowrestrictions , where you can spesify Rowrestriction Combinations using AND or OR.

I have not been able to have this working.

Anyone having a positive experience here ? Or have I misunderstood what this actually means.

I would expect

WHERE ( FLEX_SEGMENT5.FLEX_VALUE IN (030,033,090,805,041,062,048)

OR FLEX_SEGMENT5.FLEX_VALUE IN (041,048,062)

What I get is

( FLEX_SEGMENT5.FLEX_VALUE IN (030,033,090,805,041,062,048)

AND FLEX_SEGMENT5.FLEX_VALUE IN (041,048,062)

Is this a known bug ?

I have tried this in WEBI 3.1 and R2

[MariannevL · Accepted solution]

Hi Ingrid,

For me it works...

I have two department groups in the CMC and I am member of both.

I have two restrictionsets, one allows to see rows for one department, one for the other.

(As in your case the same underlying table.column, but different values).

I apply one restriction to department group A, the other to department group B.

Now with the setting to only AND, i see nothing (the condition gets an AND).

With the setting to OR between groups I get the OR and see data for both departments.

My department groups don't have a hierarchical relationship.

So it may be that you are experiencing this because of this relationship.

Check that the groups are not in the same hierarchy...

Hope this helps,

Marianne

[Former Member · Accepted solution]

Thank you for your quick answer.

I dont think there are any hierarchies in the Universe.

Happy to hear that it works for you.

Are you running on Oracle as well ?

which version Oracle, BO +

Are you using rowrestrictions as well ?

best Regards

Ingrid Husby

[MariannevL · Accepted solution]

I'm not talking hierarchies in the universe, but usergroups in the CMC.

You apply a restrictions to a usergroup.

If one usergroup is a subgroup of another usergroup, it will use the AND no matter what you choose.

If the two are separate, like both subgroup of another group, or both top level, then you get the OR if you choose the last option.

BO 3.1 sp3 is the latest I used, but I haved used this in earlier versions as well.

Oracle version really doesn't make a difference, its in the rendering of the sql that this is handled,

and there has not been a change in syntax there. Also used different versions (10 earliest if I recall correctly).

Hope you get it to work too,

Marianne

[Former Member · Accepted solution]

Thanks for answers.

It is clearer now. But I still get error.

I get the error message ORA-01722 invalid number only when I choose to go for the OR option.

It is a predefined filter , alphanumeric getting values from a char field.

Does anyone know how to see the SQL generated for the diferent options.

Or the SQL generated at the promt moment ?

Ingrid

[MariannevL · Accepted solution]

Hi again,

Do both restrictions parse ok in designer?

This invalid number only happens when both are combined with an OR? (As this does sound strange).

Best way to check is to have test users (or business users willing to work with you) two in both single groups, one in both.

Now as each user create a simple report and select an object from the table with the restriction.

When you view the SQL (the SQL button in the query panel) you can already see the restriction kicking in.

See if it looks as expected, then run and see if you have the error.

Hope you get it to work,

Marianne

[Former Member · Accepted solution]

I think my problems are due to yet another group the users also is a part of.

I guess the OR statement opens up the list for my LOV, so that wrong values might apply.

I don't hva time to look further into this at the moment.

Anyway I can see the OR statement in the SQL, so I mark question solved.

Thanks for your repluies.

Best Regards

Ingrid Husby

[christian_ah-soon]

Hello,

In fact, Priority is used for universe overload where only one overload among several might apply. This is the case for connection overloads, table mappings..., but not for row restrictions.

For row restrictions, you have AND/OR aggregation operators. In Universe design tool, you have the option to choose how Row restriction will aggregate (click the "cog" icon):

+ Combine row restrictions using AND

+ Combine row restrictions using AND within group hierarchies and OR between groups

In the first case, all row restrictions that might apply to a user will be aggregated with AND operators.

In the second case, row restrictions that are inherited are aggregated with AND operators and the ones coming the user groups a user belongs to are aggregated with OR operators.

Ex with the following user groups organisation:

G1

G11 (G11 belongs to G1 group)

G2

G21 (G21 belongs to G2 group)

and user U belongs to G11 and G21 user groups.

Final row restriction that applies to the user U is:

(G1 AND G11) OR (G2 AND G21)

Hope it helps.

Cheers

~~cas