5

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?

4

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)

| improve this answer | |
  • 1
    Thanks for the detailed response. I was thinking that some sort of translator would be a no-brainer to add to the featureset, although I'm starting to see that people approach from one side or other (UI or SOQL). Thanks for the XML tip - no doubt you've saved us a world of pain! I'll mark this one answered in a few days - just want to hold out in case there's some clever solution... but this answer helps a lot. – David Jun 3 '13 at 13:42
  • Good luck! I'm not aware of any free tool for such Report->SOQL generation, would be nice to have! Not sure how Conga does their magic (appexchange.salesforce.com/…), I'm under impression they are doung something to what you're after - metadata instead of screenscraping of actual report (like I do in salesforce.stackexchange.com/questions/4303/…)... – eyescream Jun 3 '13 at 14:55
  • follow up... I installed force.com IDE and attempted to create a new project... refused access as I don't have write permissions ('Customize All Data' and 'Modify All Data' - though I do have read access when I log into my org on the cloud UI). Can you tell me whether this IDE or another application can give me report access so I can generate XML? I'm very new to salesforce, but ready to learn... and grateful for any details you can include on specific steps. – David Jun 3 '13 at 15:02
  • Tomasz, wanted to thank you for your detailed replies. I got enhanced permissions and downloaded the XML and the first report was a snap... hopefully the others will be as well! – David Jun 5 '13 at 18:46
  • I'm happy to hear that, good luck David! :) – eyescream Jun 5 '13 at 18:51
-3

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 .

| improve this answer | |

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.