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
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:
Report object. More straightforward than the servlet.ReportList approach.
Jan 3, 2013 at 11:42
isdtp=p1 parameter. I suggest testing it yourself and maybe updating your answer :)
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.
No, currently there's no API to access the results of running a report.
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.
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:
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 !
