Build a Risk Assessment Report

ujjwala_678
Tera Contributor

8 hours ago - last edited 8 hours ago

Hi Everyone,

 

We need help creating a Compliance Risk Assessment report using a Database View, with an Excel export from list/table view.

 

Requirement

 

Pull Risk & Risk Assessment data ,

 

Risk Number, Risk, Assessment Number, Methodology, Assessable Entity

 

Inherent, Control Effectiveness & Residual ratings

 

Residual Likelihood & Impact

 

Risk Response, Assessor, State, Next Scheduled Date

 

Previous Assessment (Inherent, Control Effectiveness, Residual, Likelihood, Impact)

 

Control Effectiveness (5 components + sub-components)

 

Factors and their responses displayed as columns

 

Inherent Impact (Reputation, Regulatory)

 

Likelihood comments 

Best approach to design a Database View for this?

 

How to flatten factor-based responses into columns?

 

Any limitations/best practices for Excel export?

 

Any guidance or examples would be appreciated. Thanks! 

  • 239 Views
1 REPLY 1

SagnicDas_dev
Mega Guru

6 hours ago

Hi @ujjwala_678 ,

Hope you are doing well.

To create a Compliance Risk Assessment report in that flattens factor responses into columns for Excel export, use a Database View to join metadata and assessment results.
 
1. Database View Design (Recommended Join Logic)
Join the following tables to capture all required metadata and scores. Ensure you are using the Advanced Risk Assessment (ARA) tables.
 
Table Alias Order Prefix Join Where Clause
Risk [sn_risk_risk]risk100rk(None)
Risk Assessment Instance [sn_risk_advanced_risk_assessment_instance]asmt200asas_risk = rk_sys_id
Entity [sn_grc_profile]ent300enrk_profile = en_sys_id
Risk Assessment Methodology [sn_risk_advanced_risk_assessment_methodology]ram400rmas_methodology = rm_sys_id
  • Ratings & Logic: These ratings (Inherent, Control Effectiveness, Residual) are stored directly on the Assessment Instance [sn_risk_advanced_risk_assessment_instance] record. sn_risk_advanced_risk_assessment_methodology_list
  • Previous Assessments: To include previous ratings, you must add a second alias of the Assessment Instance table (e.g., prev_asmt) and join it where prev_asmt.risk = asmt.risk and prev_asmt.state = 'completed'.
 
2. Flattening Factor Responses into Columns
Flattening dynamic factor responses (like "Inherent Impact - Reputation") into a single row for Excel is the most complex part of this requirement.
  • The Challenge: Factors are stored in a related table as multiple rows per assessment. Standard Database Views do not pivot rows into columns.
  • The 2026 Solution: Use Automatic Reporting Views. When you publish a Risk Assessment Methodology (RAM) in modern versions (Xanadu/Yokohama), ServiceNow automatically generates a specific database view (typically named sn_risk_ara_<ram_sys_id>_v).
    • This system-generated view already flattens factors into columns.
    • Recommendation: Find this specific view in System Definition > Database Views and use it as your base instead of building one from scratch.
 
3. Control Effectiveness (5 Components)
If your methodology uses specific components for Control Effectiveness, these are typically captured as Factor Groups. To include them in your export:
  1. Navigate to the generated Reporting View.
  2. Add the specific fields representing those Factor Groups to your list view.
  3. For sub-components, ensure the RAM is configured to "Show results at factor level" so the values roll up into the view's columns.
 
4. Best Practices for Excel Export
  • List View Formatting: Before exporting, use the ServiceNow List Layout to add all required columns (Residual Likelihood, Impact, Assessor, etc.).
  • Custom Labels: If Excel column headers must match specific names (e.g., "Likelihood Comments" instead of the field's technical name), use Language File [sys_documentation] entries for the Database View to override labels.
  • Export Limits: By default, Excel exports are capped at 10,000 records. If your compliance report is larger, adjust the glide.pdf.max_rows or equivalent Excel system property.
  • Performance: Avoid joining more than 5–6 tables in a single view, as it can cause timeouts during large Excel exports. 
If you find the solution helpful please mark it as helpful and accept the solution.

Regards,

Sagnic