Risk information into Control Test report

KinserIsaac
Tera Contributor

‎05-07-2025 11:12 AM

Bottom Line: I need to show the risk name and description in a drilldown list report where the main table is Control Test. Currently, I can't dot-walk it.

 

Context: My requirement is to show a pie chart that has control test results. When the user clicks on the pie chart the drilldown is a list that shows the control, control description, risk, and risk description. I was able to get the control and control description via dot walking.

 

What I can't figure out is how to retrieve the risk that is associated with the control tests control. Essentially, I need to query the risk in the related list of the control. Having trouble figuring that out.

 

One thought I had was to have a reference field that has a calculated value script that pulls the risk from its associated control, but I am having trouble with the script because the reports table is control test; too complex for my current skill level. 

 

Open to other suggestions! Thanks. 

0 Helpfuls
  • 218 Views
1 REPLY 1

KinserIsaac
Tera Contributor

‎05-08-2025 10:13 AM

Ok I figured it out. Wanted to share my solution to others in case they run into the same issue. 

 

I ended up using a database view where I related 4 tables. 

 sn_risk_risk 

  • order: 100
  • Where clause: empty

sn_risk_m2m_risk_control

  • order: 200
  • Where clause: risk_sys_id = m2m_sn_risk_risk

sn_audit_control_test

  • order: 300
  • where clause: audit_control = m2m_sn_compliance_control

sn_audit_test_of_effectiveness_results (custom table) 

  • order: 400
  • where clause: toe_u_control_test = audit_sys_id

This allowed me to run a report utilizing data from risk, control, control test, and our custom table. 

0 Helpfuls