Unable to filter out blank fields

Question

I've read many topics, but still couldn't solve this problem: I need to create a report that shows number of activities on each account total AND also shows accounts with no activity at all. The problem is, that there are blank records and there's no way to filter them out (I tried everything, that I found). I also found the guy, with the same problem, he was suggested to use a different type of report ("Accounts with activities" instead of "Accounts with or without Activities"). The problem is, that it won't show account with 0 records, which is important. Also, some fields, which I need, aren't there.

Thanks!

Answer 1

So guys on Success Community helped me solve this problem and here's the solution. BTW it's called The "Power of One" technique.

By Geoffrey Flynn:

Let me try an example here. I'm filtering on Account Name just to reduce the list to only two accounts - "Answers" and "Initech". I've grouped by Account Name in a Summary Report using Accounts with and without Activities

So notice that Answers has a record count = 1, but an Activities (my power of one field) = 0. There are no Activities for Answers, but it has a record because your report type is Accounts with/without Activities, so an Account will show up regardless.

The reason it shows up is because if you run that as a Tabular report type, it looks like this:

A tabular report acts like an Excel worksheet. There is a row for every item in the report. So in this case there is a row for Answers even though there is no activity, and 4 rows for Initech - one for each Activity.

So when you Group in a Summary report, that row will still appear, there isn't anything you can do about that.

If you were to filter by Activity fields, both Accounts would still come back, Initech would just show less Activities. Note here that I've filtered out the Activities that have a subject of "Email"

So short answer is that unless you get rid of the Account completely you can't get rid of the blank row because it is still a row of data in the report type. By using a Power of One field you can hopefully explain that it just means there are no Activities for that particular account, for the filters you have set.

If you were to see Answers (2 Records) there - In this use case - it means that you have a duplicate Account Name. Group by Id instead of name and you would see that there is more than one

Thanks again to Geoffrey Flynn!