Analytics Software

Hi,

We are trying to implement data level security, let me explain the issue

The requirement is, we have 7 schools and each school has one principle , there will be a Superdintent who has 3 schools under him. so now when each principle logs in to dashboard we have a prompt for school i.e Name of school in that prompt he should see only his school and even the data of that school only which are assigned to him, now when Superdintent logs in he should see all 3 schools in the prompt and data. I have gone through this link (http://www.rittmanmead.com/2012/03/obiee-11g-security-week-row-level-security/) but could not achieve.

We are able to achieve by writing SQL in BMM layer ( LTS Table) so where ever the table is used in dashboards the security is being applied and we are able to see what we want. We want to achieve this by application role, But when we are creating session variables and applying on Application Role its not working. We want to achieve this by using Application role because suppose in other dashboards when the table is not used or pulled in, it will not work.But if we do it using application role its applies to all dashboards and data is resticted. so that when principle or Superdintent logs in automatically its restricts the data.

Below is the SQL which we used in BMM LTS, its working fine. But when the same SQL is applied in Application Role it's not working.

SQL used in session variable -
select 'SCHOOL_CD1', school_cd1 from w_staff_d where empl_id ='VALUEOF(NQ_SESSION.USER)'
and job_desc1 = 'Principal High School - KPI'

Any suggestions please ??

Thanks,
VRP