Report on Account to show number of contacts with email addresses

[qpoint]

I would like to create a report of Accounts and show the number of Contacts with defined email addresses (i.e. email address not blank). The primary use case would be to identify companies for a team to work on (to obtain email addresses). We have Advanced Pack.

My main question here is whether it's possible to do this, and if so, some suggestions on how to set up the filter.

Thanks

[victor]

Hi qpoint,

If I understood you correctly, you need to specify in the created Accounts Report in Filters: Contacts.Email > Not Empty.

[qpoint]

Breaking this down...
1) The List - goal is a List of Accounts, with a column that shows a COUNT= number of contacts with email addresses.

2) How to filter. A list filter on Contacts.Email = Is Empty will show any Account with at least 1 Contact with an empty email address. Run time filter would be to set a threshold, e.g. only Accounts with zero Contact email addresses.

[qpoint]

Found out a way to do this. Was trying to do this with just a Report and a Report filter, but couldn't figure it out.
So instead, I did it with a custom field and a formula that counts the number of contacts with email addresses on the account.

The steps:
On the Account Entity, create the custom field nContactsWithEmailC as an Integer.

Create a Report that filters on Contacts with Email addresses - a list report on Contact entity, with filter of Email Is Not Empty.

Create a Report Filter that references this report. It is needed for the forthcoming formula - in Administration, choose Report Filters. Create the report filter, choose the Report above. Once saved, the url shows the id of the Report Filter.

Create a formula on the Account Entity. For the FILTER, reference the Report Filter id (prefixed by reportFilter).

// Calculates the number of Contacts on this Account with email addresses.
nContactsWithEmailC = entity\countRelated('contacts', 'reportFilter63477053ca7bc2502');

Recalculate the formula so the field is updated - view Accounts and filter to desired subset. Then under the Name, choose Select All Results, Actions, Recalculate Formula.

Create the Report - now it's just a simple List report on the Account entity. Include the custom field nContactsWithEmailC.

There may be a simpler way to do this. Comments welcome.

[rabii]

thanks for sharing this. another way would just replace the report filter with a custom primary filter and then use that filterName in the formula. just to avoid having a report changed or deleted.