Report Accounts without open activities

Question

I'm building a report to show all accounts without open activities. It's:

• Accounts with or without related records from Activities

• with filter activity status equals Completed.

I don't understand. In the report there are also accounts with activity with status not equals to completed. Do you Know why?

UPDATE TO THE QUESTION:

Now it works and i want that each account appears just one time in the report(See in the picture Grand Hotel..). This account hasn't open activities but it has 2 closed activities.

Do you Know how archive this?

Thanks in adavantage for any advice.

Answer 1

You can do this with the new Cross Filter feature, described here and here's a short demo.

The report would start with the standard Accounts report, then you'd add a cross filter for "without Activities", and even further define filters on the Activity object.

It will look like this when you're done:

Answer 2

A report that uses "with or without" is a left semi-join. In SQL, you can run semi-joins to get all relevant information from two tables where data may not be present in one table.

For example, to get all accounts and any open orders, you would run the following query:

SELECT `Account`.`Id`, `Account`.`Name`, `Order`.`Id`, `Order`.`OrderNumber`,`Order`.`Date`
FROM   `Account` LEFT JOIN `Order`
ON     `Account`.`Id`=`Order`.`AccountId`
WHERE  `Order`.`Status` = "Open"

Your query result would look something like this:

Id     Name     Id    OrderNumber     Date
1      Acme     2     123             2012-02-09
1      Acme     4     986             2011-10-12
2      GM       NULL  NULL            NULL
3      IBM      1     897             2013-09-12

The exact order may vary, but the principle remains solid. A left semi-join returns "with or without," even if the criteria specifies data from the right-hand table (in this case, Order).

What you're actually looking for is an anti-join, which unfortunately can't be built in the report builder. You'll need to look into a Visualforce report engine or an external platform, such as Crystal Reports. You can also express anti-joins in the Apex Data Loader.