|
NOTE: This report forms part of our GDPR Packaged Service. For more information on this service please contact our Support Team on support@workbooks.com. Alternatively, you can implement this report and the GDPR Solution yourself using the GDPR Plugin available from the Workbooks Script Library. If you are following the Plugin, please ensure you name this report exactly as specified otherwise you will not be able to complete the installation. Most of the fields used in this Report, reference Custom Fields that are created via the Plugin.
This Report will give you a detailed overview of all your Suppliers giving you visibility over their status (Approved, Lapsed, Awaiting Review) and also when they are due their next Security Assessment. This report also allows you to quickly see which accreditations they currently have in place.
Once you have built this report, it should look something like the below:
There is a Template Report available to get you started which has some of the basic columns and views. You should start by creating a new Report by clicking New > Report > Create a report from a template report and select "Template - GDPR Supplier Security Assessments and Accreditations". Give the report a name as follows:
Report Name: GDPR Supplier Security Assessments and Accreditations
Once you have created the report from the template, you now need to add in the remaining columns that reference Custom Fields in your database as detailed below. Of course, if you want to add further Custom Fields to these reports, you can do so as required.
DETAILS VIEW
Add the following calculated columns to the report:
| Column # |
Column Name |
Formula |
| 1 |
ISO27001 |
IF(suppliers.activities.cf_task_iso27001, 'ISO27001', NULL) |
| 2 |
ISO37001 |
IF(suppliers.activities.cf_task_iso37001, 'ISO37001', NULL) |
| 3 |
ISO9001 |
IF(suppliers.activities.cf_task_iso9001, 'ISO37001', NULL) |
| 4 |
Carbon Trust |
IF(suppliers.activities.cf_task_carbon_trust, 'Carbon Trust', NULL) |
Lastly, add this calculated column to finish the details view:
| Column # |
Column Name |
Formula |
| 1 |
Next Security Assessment Date |
IF(suppliers.activities.activity_type = 'Supplier Security Assessment', (
CASE
WHEN suppliers.cf_organisation_review_frequency = '12 months' THEN ADDDATE(suppliers.activities.completed_date, INTERVAL 1 YEAR)
WHEN suppliers.cf_organisation_review_frequency = '24 months' THEN ADDDATE(suppliers.activities.completed_date, INTERVAL 2 YEAR) WHEN suppliers.cf_organisation_review_frequency = '36 months' THEN ADDDATE(suppliers.activities.completed_date, INTERVAL 3 YEAR) ELSE ADDDATE(suppliers.activities.completed_date, INTERVAL 1 YEAR) END), NULL) |
NEW SUPPLIERS (SUMMARY VIEW):
This Summary View shows you Suppliers that have never had a Security Assessment completed. These Suppliers should not be used until the appropriate checks have been carried out.
Add the following calculated columns:
| Column # |
Column Name |
Formula |
| 1 |
CSS Styling |
CASE
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) > CURDATE() THEN 'background-color:green; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) <= CURDATE() THEN 'background-color:red; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Rejected' THEN 'background-color:red; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Under Review' THEN 'background-color:gold; color:black'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'New' THEN 'background-color:red; color:white' ELSE 'background-color:red; color:white' END
|
| 2 |
Assessment Due Date |
IF( MAX( PARENT('Supplier Security Assessment Date')) IS NULL OR PARENT('Supplier Security Assessment Date') != '' , 'Supplier Security Assessment Required', MAX( PARENT('Next Security Assessment Date'))) |
| 3 |
Assessment Status |
CASE
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) > CURDATE() THEN 'Approved'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) <= CURDATE() THEN 'Lapsed'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Rejected' THEN 'Unapproved'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Under Review' THEN 'Under Review'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'New' THEN 'New' ELSE 'New' END
|
You will then need to add a Calculated Criteria as shown to restrict the summary view to show only New activities:
APPROVED SUPPLIERS (SUMMARY VIEW)
This view shows Suppliers that have a valid "Approved" Security Assessment Activity, i.e. an assessment activity that has a Status of "Approved" and was completed within the Review Frequency - or 1 year if this has not been set.
Add the following calculated columns to the report:
| Column # |
Column Name |
Formula |
| 1 |
CSS - Accreditations |
CASE
WHEN CONCAT_WS(', ', PARENT('ISO27001'), PARENT('ISO37001'), PARENT('ISO9001'), PARENT('Carbon Trust')) != "" THEN 'background-color:green;colour:white;'
ELSE "background-color:red; color: white;"
END
|
| 2 |
Supplier Accreditations |
CASE
WHEN CONCAT_WS(', ', PARENT('ISO27001'), PARENT('ISO37001'), PARENT('ISO9001'), PARENT('Carbon Trust')) != "" THEN CONCAT_WS(', ', PARENT('ISO27001'), PARENT('ISO37001'), PARENT('ISO9001'), PARENT('Carbon Trust'))
ELSE "No Accreditations Recorded"
END
|
| 3 |
CSS Styling |
CASE
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) > CURDATE() THEN 'background-color:green; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) <= CURDATE() THEN 'background-color:red; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Rejected' THEN 'background-color:red; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Under Review' THEN 'background-color:gold; color:black'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'New' THEN 'background-color:red; color:white' ELSE 'background-color:red; color:white' END
|
| 4 |
Assessment Due Date |
IF( MAX( PARENT('Supplier Security Assessment Date')) IS NULL OR PARENT('Supplier Security Assessment Date') != '' , 'Supplier Security Assessment Required', MAX( PARENT('Next Security Assessment Date'))) |
| 5 |
Assessment Status |
CASE
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) > CURDATE() THEN 'Approved'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) <= CURDATE() THEN 'Lapsed'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Rejected' THEN 'Unapproved'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Under Review' THEN 'Under Review'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'New' THEN 'New' ELSE 'New' END
|
You will then need to add a Calculated Criteria as shown to restrict the summary view to show only Approved activities:
SUPPLIERS UNDER REVIEW (SUMMARY VIEW):
This Summary View shows Suppliers that may have previously lapsed or new Suppliers that are currently have Assessments that are currently being reviewed. These Suppliers should not be used until the appropriate checks have been completed.
Add the following calculated columns:
| Column # |
Column Name |
Formula |
| 1 |
CSS Styling |
CASE
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) > CURDATE() THEN 'background-color:green; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) <= CURDATE() THEN 'background-color:red; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Rejected' THEN 'background-color:red; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Under Review' THEN 'background-color:gold; color:black'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'New' THEN 'background-color:red; color:white' ELSE 'background-color:red; color:white' END
|
| 2 |
Assessment Due Date |
IF( MAX( PARENT('Supplier Security Assessment Date')) IS NULL OR PARENT('Supplier Security Assessment Date') != '' , 'Supplier Security Assessment Required', MAX( PARENT('Next Security Assessment Date'))) |
| 3 |
Assessment Status |
CASE
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) > CURDATE() THEN 'Approved'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) <= CURDATE() THEN 'Lapsed'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Rejected' THEN 'Unapproved'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Under Review' THEN 'Under Review'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'New' THEN 'New' ELSE 'New' END
|
You will then need to add a Calculated Criteria as shown to restrict the summary view to show only Under Review activities:
Once you have added all columns - you can apply the "CSS Styling" column created above as "Calculated conditional styling" from the Style tab on each column created. This column applies Red, Amber and Green colours depending on the status of the Supplier.
LAPSED SUPPLIERS (SUMMARY VIEW):
This Summary View shows all Suppliers where they have previously had an Approved assessment completed which has now lapsed i.e. the Completed Date is past the Review Frequency set on the Supplier's Record.
Add the following calculated columns:
| Column # |
Column Name |
Formula |
| 1 |
CSS Styling |
CASE
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) > CURDATE() THEN 'background-color:green; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) <= CURDATE() THEN 'background-color:red; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Rejected' THEN 'background-color:red; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Under Review' THEN 'background-color:gold; color:black'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'New' THEN 'background-color:red; color:white' ELSE 'background-color:red; color:white' END
|
| 2 |
Assessment Due Date |
IF( MAX( PARENT('Supplier Security Assessment Date')) IS NULL OR PARENT('Supplier Security Assessment Date') != '' , 'Supplier Security Assessment Required', MAX( PARENT('Next Security Assessment Date'))) |
| 3 |
Assessment Status |
CASE
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) > CURDATE() THEN 'Approved'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) <= CURDATE() THEN 'Lapsed'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Rejected' THEN 'Unapproved'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Under Review' THEN 'Under Review'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'New' THEN 'New' ELSE 'New' END
|
You will then need to add a Calculated Criteria as shown to restrict the summary view to show only Lapsed activities:
SUPPLIER STATUS (SUMMARY VIEW):
This Summary View is used to display the relevant information in the Report Cell fields that are displayed on Organisations in regards to when a Supplier's Next Assessment is due, when their last one was completed along with the current status.
Add the following calculated columns:
| Column # |
Column Name |
Formula |
| 1 |
CSS Styling |
CASE
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) > CURDATE() THEN 'background-color:green; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) <= CURDATE() THEN 'background-color:red; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Rejected' THEN 'background-color:red; color:white'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Under Review' THEN 'background-color:gold; color:black'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'New' THEN 'background-color:red; color:white' ELSE 'background-color:red; color:white' END
|
| 2 |
Next Assessment Date |
IF(MAX(DATE_FORMAT(PARENT('Supplier Security Assessment Date'), '%d/%m/%Y')) IS NULL, 'Security Assessment Required',
MAX(DATE_FORMAT(PARENT('Next Security Assessment Date'), '%d/%m/%Y')))
|
| 3 |
Assessment Status |
CASE
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) > CURDATE() THEN 'Approved'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Approved' AND MAX( PARENT('Next Security Assessment Date')) <= CURDATE() THEN 'Lapsed'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Rejected' THEN 'Unapproved'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'Under Review' THEN 'Under Review'
WHEN SUBSTRING_INDEX( GROUP_CONCAT( PARENT('Status') ORDER BY PARENT('Supplier Security Assessment Date') DESC SEPARATOR ', '), ', ', 1) = 'New' THEN 'New' ELSE 'New' END
|
|
