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.