How To Achieve The Below Goal In Obiee Dashboard

I have a table like below: id name role1 role2 role3 ------------------------- 1 John y n y 2 Pete n y y 3 Den n y y 4 Mat y n n Afte

Solution 1:

If your database is Oracle 11g or later you can use the unpivot clause

SELECT usr_role,
  COUNT(*) role_count
FROM
  (SELECT*FROM table_name 
     UNPIVOT (hasRole FOR usr_role IN (role1,role2,role3))
  WHERE hasRole ='y'
  )
GROUPBY usr_role ;

This returns:

USR_ROLE  ROLE_COUNT
ROLE3      3
ROLE1      2
ROLE2      2

You could use this query as an opaque view in the RPD