We were recently asked:

"Could you please explain how the Group_Concat function should work in the new reporting functionality.  Previously we would have created a calculated column called all campaigns by using the breadcrumbs.  This would create a Group_Concat all of the campaign ref each person had been mailed.  We would then have set a criteria on this field to say does not contain camp-1542.  When we try this now it will not let you create the Group concat."

ANSWER:

It's important to remember that the new functionality allows you to create multiple views of your data. Each report includes a details view, which lists all the data you need to see. The details view can include calculations but not ones that aggregate records (eg, formulae such as total, count, average, group concat, etc). To carry out aggregation calculations such as group concat, you need to use a summary view.  Each report can have multiple summary views if that makes sense.

So, for example, you could have one detail view that lists all of the members on your database, which includes columns for their name, object reference, telephone number, etc, etc. If you also add a column to show the campaigns of which they are a member, you can then use this information on your summaries. You could then build a summary that shows the members who aren't members of a particular campaign. You could build a number of different summary views e.g. those for whom you don't have a mobile number (as long as you've included a column for mobile number in your detail report), those whose names begin with A etc.

Save and close and click Add Summary View, which opens a new tab.  When you do this you'll see that the View name field is already populated with the word summary but you can overtype this. 

To add columns to the Summary View click Add value column > Value column and use the dropdown next to column to select the columns from the detail view that you want to see in the summary. Notice that each time you add one you can click Save & New to add another, rather than having to click Save & Close and starting all over again.

You can then add a calculated column to group concatenate all the object references and then summarise them by the person object reference. Previously you had to group concatenate and then open the summary tab separately, but now you can achieve this all in one by clicking Add value column > Calculated column. 

You'll see a formula window, just like before, where you can enter GROUP_CONCAT(.  Now though, when you use the Add a column dropdown, the only options you're given are the ones from the Details View. I chose CAMP Object Reference.  When Workbooks adds that to the formula window it displays as GROUP_CONCAT( PARENT('CAMP Object Reference')). This is because it's saying that it's concatenating together the column CAMP Object Reference from the parent view, ie, the Details View.

Then if you open the Criteria tab and click Add calculated criteria.  Just as before, the functionality allows you to include/exclude records that match your required criteria.

I hope this helps.

Kind regards,

Workbooks Support