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
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
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.
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.
- CSV https://instance.salesforce.com/00Ox0000000xxxx?export=1&enc=UTF-8&xf=csv
- HTML https://instance.salesforce.com/00Ox0000000xxxx?export=1&enc=UTF-8&xf=xls (Last time I checked this was HTML rather than a native Excel file format. Excel does the conversion when it is opened).
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.
- Progmatic Access to Salesforce.com Reports This post was from 2006, but shows that the approach has been working for a several years now. That still doesn’t mean it might not work tomorrow.