8

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.

Improve this question
1
  • I've just hit this too. Why is it "CUST_NAME" for the Name field on a custom object. Baffling naming convention for ther report columnns
    – codeulike
    Feb 14, 2020 at 17:13

1 Answer 1

Reset to default
2

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.

Improve this answer
4
  • 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, 2013 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, 2013 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, 2014 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, 2014 at 21:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .