What SOQL does salesforce.com use to execute a report?

[gmcinnes]

Hi all:

I'm a relative newb. I'm building an integration with salesforce.com via the SOAP API. I've reached a sticking point.  There is a report that  I need to access via the API.  I understand I can't access this directly, but the preferred approach is to mimic the SOQL that the report would be running, and generate the same data set that way.  

This seems straight forward, but I can't seem to make my SOQL get the same number of results as the report in salesforce.com.  Attached is a screen capture from the report page, as well as the SOQL I'm trying to use to generate the same data set.

SELECT Count(Id), (SELECT Contact.Name FROM Account.Contacts WHERE Contact.MaRS_Contact_Relationship__c INCLUDES ('Advisory Services Primary Contact') AND Contact.Inactive_Person_left_company__c = false) FROM Account WHERE MaRS_Company_Relationships__c INCLUDES ('Active VG Client', 'High Potential Client', 'SiG Client')

Can anyone see how these differ?  Or do you know how, in general, to get the SOQL that the report is executing?

[Andrew Wilkins… · Accepted solution]

The only thing I can think of is the outer query is only filtering on the account. Try doing it from the child object...

SELECT Count(Id),Name FROM Contact WHERE Account.MaRS_Company_Relationships__c INCLUDES ('Active VG Client', 'High Potential Client', 'SiG Client') AND MaRS_Contact_Relationship__c INCLUDES ('Advisory Services Primary Contact') AND Inactive_Person_left_company__c = false GROUP BY Name

[*werewolf*]

If it makes you feel better, reports aren't run in SOQL.  They get translated directly into SQL in the underlying database, so they can sometimes do things you can't do in SOQL.

[gmcinnes]

Thanks so much for taking the time to respond!  

I tried this, but it still doesn't get me to the magic number of records.  When running the report in Salesforce it finds 1,204 records.  When running my previous report I get 1,555.  This was a good idea, but running this gets me 1,062 records, so this isn't getting it either.  I'm stumped.

[gmcinnes]

Oh. Actually, no. That looks like it was exactly it Andrew.  The continuing discrepency was due to a difference between my prod and sandbox instances.

Although I think I understand it, can you expand on "the outer query is only filtering on the account"  Do you mean that in the first query I may have been getting "extra" Account objects that don't have child "Contacts" and that's what made the difference?

[Andrew Wilkins…]

Exactly. THE sub query was returning no results but the outer query was still returning the accounts whether or not they had contacts matching the criteria.

[gmcinnes]

Ah ha!  Ok.  Then my query is exactly what is wanted, and my collaborator's report is incorrect :)

Thanks so much for your help!

[Andrew Wilkins…]

No problem. If you mark the post that has the query in it for everyones benefit that would be great.