Can report data be accessed programatically?

  • 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.

  • 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 Yes, the above approach just gives you the raw report data which you could then manipulate in apex. I used it successfully to create a .NET wrapper that could browse and pull reports from an org. Alternatively, if you wanted to create an iframe you could embed the report and get all the widgets as well. If neither of those options suit, then yes, straight SOQL would serve you better.

    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.

    @DanielBallinger : This seems to be working until few weeks back. Since few days, the links for report csv/xls export are not working out. Just want to make sure I am not missing anything. Really appreciate if you can confirm. Thanks

    @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?

    Thanks, yes I was referring to a report already deleted(such a mind lag!). Thanks for the response, it is working fine for me too.

    @DanielBallinger : I am trying to get standard report data using the url : **https://instance.salesforce.com/00O?{some url}?export=1&enc=UTF-8&xf=csv**. But I am unable to get the data, instead it returns some data in html which looks like source code of that page. Not understanding where I am going wrong.

    @Smitha Try saving the HTML out to disk and opening it in a web browser. You might find that Salesforce is returning some sort of error message or security redirect. Check your request cookies are correct and the instance URL is correct.

    @DanielBallinger : Thanks for the reply. I tried saving that HTML. There is no error in that. It is nothing but source code of that page. Here is the sample request format :

    **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.

    Got it!!! For standard reports we have to append '?view=d&snip&export=1&enc=UTF-8&xf=csv' at the end of URL.

    Smitha - did you get this working in conjunction with sending an OAuth Bearer token?

    @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 :)

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM