|Posted: 2018-04-19 13:12|
To Create this Report you can build a blank Report based on Marketing Campaigns, then add the following seven columns:
Then you need to add a single criteria of:
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.
Further to this you could build another Summary View from this one to show only Campaigns with Compliant or Non Compliant Members by using the yes / no field as your criteria.