5

I know there's been several topics like that, but none of them has any usable answers, so I'm starting a new one. My current task is to build a tool that lets the user select any of the existing reports, click a button and generate a SOQL query that would grab the same data as the report. I'm using .NET and Metadata API. I've been able to do the report selection part and retrieve the report XML. However, there's not enough information in the XML to build a query. I've created a sample report called Activities with Car Orders (Car Order is a custom object). Here's the xml:

<?xml version="1.0" encoding="UTF-8"?>
<Report xmlns="http://soap.sforce.com/2006/04/metadata">
    <columns>
        <field>SUBJECT</field>
    </columns>
    <columns>
        <field>CUST_NAME</field>
    </columns>
    <columns>
        <field>Car_Order__c.Model__c</field>
    </columns>
    <filter>
        <criteriaItems>
            <column>Car_Order__c.Make__c</column>
            <operator>equals</operator>
            <value>Acura</value>
        </criteriaItems>
    </filter>
    <format>Tabular</format>
    <name>test report 5</name>
    <params>
        <name>closed</name>
        <value>open</value>
    </params>
    <params>
        <name>type</name>
        <value>te</value>
    </params>
    <params>
        <name>co</name>
        <value>1</value>
    </params>
    <reportType>ActivityCustomEntity$Car_Order__c</reportType>
    <scope>organization</scope>
    <showDetails>true</showDetails>
    <timeFrameFilter>
        <dateColumn>DUE_DATE</dateColumn>
        <interval>INTERVAL_CUSTOM</interval>
    </timeFrameFilter>
</Report>

My problems start right at the field level. How do I know that SUBJECT field is on the Activity object? I can kinda get it from the report type, but what is 'ActivityCustomEntity$Car_Order__c'? How would the report type name be built if there's 3 or 4 related objects in the report? Then goes CUST_NAME. I know that it represents the standard 'Name' field on my custom object, but again, what if there's more than one custom object in the report type? There's such a multitude of possibilities here that I am scratching my head trying to understand if it's even possible to build a reliable logic that would grab that xml and produce a SOQL. Information on this topic is extremely scarce in the Internet, although I have a feeling that I'm reinventing the wheel here. Somebody must have done this before, there's no way I'm the first one out there with this task.

1

http://www.salesforce.com/us/developer/docs/api_analytics/salesforce_analytics_rest_api.pdf

Use the new Analytics API instead. Select the report and return the resulting data in JSON format. Parse that with your .NET application.

  • Thanks Jim, I've been playing with Analytics API for a while. One thing I found out is the returned json describing the report metadata is exactly the same as the report metadata I get in xml format using Metadata API. So at the end of the day I'm still stuck with not understanding the parsing rules for this metadata. I searched, but wasn't able to find any documentation describing how they build the field names and determine which object the field belongs to. – taralex Dec 29 '13 at 17:48
  • Got Analytics API working, the only caveat is that it returns maximum 2000 rows of data. Can't be used for large reports, unfortunately. – taralex Dec 31 '13 at 3:01
  • 1
    There is a 2000 row limit on tabular reports when run via the UI as well. So the API returns the same result set. In the UI, the user has to export details to get more than 2000 records. If you need all of the results, you could write your own parser on the export. Use the URL of the report and add ?export=1&enc=UTF-8&xf=CSV to the end. – JimRae Jan 1 '14 at 16:39
  • Thanks, Jim! I think just exporting reports as csv in the background is the way to go! And I won't have to parse cryptic xml files that Analytics API returns.. – taralex Jan 13 '14 at 21:08

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.