53

Is there any way to access existing reports and their data to be used in apex? Sure, I could write the SOQL manually but if I want it to mirror a specific report I would have to make code changes anytime the report was updated.

I don't think it can currently be done but I'd love to be wrong.

51

Update for Winter '14

API:
I think the biggest announcement that developers have been waiting for API wise is the availability of our Analytics API. We introduced a limited pilot in summer 13 and now the Analytics REST API is generally available. The Analytics API lets you integrate Salesforce report data into your apps programmatically and has several resources that let you query metadata, and record details.
Source - Winter ’14 Developer Preview

The Salesforce1 Reporting REST API

Run Reports in Apex


Update with Lightning enabled

Stick with the official API as above where possible. The direct URL access from below is now breaking when Lightning is enabled in an org as the HTML content that comes back is instead a redirect into Lightning. E.g.

window.location.href ='https://na1.lightning.force.com/one/one.app#/alohaRedirect/00O?export=1&enc=UTF-8&xf=xls&rt=48';


In the past I have used a bit of a grey area (not an official / supported API) to pull the list of reports and then the corresponding report data.

While it worked well, it was to be part of a product that people would purchase. In the end the feature never saw the light of day as it seemed like we might end up talking to Salesforce lawyers.

I was working from .NET, but you should be able to to make the same requests from apex.

Firstly, you can get a list of reports in XML from: https://instance.salesforce.com/servlet/servlet.ReportList (replacing instance with the actual server instance, such as na1)

Note: Not all session types will have sufficient access to this URL. For instance, an API only session will return: <?xml version="1.0"?><result>Invalid session.</result>

Once you have extracted the required report Id (00O key prefix) you can then request the report data in either CSV or HTML.

As for working with the CSV output, I found Parse a CSV with APEX but I haven't tested it yet.

If you want to get fancy, you can set filters for the report too. See BUILDING SALESFORCE CUSTOM LINKS - Linking to Reports for the pv0, pv1... query string parameters.

Reference:

  • when I tried it in the past I didn't get very far. You just get a raw data dump for the most part right? a lot of the nifty features are missing, IMHO better to just to query from SOQL, or batch up some summary reports. – ebt Aug 13 '12 at 11:32
  • 1
    You can also get a list of Reports and their Ids from SOQL by querying the Report object. More straightforward than the servlet.ReportList approach. – zachelrath Jan 3 '13 at 11:42
  • 1
    @user1986059 It is still working for me. I browsed to a random report and then appended '?export=1&enc=UTF-8&xf=csv` to the end of the URL and the CSV was sent back. Maybe the report has been changed in some way? – Daniel Ballinger Dec 16 '14 at 1:56
  • 1
    Http h = new Http(); HttpRequest request = new HttpRequest(); request.setHeader('Authorization','Bearer '+sessionId); ** **request.setMethod('GET'); request.setHeader('Cookie','sid='+sessionId); request.setEndpoint(instanceUrl+'/00O?{url}?export=1&enc=UTF-8&xf=csv'); HttpResponse res1 = h.send(request); . Am I missing something here. I cross checked with instanceUrl and it is correct. Also it works for reportID. – Smitha Feb 2 '16 at 7:49
  • 1
    @DanielBallinger just an update for LEX - the CSV & HTML link you provided in your answer will still work in LEX if you append the isdtp=p1 parameter. I suggest testing it yourself and maybe updating your answer :) – smukov Oct 25 '17 at 8:55
5

No, currently there's no API to access the results of running a report.

  • 4
    Just a note for the rookies who find this answer, currently as of Sept 2014 there is API access to reports. - salesforce.com/us/developer/docs/api_analytics/index.htm – Joshua Dance Sep 26 '14 at 18:05
3

Just to clarify for fellow rookies how will find this, when the question was asked you could access your report data programatically, but you had to use some hacky, and prone to errors methods.

This is all fixed you can now access your reports via the API. Winter '14 release added API access to Reports.

Documentation here - http://www.salesforce.com/us/developer/docs/api_analytics/index.htm

Go to town on those custom dashboards etc.

2

Yes, but no.

Everything you need to know about the report is available via the metadata API. So, technically you could use that and generate the result set based on what the metadata api tells you about the report.

However, as far as I am aware, there is no existing tool to do this. So, you would need to implement it yourself.

  • 2
    there's 2 issues with trying to do this, (i) the metadata API uses zip files, for which there are no way to access the contents of from apex and (ii) the reports data model is not the same as soql, not every report is translatable into soql. – superfell Aug 12 '12 at 2:02
0

Yes it is possible and it's pretty easy too!

    System.debug('describeReport: '+Reports.ReportManager.describeReport(reportId));

    //Reports.ReportResults results = Reports.ReportManager.runReport(reportId, true); // with detail
    Reports.ReportResults results = Reports.ReportManager.runReport(reportId); // without detail
    System.debug('runReport results: '+results);
    Reports.ReportFact fact0T = results.getFactMap().get('0!T');
    System.debug('getFactMap(0!T): '+fact0T);
    LIST<Reports.SummaryValue> fact0Taggregates = fact0T.getAggregates();

    // from run to run the report will not have the same "sections" in its grouping...so we can't but sure that the key 0_3!T will always contains the Feb 2017, Proposal/Price Quote
    label1 = fact0Taggregates[0].getLabel();
    value1 = (Decimal)fact0Taggregates[0].getValue();
    label2 = results.getFactMap().get('1!T').getAggregates()[0].getLabel();
    value2 = (Decimal)results.getFactMap().get('1!T').getAggregates()[0].getValue();
    label3 = results.getFactMap().get('0_3!T').getAggregates()[0].getLabel();
    value3 = (Decimal)results.getFactMap().get('0_3!T').getAggregates()[0].getValue();

    // we can rely for sure on this way to get what we want in the report
    Reports.Dimension groupingDownDim = results.getGroupingsDown();
    List<Reports.GroupingValue> firstLevelGrouping = groupingDownDim.getGroupings();

    // first level of grouping
    for (Reports.GroupingValue gv : firstLevelGrouping) {

        String key1;
        if (gv.getLabel() == 'February 2017') {

            key1 = gv.getKey();
            labelForSure1 = results.getFactMap().get(key1+'!T').getAggregates()[0].getLabel(); // will always contains Feb 2017 total

            // second level of grouping
            List<Reports.GroupingValue> secondLevelGrouping = gv.getGroupings();
            for (Reports.GroupingValue gv2 : secondLevelGrouping) {

                if (gv2.getLabel() == 'Proposal/Price Quote') {

                    String key2 = gv2.getKey();
                    labelForSure3 = results.getFactMap().get(key2+'!T').getAggregates()[0].getLabel(); // will always contains the Feb 2017, Proposal/Price Quote
                    break; // I don't have all night!
                }
            }
        } else if (gv.getLabel() == 'January 2017') {

            key1 = gv.getKey();
            labelForSure2 = results.getFactMap().get(key1+'!T').getAggregates()[0].getLabel(); // will always contains Jan 2017 total
            break; // I rest my case!
        }
    }

Ref:

-1

Yes ... and it works fine !

http://brendansterne.com/2012/12/11/download-scrape-salesforce-reports-in-4-lines-of/

The solution above use's Python and the lib "Requests".

You can found the lib and its installation instructions here : http://docs.python-requests.org/en/latest/user/install/

It is very easy to install and to use.

Enjoy !

  • The question specifically says he wants to access the data in Apex, not Python. – Daniel Hoechst Oct 8 '14 at 21:58
  • Sorry : I was focused on "Can report data be accessed programatically?" – Fabrice Oct 9 '14 at 7:51

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.