0

We have an issue where we have to manually click Export Report on SFDC reports though the UI on many reports. These reports have several filters and filter logic such as "(1 or 2) AND (4 AND 1)" and other filters such as ("Status=Completed AND User="First_Last") OR (CloseDate>=TODAY), etc.

Ideally, we want to automate the process of running the report, exporting the data as a xls (excel) format, and saving to a particular location.

The idea was to create a java class that pulls a selective list of reports that we can export into an XML file. Then another java class can then use the XML file to find the filter criteria and logic that can be used to build a SOQL query dynamically so that if in Salesforce the report criteria or filters are updated, we can easily have the java class update without manually updating (basically trying to prevent the hardcoding of soql query).

I am needing assistance with trying to get the appropriate API to use so that when the selective list of reports are pulled, i can parse through the XML file and easy create an Excel file for each report that contains the filter logic and criteria. I imagine i can use the ANT migration tool to generate the xml files.

Any thoughts?

  • Have you considered using a free tool like Jitterbit to generate queries and schedule exports of data? – Jenny B Mar 31 '16 at 0:42
0

You can get the report filter details by passing the folder and report name from metadata api.

Here what I tried in my developer org:

1) Created a report All_Leads inside the folder Sample_Folder with filter condition as created date between 03-15-2015 and 03-31-2016.

enter image description here

Filter conditions that I gave for the report:

enter image description here

2) Created a class named Metadataservice in my developer org and copied the contents from here

3) Added the Remote site settings for my org instance and then executed the below code in anonymous window by passing the folder and report name:

MetadataService.MetadataPort service = new MetadataService.MetadataPort();
service.SessionHeader = new MetadataService.SessionHeader_element();
service.SessionHeader.sessionId = UserInfo.getSessionId();
MetadataService.Report report =
    (MetadataService.Report) service.readMetadata('Report',
                                                  new String[] {'Sample_Folder/All_Leads' }).getRecords()[0];
System.debug(report);

4) Below is the debug log of the response from the metadata call and we can see that Start date and End date from the log are matching with what I entered in the UI.

timeFrameFilter=ReportTimeFrameFilter:
        [apex_schema_type_info=(http://soap.sforce.com/2006/04/metadata, true, false), 
        dateColumn=CREATED_DATE, 
        dateColumn_type_info=(dateColumn, http://soap.sforce.com/2006/04/metadata, null, 1, 1, false), 
        endDate=2016-03-31 00:00:00, 
        endDate_type_info=(endDate, http://soap.sforce.com/2006/04/metadata, null, 0, 1, false), 
        field_order_type_info=(dateColumn, endDate, interval, startDate), interval=INTERVAL_CUSTOM, 
        interval_type_info=(interval, http://soap.sforce.com/2006/04/metadata, null, 1, 1, false), 
        startDate=2015-03-01 00:00:00, 
        startDate_type_info=(startDate, http://soap.sforce.com/2006/04/metadata, null, 0, 1, false)], 
    timeFrameFilter_type_info=(timeFrameFilter, http://soap.sforce.com/2006/04/metadata, null, 0, 1, false), 
    type=Report, 
    type_att_info=(xsi:type), 
    userFilter=null, 
    userFilter_type_info=(userFilter, http://soap.sforce.com/2006/04/metadata, null, 0, 1, false)]

But If you follow this approach there is going to be lot of xml processing in your java application to parse the values.

I guess there might be other elegant solutions than this for what you are trying to do but I was intrigued by your question and wanted to see if that is possible by metadata api.

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.