I have a report, both Table and Chart, to show form usage.
I was wondering if there is a similar reporting method for Role usage.
Example: How many users were added to a role in a given month.
Do you want to get your source of data from a query that targets the CRs used to add/remove users to/from roles as above? The following would target the role membership data itself within the role and not the data of change.
I'd usually would guide to using views, however the core of this use case is dates and I haven't found a role membership view that contains the date column needed. The T_AV_ROLEMEMBERSHIPS table contains all members of all roles whether still are members or not. The way you know that a user is no longer a member of a role is by identifying the record of membership of that User per the role. The date columns I am talking about here is the "CREATION_DATE and "DELETION_DATE" columns. For the deletion date column, If the deletion date column value is populated, that means that the user is no longer a member of the role within the record that has the deletion date value not set to null, however if set to null, that means that the user is a valid member of the role.
The following query checks users who were added between the 18th of June 2020 until now:
Select r.name as ROLE_NAME,u.last_name||', '||u.first_name as FULL_NAME,CASE WHEN rm.deletion_date is not null then 'No Longer a Member' ELSE 'Valid Member' END as MEMBERSHIP_STATUSfrom T_AV_ROLEMEMBERSHIPS rmJOIN PV_ROLE r on r.id=rm.role_idJOIN PV_USERS u on u.id=rm.member_idWHERE u.deletion_date is nulland r.deletion_date is nulland rm.creation_date between '18-JUN-2020' and sysdate;
You can also write in an opposite report to show leavers of roles by utilizing the deletion_date column within the T_AV_ROLEMEMBERSHIPS table as illustrated above.
I hope this helps
what version are you on?
can you share images and SQL you have used today.
im sure we can easily move this over to roles as we track all that info in the DB
This is the query for Forms:
(select f.name AS Form_name,count(*) AS Usage,c.REQUEST_DATE_MONTH_TEXT AS Month,to_char(c.REQUEST_DATE,'YYYY "Q"-Q') as Quarter from CHANGE_REQUEST_FORM f, change_request c where c.FORM_ID is not nullANDc.request_Date between add_months(trunc(sysdate, 'MM'), -3) and trunc(sysdate)ANDf.id = c.form_idGroup By f.name,c.REQUEST_DATE_MONTH_TEXT, to_char(c.REQUEST_DATE,'YYYY "Q"-Q') )
Retrieving data ...