I have a requirement to generate an all identites analytic report that can also show the first enterprise role assigned to them, total number of enterprise roles and total number of roles that a user has in a report with other columns.
Below is my current query:
Select u.username, concat(u.firstname,' ',u.lastname),m.username,m.lastname ,m.firstname, m.displayname, m.username, m.displayname,u.customproperty16,u.customproperty24,u.customproperty6,u.customproperty11,u.customproperty15, u.customproperty53, u.customproperty54,CASE WHEN u.statuskey = 0 THEN 'Inactive' WHEN u.statuskey = 1 THEN 'Active' else u.statuskey END,u.customproperty55,u.updatedate,u.customproperty51,u.customproperty52 ,u.customproperty22,u.customproperty17,u.customproperty21,u.customproperty21 ,u.customproperty20,u.customproperty25,u.customproperty12,u.customproperty56,u.customproperty5,u.customproperty55 from users u inner join users m on u.manager=m.userkey
How would I build a query to display first enterprise role, total number of enterprise roles and total number of roles as columns?
