Sign Up ›
  • jverd
  • NEWBIE
  • 0 Points
  • Member since 2008
  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 7
    Questions
  • 15
    Replies
6 replies

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

  • December 10, 2008
  • Like
  • 0
4 replies

Trying to logout gives No operation available for request {urn:enterprise.soap.sforce.com}logout

I assume the problem has to do with how I'm setting the session header after logging in.

Code:
public class LoginLogout {
  private static final Scanner scanner = new Scanner (System.in);

  public static void main (String... args) throws Exception {
    final SoapBindingStub soapBinding = (SoapBindingStub) new SforceServiceLocator ().getSoap ();
    final LoginResult loginResult;
    final String userName = getInput ("User");
    final String password = getInput ("Password");

    soapBinding.setTimeout (60000);
    loginResult = soapBinding.login (userName, password);

    if (loginResult.isPasswordExpired ()) {
      System.out.println ("An error has occurred. Your password has expired.");
      throw new ServiceException ("Login failed. Password expired");
    }

    final SessionHeader sh = new SessionHeader ();
    sh.setSessionId (loginResult.getSessionId ());

    soapBinding._setProperty (SoapBindingStub.ENDPOINT_ADDRESS_PROPERTY, loginResult.getMetadataServerUrl ());
    soapBinding.setHeader (new SforceServiceLocator ().getServiceName ().getNamespaceURI (), "SessionHeader", sh);

    soapBinding.logout ();
  }

  private static String getInput (final String prompt) {
    System.out.println (prompt + ": ");
    return scanner.nextLine ();
  }
}



