Simple Risk MatrixLink to Simple Risk Matrix

  • Wrap your manually created or macro generated source table in the Pivot Table macro

  • Configure the Pivot Table macro

    Row LabelsLikelihood
    Column LabelsImpact
    Calculated ColumnPage
    Operation TypeCount
  • Additional parameters: hide totals of the pivot table if required

Advanced Risk MatrixLink to Advanced Risk Matrix

 
  • Wrap your manually created or macro generated source table subsequently in the Pivot Table, Table Transformer #1, Table Transformer #2, and Table Transformer #3 macros

  • Configure the Pivot Table macro

    Row LabelsSeverity
    Column LabelsProbability
    Calculated ColumnKey
    Operation TypeCount

    The hide totals option is also enabled

  • Configure the Table Transformer #1 macro: here we correctly process empty cells and make sure that everything will be working fine if any status types from the 'Probability' column are not present in the current source table
SELECT 
T1.'Severity' AS 'Severity/Probability',
IFNULL(T1.'Count Almost none', "") AS 'Almost none',
IFNULL(T1.'Count Low', "") AS 'Low',
IFNULL(T1.'Count Medium', "") AS 'Medium',
IFNULL(T1.'Count High', "") AS 'High',
IFNULL(T1.'Count Very high', "") AS 'Very high'
FROM T1
WHERE T1.'Severity' IS NOT NULL
SQL
  • Configure the Table Transformer #2 macro: here we make sure that everything will be working fine if any status types from the 'Severity' column are not present  in the current source table.
    Before applying the SQL query given below, navigate to the Settings tab and enable both "Transpose source tables" and "Transpose result table" options
SELECT 
T1.'Severity/Probability',
IFNULL(T1.'Trivial', "") AS 'Trivial',
IFNULL(T1.'Low', "") AS 'Low',
IFNULL(T1.'Medium', "") AS 'Medium',
IFNULL(T1.'High', "") AS 'High',
IFNULL(T1.'Critical', "") AS 'Critical'
FROM T1
SQL
  • Configure the Table Transformer #3 macro: here we color the Risk Matrix based on its column and rows statuses.
    To learn more about the FORMATWIKI function, visit the "Advanced table cells formatting" page
SELECT
T1.'Severity/Probability',
FORMATWIKI("{cell:bgColor=" + 
CASE 
WHEN T1.'Severity/Probability' = "Trivial" OR T1.'Severity/Probability' = "Low" THEN "green"
ELSE "orange"
END
     + "}" + 
     CASE 
     WHEN T1.'Almost none' = 0 OR T1.'Almost none' IS NULL THEN ""
     ELSE T1.'Almost none'
     END
     + "{cell}") 
AS 'Almost none',
FORMATWIKI("{cell:bgColor=" + 
CASE 
WHEN T1.'Severity/Probability' = "Trivial" THEN "green"
ELSE "orange"
END
     + "}" + 
     CASE 
     WHEN T1.'Low' = 0 OR T1.'Low' IS NULL THEN ""
     ELSE T1.'Low'
     END
     + "{cell}") 
AS 'Low',
FORMATWIKI("{cell:bgColor=" + 
CASE 
WHEN T1.'Severity/Probability' = "Critical" THEN "red"
ELSE "orange"
END
     + "}" + 
     CASE 
     WHEN T1.'Medium' = 0 OR T1.'Medium' IS NULL THEN ""
     ELSE T1.'Medium'
     END
     + "{cell}") 
AS 'Medium',
FORMATWIKI("{cell:bgColor=" + 
CASE 
WHEN T1.'Severity/Probability' = "Critical" OR T1.'Severity/Probability' = "High" THEN "red"
ELSE "orange"
END
     + "}" + 
     CASE 
     WHEN T1.'High' = 0 OR T1.'High' IS NULL THEN ""
     ELSE T1.'High'
     END
     + "{cell}") 
AS 'High',
FORMATWIKI("{cell:bgColor=" + 
CASE 
WHEN T1.'Severity/Probability' = "Critical" OR T1.'Severity/Probability' = "High" OR T1.'Severity/Probability' = "Medium" THEN "red"
ELSE "orange"
END
     + "}" + 
     CASE 
     WHEN T1.'Very high' = 0 OR T1.'Very high' IS NULL THEN ""
     ELSE T1.'Very high'
     END
     + "{cell}") 
AS 'Very high'
FROM T1
SQL