Generate SOQL from an existing report

Question

I have two existing reports that were constructed awhile ago and no one here knows much about, e.g. which objects and fields are involved. Now I want to access this same data through a Python module and SOQL. I have the Python module working, but how can I get the SOQL? Do I have to painstakingly construct it from what I see in the results? Or is there an easy way to get the SOQL?

Answer 1

Tough one.

You can download the XML containing the report's definition either with Eclipse IDE or other Metadata API tool. If you don't have any handy & don't want to install - you can cheat for example by making a changeset that contains it (just upload the changeset to another environment; it can even have this report already, we don't care; all you need is to be able to view XML of uploaded changeset in target org, you don't have to finalize the deployment).

Having the XML handy will save you some trouble as to which objects and which fields are used. Otherwise - especially when the field names would be similar on many involved objects you'd be in world of pain trying to guess CreatedDate of which object should be in this column or that filter... It can also help you a bit to view the report's properties to learn which report type was used to build it.

One way or another - eventually you'll have to manually hand-craft your SELECT statement. Couple of tips:

1. If the report is a summary - you can achieve some interesting results with GROUP BY and GROUP BY ROLLUP(Some_Field__c).
2. If the report filters by date - check out some special date literals.
3. If the report groups something by Date (but say by year & month component of the date instead of actual day) - there are date functions like CALENDAR_MONTH
4. If the report is a matrix one - I think you're in for some post-processing in Python because as far as I know Apex doesn't have nice utility to pivot (flip rows into columns).
5. Amount of result rows returned might bite you in a$$. Check out the queryMore() (for SOAP API) or nextRecordsUrl - it's equivalent in REST API.

---

Edit to answer comment(s):

Have you tried the "dummy deployment of a changeset" path? Although you might need different permissions (http://help.salesforce.com/help/doc/en/changesets.htm). Maybe your SysAdmin could generate xml for you / promote you temporarily. It might also pay off to ask it as a separate question :)

You could try going to https://workbench.developerforce.com, then Migration -> Retrieve and prepare "package.xml" - an XML file that says which other XMLs you want to work with ;)

This package.xml could look similar to

<?xml version="1.0" encoding="UTF-8"?>
<Package xmlns="http://soap.sforce.com/2006/04/metadata">
    <types>
        <members>Some_Reports_Folder</members>
        <members>Another_Folder</members>
        <members>Another_Folder/Some_Rep1</members>
        <members>Another_Folder/Some_Rep2</members>
        <members>unfiled$public</members>
        <members>unfiled$public/SampleReportActiveUsers</members>
        <members>unfiled$public/SampleReportCasesStatusbyRep</members>
        <name>Report</name>
    </types>
    <version>26.0</version>
</Package>

(you'll need to click "Properties" on your report to learn what's its name (the one with underscores) and possibly also folder name)

Answer 2

You can retrieve Salesforce Object information (such as fields, object relationships and such) either from:

• Your company's organisation;
• Force.com IDE Salesforce Schema;
• Some external tool, such as Force.com Explorer;
• SObject describe information.

If you can login through Python, you probably have all nedded credentials to login to any of these methods.

Once you know enough about whichever object you want to query, do so according to the Force.com SOQL and SOSL Reference .