Exception in thread "main" AxisFault
 faultCode: {http://schemas.xmlsoap.org/soap/envelope/}Client
 faultSubcode: 
 faultString: No operation available for request {urn:enterprise.soap.sforce.com}logout
 faultActor: 
 faultNode: 
 faultDetail: 
 {http://xml.apache.org/axis/}stackTrace:No operation available for request {urn:enterprise.soap.sforce.com}logout
 at org.apache.axis.message.SOAPFaultBuilder.createFault(SOAPFaultBuilder.java:222)
 at org.apache.axis.message.SOAPFaultBuilder.endElement(SOAPFaultBuilder.java:129)


etc. ...


 

  • December 09, 2008
  • Like
  • 0
7 replies

How do I get report data (not metadata) through Web Services API?

I'm not sure if this is the right place to ask this--I'm still not sure about the relationships amon Apex, AppExchange, Force.com, and the Web Services and Metada APIs.

I want to get a report's data through the Web Services API. I'm coding in Java, but my understanding is the implementation language shouldn't matter.

As near as I can tell, what I'll have to do is first get the report's metadata with the Metadata API, then use that to construct a SOQL query that I'll execute to get the report data. Is this the only way, or is there a more direct way through the Web Services API to just say, "Run this report and give me the results?" I've been studying the API for a couple of days now and haven't found anything, but I'm hoping I missed something.

Alternatively, if I schedule a report through the website, is it possible to retrieve its results via the API?

Thanks in advance for any light you can shed on this subject.

Jeff

  • December 08, 2008
  • Like
  • 0
0 replies

How do I list reports/folders I didn't create

Using the metadata API, I'm able to list folders I created, and reports I created in folders I created, but I've not been able to figure out how to list "standard" folders and reports--that is, those that I can see from day one on my account but that I didn't create.

How can I list these "standard" items and retrieve the reports?

Thanks,
Jeff

  • December 02, 2008
  • Like
  • 0
6 replies

End-to-end example: List reports, retrieve report metadata, execute report, retrieve results

I'm beating my head against the wall here trying to achieve the following, using the metadata API:
  1. List reports. I'm able to list reports I created in folders I created, but that's all.
  2. Retrieve a report's metadata. I tried with
    new RetrieveRequest (14.0, null, true, new String[] {"MyFolder/MyReport"}, null);
    But got INVALID_CROSS_REFERENCE_KEY: No packages or unpackaged specified. I'm really not clear on what the sepcificFiles an unpackaged parameters should be.
  3. Run the report. I haven't seen anything that suggests I can even do this, but I'm assuming it must be possible.
  4. Retrieve the results of running the report in some standard format such as CSV or XML. I haven't seen anything that suggests I can even do this, but I'm assuming it must be possible.
I've been using the docs at http://www.salesforce.com/us/developer/docs/api_meta/index.htm, but they're kind of sketchy. Is there more complete documentation somewhere?

Thanks,
Jeff



Message Edited by jverd on 12-01-2008 01:50 PM
  • December 01, 2008
  • Like
  • 0
3 replies

Listing reports through metadata API

Following the metadata documentation, I
  1. Used Axis 1.4 to generate .java files from enterprise.wsdl and metadata.wsdl.
  2. Called SoapBindingStub.login(user, password) to connect.
  3. Listed a report I created in a folder I created with
Code:
String component = "Report";
String folder = "MyFolder";
ListMetadataQuery query = new ListMetadataQuery (folder, component);
FileProperties[] lmr = metadatabinding.listMetadata (new ListMetadataQuery[] {query});


My questions are:
  1. Is it possible to list the folders, or do I have to know what folder I'm looking for reports in? (Setting folder to null and component to "Folder" and calling  listMetadata gave an error, since there's no such component as "Folder."
  2. Alternatiely, is it possible to just list all the reports, without knowing the folders?
  3. Is it possible to list reports that I didn't create in folders that I didn't create? On the website, in addition to my folder/report, I see many others, but when I try to list them with the above (e.g. folder = "Sales Reports"), nothing is returned.
  4. Finally, is ther any more complete documentation than the metadata docs I linked above? I'm finding I have to do a lot of trial and error to fill in holes in those docs.
Thanks,
Jeff


  • December 01, 2008
  • Like
  • 0
SOLVED
7 replies

Looking for quickstart for WSDL + jars needed for report metadata

Hi there,

Apologies if this is not the appropriate board for this topic. I'll be happy to move elsewhere if you let me know where.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 10"><meta name="Originator" content="Microsoft Word 10">

I’m looking for a good quickstart guide to get me going on downloading report metadata, executing reports, and downloading their output.

 

I went to the Quick Start link on http://www.salesforce.com/us/developer/docs/api_meta/index.htm. This in turn told me:

  1. Log in to your Enterprise, Unlimited, or Developer Edition Salesforce account. You must log in as an administrator or as a user who has the “Modify All Data” permission.
  1. Click Setup | Develop | API.
  1. Click the Download Metadata WSDL link and save the XML WSDL file to your file system.
  1. Click the Download Enterprise WSDL link and save the XML WSDL file to your file system.

However, those links were not present on that page.

 

I did poke around and find an enterprise.wsdl somewhere else, but I’m not sure if it’s the correct one. In addition, the quickstart refers to Apache axis 1.3, which seems to no longer be available for download. Should I use Axis1.4? Or Axis2?

 

Bottom line: Is there an up-to-date, accurate quickstart guide for working with reports and their metadata in Java somewhere?

 

Thanks,

Jeff


 


  • November 17, 2008
  • Like
  • 0
6 replies

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

  • December 10, 2008
  • Like
  • 0
4 replies

Trying to logout gives No operation available for request {urn:enterprise.soap.sforce.com}logout

I assume the problem has to do with how I'm setting the session header after logging in.

Code:
public class LoginLogout {
  private static final Scanner scanner = new Scanner (System.in);

  public static void main (String... args) throws Exception {
    final SoapBindingStub soapBinding = (SoapBindingStub) new SforceServiceLocator ().getSoap ();
    final LoginResult loginResult;
    final String userName = getInput ("User");
    final String password = getInput ("Password");

    soapBinding.setTimeout (60000);
    loginResult = soapBinding.login (userName, password);

    if (loginResult.isPasswordExpired ()) {
      System.out.println ("An error has occurred. Your password has expired.");
      throw new ServiceException ("Login failed. Password expired");
    }

    final SessionHeader sh = new SessionHeader ();
    sh.setSessionId (loginResult.getSessionId ());

    soapBinding._setProperty (SoapBindingStub.ENDPOINT_ADDRESS_PROPERTY, loginResult.getMetadataServerUrl ());
    soapBinding.setHeader (new SforceServiceLocator ().getServiceName ().getNamespaceURI (), "SessionHeader", sh);

    soapBinding.logout ();
  }

  private static String getInput (final String prompt) {
    System.out.println (prompt + ": ");
    return scanner.nextLine ();
  }
}



Exception in thread "main" AxisFault
 faultCode: {http://schemas.xmlsoap.org/soap/envelope/}Client
 faultSubcode: 
 faultString: No operation available for request {urn:enterprise.soap.sforce.com}logout
 faultActor: 
 faultNode: 
 faultDetail: 
 {http://xml.apache.org/axis/}stackTrace:No operation available for request {urn:enterprise.soap.sforce.com}logout
 at org.apache.axis.message.SOAPFaultBuilder.createFault(SOAPFaultBuilder.java:222)
 at org.apache.axis.message.SOAPFaultBuilder.endElement(SOAPFaultBuilder.java:129)


etc. ...


 

  • December 09, 2008
  • Like
  • 0
7 replies

How do I get report data (not metadata) through Web Services API?

I'm not sure if this is the right place to ask this--I'm still not sure about the relationships amon Apex, AppExchange, Force.com, and the Web Services and Metada APIs.

I want to get a report's data through the Web Services API. I'm coding in Java, but my understanding is the implementation language shouldn't matter.

As near as I can tell, what I'll have to do is first get the report's metadata with the Metadata API, then use that to construct a SOQL query that I'll execute to get the report data. Is this the only way, or is there a more direct way through the Web Services API to just say, "Run this report and give me the results?" I've been studying the API for a couple of days now and haven't found anything, but I'm hoping I missed something.

Alternatively, if I schedule a report through the website, is it possible to retrieve its results via the API?

Thanks in advance for any light you can shed on this subject.

Jeff

  • December 08, 2008
  • Like
  • 0
6 replies

End-to-end example: List reports, retrieve report metadata, execute report, retrieve results

I'm beating my head against the wall here trying to achieve the following, using the metadata API:
  1. List reports. I'm able to list reports I created in folders I created, but that's all.
  2. Retrieve a report's metadata. I tried with
    new RetrieveRequest (14.0, null, true, new String[] {"MyFolder/MyReport"}, null);
    But got INVALID_CROSS_REFERENCE_KEY: No packages or unpackaged specified. I'm really not clear on what the sepcificFiles an unpackaged parameters should be.
  3. Run the report. I haven't seen anything that suggests I can even do this, but I'm assuming it must be possible.
  4. Retrieve the results of running the report in some standard format such as CSV or XML. I haven't seen anything that suggests I can even do this, but I'm assuming it must be possible.
I've been using the docs at http://www.salesforce.com/us/developer/docs/api_meta/index.htm, but they're kind of sketchy. Is there more complete documentation somewhere?

Thanks,
Jeff



Message Edited by jverd on 12-01-2008 01:50 PM
  • December 01, 2008
  • Like
  • 0
3 replies

Listing reports through metadata API

Following the metadata documentation, I
  1. Used Axis 1.4 to generate .java files from enterprise.wsdl and metadata.wsdl.
  2. Called SoapBindingStub.login(user, password) to connect.
  3. Listed a report I created in a folder I created with
Code:
String component = "Report";
String folder = "MyFolder";
ListMetadataQuery query = new ListMetadataQuery (folder, component);
FileProperties[] lmr = metadatabinding.listMetadata (new ListMetadataQuery[] {query});


My questions are:
  1. Is it possible to list the folders, or do I have to know what folder I'm looking for reports in? (Setting folder to null and component to "Folder" and calling  listMetadata gave an error, since there's no such component as "Folder."
  2. Alternatiely, is it possible to just list all the reports, without knowing the folders?
  3. Is it possible to list reports that I didn't create in folders that I didn't create? On the website, in addition to my folder/report, I see many others, but when I try to list them with the above (e.g. folder = "Sales Reports"), nothing is returned.
  4. Finally, is ther any more complete documentation than the metadata docs I linked above? I'm finding I have to do a lot of trial and error to fill in holes in those docs.
Thanks,
Jeff


  • December 01, 2008
  • Like
  • 0
SOLVED
7 replies

Looking for quickstart for WSDL + jars needed for report metadata

Hi there,

Apologies if this is not the appropriate board for this topic. I'll be happy to move elsewhere if you let me know where.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 10"><meta name="Originator" content="Microsoft Word 10">

I’m looking for a good quickstart guide to get me going on downloading report metadata, executing reports, and downloading their output.

 

I went to the Quick Start link on http://www.salesforce.com/us/developer/docs/api_meta/index.htm. This in turn told me:

  1. Log in to your Enterprise, Unlimited, or Developer Edition Salesforce account. You must log in as an administrator or as a user who has the “Modify All Data” permission.
  1. Click Setup | Develop | API.
  1. Click the Download Metadata WSDL link and save the XML WSDL file to your file system.
  1. Click the Download Enterprise WSDL link and save the XML WSDL file to your file system.

However, those links were not present on that page.

 

I did poke around and find an enterprise.wsdl somewhere else, but I’m not sure if it’s the correct one. In addition, the quickstart refers to Apache axis 1.3, which seems to no longer be available for download. Should I use Axis1.4? Or Axis2?

 

Bottom line: Is there an up-to-date, accurate quickstart guide for working with reports and their metadata in Java somewhere?

 

Thanks,

Jeff


 


  • November 17, 2008
  • Like
  • 0