Hide presentation column using "Hide object if"

Community DW Integration

Hi,

Oracle Business Intelligence 11.1.1.9.0

In the Presentation layer, I want to hide a column from most of the OBI users and make it visible only for a certain list of users defined in a table.

sql> select users from hr_security;USERS------------------jsmithjdoe

For testing how "Hide object if" works, I was able to use the below condition and only user "hradmin" could see the column but not others.

lower(VALUEOF(NQ_SESSION.USER)) <> 'hradmin'

Hoping I could call a custom database function, I wrote a function hr_sec_check.

create or replace function hr_sec_check (p_users in varchar2) return varchar2 is  l_user varchar2(1);begin  select 'Y'  into l_user  from hr_security  where users = p_users;  return l_user;exception  when no_data_found then    return 'N';  when others then    return 'N';end;/

Then for the specific presentation column's property, "Hide object if" code, I entered the below expression.

hr_sec_check(lower(VALUEOF(NQ_SESSION.USER))) = 'N'

But I get the below error when I click OK.

[nQSError: 27042] Function hr_sec_check is not defined by administrator.

If I fully qualify the function name, I get the below error.

[nQSError: 27002] Near <(>: Syntax error [nQSError: 26012] .

We already have EVALUATE_SUPPORT_LEVEL = 2; set in NQSConfig.INI

Can I not call a custom database function here?  If not, what other options are available to me?

As always, thanks in advance!

Joel

The approach I’d use in this scenario would be to take out the SELECT statement of your function additionally adding a where clause using the USER Session Variable and create an Initilalization Block and then a Session Variable. You can then reference the new Session Variable which should a Y or N for each user.

Community DW Integration

@Joel Acha thanks for the suggestion.  I have never done this but will give it a try.  Stay tuned for more questions :-)

I was just thinking if this can be achieved by creating Groups in Weblogic Console and Application Roles in EM and then assigning the group to the user?  Then I won't need a table to maintain.

Joel

but @skull you'd then need to maintain the user/application role mappings in WebLogic. Pushing this down to the application ensures that you don't have to replicate the security and can just have it maintained outside of OBIEE and only in 1 place.

Community DW Integration

So do you suggest maintaining in a table is a better solution?

Joel

I thought you already had an HR_SECURITY table?

Community DW Integration

Yes I had created that table for this POC.  So I don't have to use that table if there is a better way.

[Deleted User]

Fill the result into a session variable. That way the whole logic runs ONCE upon login of a person rather than EVERY time you need to know the value Y/N.