function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
jverdjverd 

Figuring out report "Master" table + query()-ing for calculated fields

I'm trying to use a Report's metadata to create a SOQL query that pulls back the same data that's in the report. I'm stumped on what I assume are calculated fields.

The metadata is (with a couple of irrelevant fields removed for brevity)
<Report xmlns="http://soap.sforce.com/2006/04/metadata">
    <columns> <field>SUBJECT</field> </columns>
    <columns> <field>CREATED_DATE</field> </columns>
    <columns> <field>AGE</field> </columns>
    <columns> <field>OPEN</field> </columns>
    <columns> <field>CLOSED</field> </columns>
    
    <format>Tabular</format>
    <name>Total Cases Created</name>
    <params>
        <name>units</name>
        <value>h</value>
    </params＞
    <reportType>CaseList</reportType>
    <scope>user</scope>
    <showDetails>true</showDetails>
</Report>


Problem 1: I see reportType is CaseList, and my human brain can make the leap that the "Master" table (not sure if that's the right term) for this report is Case. How can my code know that though?


Problem 2: I use binding.describeSObject() and DescribeSObjectResult.getFields() to get the field names for Case, there is no Age, no Open, and no Closed. There's IsClosed, which my human brain maps to Closed. And I figure Age is calculated from current time minus CreatedDate and Open is calculated from !IsClosed. But when I try to use, e.g. Age in the query, I get 

select Subject, CreatedDate, Age, IsClosed, Account.Name from
                             ^
ERROR at Row:1:Column:30
No such column 'Age__c' on entity 'Case'.
If you are attempting to use a custom field, be sure to append the '__c' after the custom field name.

 I tried Age__c, but of course that didn't work, because apparently "custom" != "calculated".

So...
  1. Is there a way to find out a Report's Master table, programmaticaly?
  2. Is there a way to get the "real" column name--e.g. IsClosed instead of Closed?
  3. Is there a way to get the calculated fields?

Thanks,
Jeff

SuperfellSuperfell
You're in for a hell of time because reports are built on top of a different metadata structure that SOQL and the rest of the API.
jverdjverd
I kinda suspected that. Is there programmatic access to that model?
SuperfellSuperfell
Nope.
SathyaincampusSathyaincampus

Hi Simon,

 

   Even am facing kinda same problem. Is it by any chance accessible now? i have posted my question in the below url. can you pls help?

 

http://boards.developerforce.com/t5/General-Development/framing-the-soql-from-the-listview-columns-based-on-metadata/m-p/238157#M50645

 

 

Hi Jverd,

 

    did you get an answer for this by any chance??

 

Regards

Sathya
SuperfellSuperfell

There's been no changes related to the reports data model.
SathyaincampusSathyaincampus

Hi Simon,

 

Thanks for your response..

is it the same case for the listview metadata too??

did u get a chance to look at my post at that url?

So if I need to get the columnname mapping for created_Date or core.users.alias or some columns like that?

where can i find the link between these names and the actual field names??? is it not possible at all ? :(

 

Regards

Sathya