How can I see a list of People and the Campaigns of which they are members?

Customer Forums

How can I see a list of People and the Campaigns of which they are members?

Support

Posted: 28 November 2012 06:03 PM Report

To report on the People on your database and their Campaign Membership, start by building a People report and adding all the columns for the data you want to see. REMEMBER, if you add the column for Campaign this will show you the value in the Campaign field on the Main tab of the Person record, ie, the Campaign that first introduced you to that Person.

To see the details of all the Campaigns of which the People are members you can select the Campaign Membership option, as shown in the screenshot below. This will open a new dialogue box where you can drill through to the fields on a Campaign record, thus you can choose the column for the name of the Campaign.

The above method will return multiple rows for each Person if they are the member of more than one Campaign.

If you just want to see one row of data per person, you can use the Group Concatenate function. So, instead of adding the Campaign Membership name as above, you would add a calculated column that contained the following formula:

GROUP_CONCAT(campaign_membership.campaign_name)

When using Group Concatenate you need to summarise the report. You could summarise by the Person Name but if there is more than one person on your database with the same name, this will skew your results. It would be better to add a column for Object Reference and summarise by that instead. (If you don't want to see the Object Reference in your report you can 'hide' that column.)

Using the above method will return a report that looks similar to the one below (click to enlarge):

 

 

 

 

Support

Posted: 12 August 2013 01:53 PM Report

In next release of Workbooks, due out in late summer, you can build the same report by undertaking the following steps;

       ·     Create a new blank report based on People. Within the Details view add a column for Person Name and Campaign Name (Campaign Membership > Campaign Name).

The details view now shows all People and which Campaigns they belong to. Currently, if a Person is a member of more than one Campaign a second row is added to the report to show this. A summary view can be built to so that the Campaigns a Person belongs to can appear on one row. To do this;

{C}·        Select 'Add Summary View' and 'Add grouping column'. Group by ‘Person Name’

{C}·        Add a Calculated column which uses the following formula;

‘GROUP_CONCAT( PARENT('Campaign Name') SEPARATOR ', ')’

This formula concatenates the Campaigns which a Person belongs to onto a single row.

This will produce a report which looks similar to the one below (click to enlarge).