2 Replies Latest reply: Jan 16, 2013 3:28 AM by Alex1 RSS

    working with a string in obiee 11g

      Hi experts!

      I have an assignment to do.

      Each user that log in to obiee belongs a role/roles. I can see roles that I belong if I go to new analisys and I type: VALUEOF(NQ_SESSION.ROLES) on any column. So each user has a "different" STRING result.

      String is like: authenticated-role;BIAuthor;BIConsumer;Sales_01;BIAdministrator;anonymous-role;AuthenticatedUser

      My assignment is get "numbers" of Role Sales. So in previous example I would like to see in answers page: 1

      Each user belongs to one or more roles of Sales... for example:

      String is like: authenticated-role;BIAuthor;BIConsumer;Sales_01;Sales_05;anonymous-role;AuthenticatedUser

      In this case I'd to see: 1,5

      Another example: authenticated-role;BIConsumer;Sales_02;Sales_06;anonymous-role;Sales_07;AuthenticatedUser

      In this case: 2,6,7

      I think that is possible if I use some functions but I don't know how.

      Any help???

        • 1. Re: working with a string in obiee 11g
          Jack Carver
          Hi, you can use 'EVALUATE' expressions, which calls underlaying database functions.
          For example:
          1) create an Oracle database function, which will calculate your 'numbers for roles' (i hope you use Oracle database)
          create or replace function xx_count_roles(p_roles_str  in varchar2,
                                                    p_role_const in varchar2)
            return varchar2 deterministic is
            c_roles_delimiter varchar2(1) := ',';
            l_return_str      varchar2(512);
            for c1 in (select regexp_substr(p_roles_str, '[^;]+', 1, level) role
                         from dual
                       connect by instr(p_roles_str, ';', 1, level - 1) > 0) loop
              if (instr(c1.role, p_role_const) > 0) then
                l_return_str := l_return_str ||
                                to_number(substr(c1.role, length(p_role_const) + 1)) ||
              end if;
            end loop;
            l_return_str := rtrim(l_return_str, c_roles_delimiter);
            return l_return_str;
            when others then
              return sqlerrm;
          end xx_count_roles;
          2) then you can calls this function from Answer (type code similar to following in any answer column formula):
          EVALUATE('xx_count_roles(%1, %2)' as character, VALUEOF(NQ_SESSION.ROLES), 'Sales_')
          • 2. Re: working with a string in obiee 11g
            Thank you very much for your reply but unfortunaly I don't user oracle DB...

            Anyway I'll try your solution!