0

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.

enter image description here

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?

enter image description here

Thanks in adavantage for any advice.

2

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:

enter image description here

  • Great Matthew, you are a wizard!Thanks a lot! It works!! I have just another question.Please could you take a look again to my question.Thanks again!! – Enry Jul 10 '13 at 7:33
  • @Enry: You can't. If you want sums though, search for "salesforce power of one". Otherwise you'll need to use a different tool to remove duplicates. – Mike Chale Jul 10 '13 at 11:34
  • What is the report type you used? If you used Accounts with Activities, then the base row in the report is the Activity row, which means you'll see multiple lines for each Account. Instead, use just the standard "Accounts" report type, and then apply the same filter. The base row of the Accounts report type is Accounts, so you'll only see one line per Account. The cross-filter works the same on both report types. – Matthew Lamb Jul 10 '13 at 15:33
3

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.

  • Thanks, i have tried the solution above and it works! thanks a lot. – Enry Jul 10 '13 at 7:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.