PDF
Download PDF
Download page Creating a Risk Matrix.
Creating a Risk Matrix
Simple Risk Matrix
- Wrap your manually created or macro generated source table in the Pivot Table macro
Configure the Pivot Table macro
Row Labels Likelihood Column Labels Impact Calculated Column Page Operation Type Count
- Additional parameters: hide totals of the pivot table if required
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 Labels Severity Column Labels Probability Calculated Column Key Operation Type Count 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
