This Report is built from Compliance Records and allows you to see which Person or Lead’s associated Compliance Records will become invalid in within the next 90 days.

This Report is incredibly useful for Customers that are using Workbooks to become GDPR Compliant and therefore gives you easy to access information on whether you have the grounds to store or process peoples’ information.

Below we outline how to build the Report, as well as some ideas for Summary Views that can also be added to present other useful bits of information.

Step 1: Building the Details tab

From Start > New > Report > Create a Pre-populated Report > Compliance Records this will provide you with most of the Columns you require, you will need to add:

Person or Sales Lead Object Ref

The details tab does not require any Criteria.

Step 2: People or Leads that will lose their Compliance this Month.

The first Summary View you can build will be looking at at at all People or Leads that will lose their Compliance this month.

For this Create a Grouping Column for the Field Person or Sales Lead Object Ref as this will be your unique Identifier. Then you can then add extra columns such as the Person or Sales Lead Name as value columns.

The Important Field on this view to add is a Summarised Column of the Maximum Compliant Until date, this gives you the Compliant Until date furthest away from today’s date.

Once this Field is added you will need to create a Calculated Criteria of Maximum Compliant Until Date is in Calendar Range this month.

This Calculated Column and Criteria mean that if the last compliant until date is in this Month the Person or Lead will appear in the view.

Step 3: People or Leads that will lose compliance in the next 90 days

This Summary View can be a direct copy of the first Summary View when creating it - bar the “maximum compliant until date” criterion we applied, we will only need to make slight amendments to it in order to output what we need.

Once you have the view set up how you want, we need to add a Calculated Column called Compliance Expires in 90 days that looks like this:

IF(MAX(PARENT('Compliant Until Date')) <= DATE_ADD(CURDATE(), INTERVAL 90 DAY) AND MAX(PARENT('Compliant Until Date')) > CURDATE(), 1,0)

This calculation means that if the Maximum Compliant Until Date is less than or equal to ninety days from today then output a 1, otherwise output 0.
Once this column has been created we can hide it - it is used for calculation purposes only. 

The only criteria we need on this Summary view is a calculated criteria of “Compliance expires in 90 days” = 1. This will now only output People or Leads where there Compliance will expire in 90 days or less.

Step 4: People or Leads that are no Longer Compliant

Again, this Summary View can be a direct copy of the first one (people or Leads that will lose compliance this month) however we change the Criteria to the Maximum Compliant Until Date is before today, showing us all of the People or Lead Records in the Database where all of their Compliance Records have expired or been revoked.