Reports, Charts and Dashboards are a useful and powerful feature of RSA Governance & Lifecycle.

However, it would seem that many are not using this to its full potential...so we are here to help change that!

I've created this video and presentation, to help you understand the fundamentals of how this feature of the product works, along with a real example you can apply yourself, in your environment. 

For more advanced next steps, please see this content: Dynamic Reports & Charts - What are they and how do you use them?  

Please watch the video to learn more and leave you thoughts/comments below! 

Zoom Video Link --> RSA Reports, Charts and Dashboards - Overview and Live Demo - Zoom 

Example of what is created in the video

Useful SQL for finding tables you might need

Replace "xxx" with a lower case search, like "user" or "app" or "role"

select * from user_views
where lower(view_name) like '%xxx%'
order by view_name asc‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

SQL used in the video and presentation.

Please note that this SQL might not be relevant for your environment, so always test this in Dev before testing in Production. Some of this SQL might produce too much data and so adding a date filter (e.g.. in the last year) might be useful. The below is shared to give you examples of the SQL which was used in the demo and presentation/video. 

Active Review Details (Report)

(SELECT 
pR.NAME, 
pR.END_DATE,
pRS.REVIEWED_ENTS_COUNT,
pRS.TOTAL_ENTS_TO_REVIEW,
pRS.REVIEWED_USERS_COUNT,
pRS.TOTAL_USER_COUNT,
concat(CAST(((pRS.REVIEWED_ENTS_COUNT/pRS.TOTAL_ENTS_TO_REVIEW) *100) AS NUMERIC(10,0)),'%') AS Percent_Complete
FROM AVUSER.PV_REVIEW pR
LEFT JOIN AVUSER.PV_REVIEW_STATUS pRS
ON pR.ID = pRS.REVIEW_ID
WHERE pR.STATE = 'InProcess')‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Outstanding Review Items (Report)

(SELECT * FROM 
(SELECT R.NAME AS Review_Name,
U.FIRST_NAME,
U.LAST_NAME,
U.DEPARTMENT,
TOTAL_ENTS_TO_REVIEW AS Total_Review_Items,
REVIEWED_ENTS_COUNT,
CAST((REVIEWED_ENTS_COUNT /TOTAL_ENTS_TO_REVIEW) AS NUMERIC(10,2)) AS Input_Pct,
concat(CAST(((REVIEWED_ENTS_COUNT/TOTAL_ENTS_TO_REVIEW) *100) AS NUMERIC(10,0)),'%') AS Percent_Complete
FROM avuser.PV_REVIEW R
JOIN avuser.PV_REVIEW_REVIEWER_SUMMARY REVIEWER_STAT_BY_COMP
ON R.ID = REVIEWER_STAT_BY_COMP.REVIEW_ID
JOIN avuser.PV_USERS U
ON REVIEWER_STAT_BY_COMP.REVIEWER_ID = U.ID
WHERE R.STATE= 'InProcess')
WHERE PERCENT_COMPLETE <> '100%'
ORDER BY TOTAL_REVIEW_ITEMS DESC)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Active Reviews (Chart)

(SELECT 
pR.NAME, 
pR.END_DATE,
CAST(((pRS.REVIEWED_ENTS_COUNT/pRS.TOTAL_ENTS_TO_REVIEW) *100) AS NUMERIC(10,0)) AS Percent_Complete
FROM AVUSER.PV_REVIEW pR
LEFT JOIN AVUSER.PV_REVIEW_STATUS pRS
ON pR.ID = pRS.REVIEW_ID
WHERE pR.STATE = 'InProcess')‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Overall Review Summary (Chart)

(select 
 state as review_state,
 count(*) as total_items
from AVUSER.PV_REVIEW
group by state)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