Placeholder

Customer Forum

GDPR Supplier Security Assessments and Accreditations

Tigya (Workbooks Online) Posted: 2018-05-01 11:50
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:

report_complete.png

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)

These are just some of the accreditations you may want to track against your Suppliers. If required, you will need to create the appropriate custom Checkbox Fields on Task Records. They must be added as calculated columns in details view. These are then concatenated into a string which can be displayed on the "Approved Suppliers" view described below.

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:

newcriteria.jpg

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.

 

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:

approvedcriteria.jpg

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.

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:

criteria_report2.png

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:

criteria_report.png

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.

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

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.