This Report looks at Marketing Campaigns and highlights those that have Members that do not have a valid Compliance Record against them. In this example we will be basing the Report on Campaigns -  it could also be applied to Organisations, Opportunities or any Transaction Documents you are using where keeping track of your compliance is key.

To Create this Report you can build a blank Report based on Marketing Campaigns, then add the following seven columns:

Name
Marketing Campaign reference
Members > Member name
Members > Person > Person reference
Members > Lead > Sales Lead reference
Members > Person > GDPR Compliant
Members > Lead > GDPR Compliant

Then you need to add a single criteria of:
Members > Party or Lead type does not contain Organisation

Once these fields have been added you should see a list of all Leads and People that are members of all of your Campaigns. The criteria removes all Organisations as they do not have compliance Records and will create false positives in our Report.

Once this has been completed you need to create a calculated column and add the following formula and call it  “Non Compliant?”

CASE
WHEN members.lead.gdpr_compliant = TRUE OR members.person.gdpr_compliant = TRUE THEN 0
ELSE 1
END

This column is looking at whether either the Lead GDPR Compliant or Person GDPR Compliant Field is true. If either of these Fields are true (the checkbox is ticked) then they will output 0 otherwise 1.

You will now need to build a Summary View to show us which Campaigns have non compliant Members, as well as the total number of non compliant members.

On the Summary View, first create a grouping column using the Marketing Campaign reference Field, we use this instead of “Name”. This will give a better unique identifier than using Name, which may not be unique. Add Name as a value Column after this.

Yo can now create a subtotal column to count the total number of non-compliant campaign members per campaign. This is done by calculating the total from the Calculated Column “Non Compliant?” which we previously created in the Details tab.

You now need to create two calculated columns.

The first will output Yes or No depending on whether there are Non Compliant Members or not, to calculate this we use:

IF(SUM(PARENT('Non Compliant?')) > 0, ‘Yes’, ‘No’)

Call this column “Non Compliant Members”

This means that if the Total Non Compliant Members is greater than zero it will output Yes, if all members are compliant then it will output No.

The final column is used to apply CSS Styling to the Report, by creating a calculated column like this:

CASE
WHEN SUM(PARENT('Non Compliant?')) > 0 THEN 'background-color:red;color:black;font-weight: bold'
WHEN SUM(PARENT('Non Compliant?')) = 0 THEN 'background-color:green;color:black'
ELSE 'background-color:white;color:black'
END

Call this column Colour - you can hide this column from the report as it does not show any information, it is just used as a visual aid.

Then applying it to the Style tab of the previously created “Non Compliant Members” columns and apply the Colour column you created in the last step as a calculated conditional style will output a red background with bold black text when the campaign contains non compliant members and a green background when all members are compliant.