A customer came to us with the following requirement:

"I want to show any lead that essentially has no Activity with an activity status of "new".  In the sales lead report, I tried to do Activities > Status > does not contain > New, but this does not work. I want to be able to not show the lead if there is a "New" activity." 

Now this is possible by adding a calculated column which identifies such leads, then with a summarised column on a summary view we'll be able to set a criteria to only show leads where a 'New' Activity is not related.  

Begin with a Sales Lead report adding in your appropriate columns, then add the following formula in calculated column:

activities.activity_status NOT LIKE 'New' OR  activities.activity_status IS NULL

This expression evaluates as 1 if the related Activity does not have the status 'New' or 0 if a related Activity does not exist. 

Applying a criteria at the details level will not be sufficient as it will only remove results where there is an Activity related with the status of 'New'. If the Lead has more than one Activity related to it, one with the status of 'New' and one with something else, the the Lead will still stay in our results.

Therefore we need to add a summary view so that we can explicitly restrict our results to only show the desired Leads. 

The trick is to add a summarised column in the summary view. So add a summarised column, select the column as the calculated column from your details view and set the calculate field to minimum, then group by Sales Lead Object reference. 

This will return you a row per Sales Lead and a column which will return a 1 if there exists a related Activity of status 'New', if there does not exist a related Activity of Status 'New' a 0 will be returned. So the final step is to add a calculated criteria on this column so that only results where this equals 0 are returned.