7 Replies Latest reply on Nov 30, 2018 7:55 PM by Christian Berg

    Hide presentation column using "Hide object if"

    skull

      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
      ------------------
      jsmith
      jdoe
      

       

      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!