Report and view with same filter not showing same data - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Tom ShannonTom Shannon 

Report and view with same filter not showing same data

I have a report and a view with the same filter criterion: (custom field) Number of Contacts equals <blank>

The report shows 6 Accounts.  The view shows (correctly) thousands.

I've tried Number of Contacts is < 0, that doesn't work either.

Why are the results different?
View Filter
View Results
Report Filter
Report Results
Arijit MajeeArijit Majee
Hello Tom,

List view and report will give your records if you have access to those records. you can not grill on view with role hierarchy. but report you can view with role hierarchy. can you check the top of the report what is writtem there? are you using role hierarchy or territory hierarchy in your org?
Tom ShannonTom Shannon
We are using role hierarchy.  I do not see anything at the top of the report.  I have access to all records.
Tom ShannonTom Shannon
User-added image
Steve MolisSteve Molis
What Report Type did you use to create that Report?  Also, are you actually running the Report, or are you just looking at the counts in the Report Builder Preview?
Tom ShannonTom Shannon
Accounts and Contacts.  Yes, I am running the report.
Tom ShannonTom Shannon
We were able to create another report in special, custom reporting (new Report Type): Accounts without Contacts using this relationship
User-added image
I'm still curious why the original report and the view, with the exact same filter, have different results.
Steve MolisSteve Molis
I would try creating a new Report, select "Report Type: Accounts" and use a Cross-Filter like this =>

User-added image
Tom ShannonTom Shannon
Yes--that's what we ended up doing.  Still curious why the report filter "Number of Contacts equals <blank>" does not show the same results as a view with the same filter.  Don't mean to harp on this, just curious what the problem is.
Steve MolisSteve Molis
How are you populating the Number of Contacts field?  I would use Tom Tobin's "Power of One" technique => https://success.salesforce.com/0D53000001GwrJW

to get the count of Accounts and Contacts, create a "Contacts and Accounts" Report and select Summary or Matrix as the report format.  Then summarize(sum) your Contacts(Po1) field and your "Number of Contact field side-by-side and look for any that don't match. 
Tom ShannonTom Shannon
I'll try that--I saw that as a possible solution.

In my Sandbox, I created a trigger that increments/decrements the "Number of Contacts" field as Contacts are created/edited/deleted/un-deleted.  I have another problem that is blocking deployment, so I created the field and populated it manually in Production by exporting all Contacts with an AccountID to Excel, summing by AccountID, then importing back to update the field.

Those Accounts with no Contacts did not get updated and the Number of Contacts field should be null/blank.  Again--and I'm sorry to keep at this--I'm most concerned that the same filter produces different results in a view vs. a report.  That really seems like a flaw.
Steve MolisSteve Molis
Methinks your Trigger is on the blink.  I would do that "Power of One" Report post haste 
Tom ShannonTom Shannon
Trigger is not being used in Production.  I did it manually.
Tom ShannonTom Shannon
Disregard everything about the trigger.

I created a number field on Accounts.  I populated that field only on a fraction of the total number of Accounts.

When I create a report checking for the newly-created number field equals <blank>, I get one set of results.  When I create a view checking for the newly-created number field equals <blank>, I get a vastly different set of results.

Why?
Ezra KenigsbergEzra Kenigsberg
Hi Tom--

I ran into the same problem, and I've got an answer for you: salesforce.com decided to change how filters behave in outer join reports.

Quoting salesforce.com's (excellent!) former analytics Program Manager, Tom Tobin: "salesforce.com has decided to change the packaging, and exception reporting will be possible using the Cross-filters part of the Enterprise Analytics features."

https://success.salesforce.com/ideaView?id=08730000000Brs0AAC

I
 agree with you--Outer Join reports now filter in a way that doesn't make intuitive sense to me--but that's what they've decided to go with.
Richa MidhaRicha Midha
Did anyone find the answer for this ? I see the details of th idea that is posted but it should show some extra data and not the less data.Any help will be appreciated!
Ezra KenigsbergEzra Kenigsberg

@Richa Midha

The short answer: the View and the Report are working from two different sets of Accounts.

The longer answer: Tom's original Report was run on the "Contacts & Accounts" Report Type, which ONLY shows those Accounts that have Contacts. (I don't know why he got six Accounts where the manually-populated, custom "Number of Contacts" field is zero or null. I think he should've gotten zero Accounts. I assume there was an error populating the field somewhere.) The View looked at all Accounts.

The nerdier answer: Know the difference between inner joins (like a Report built on the standard "Contacts & Accounts" Report Type) and outer joins (like the Cross-Filter Report that SteveMo built).

The upshot: ALWAYS SANITY-CHECK ANYTHING A COMPUTER TELLS YOU :-)