How to I access reports programmatically in SalesForce using Apex

Question

I'm trying to write an app on the SalesForce platform that can pull a list of contacts from a report and send them to a web service (say to send them an email or SMS)

The only way I can seem to find to do this is to add the report results to a newly created campaign, and then access that campaign. This seems like the long way around.

Every post I read online says you can't access the reports through Apex, however most or all of these posts were written before Version 20 of the API was released last month, which introduced a new report object. I can now programmatically access info about a report (Such as the date last run etc) but I still can't seem to find a way to access the result data contained in that report.

Does anyone know if there's a way to do that?

Answer 1

After much research into it, I've discovered the only way to do this at the moment is indeed to scrape the CSV document. I would guess that Conga etc are using exactly this method.

We've been doing this for a while now, and it works. The only caveats are:

• Salesforce username / password / security token has to be shared to the app connecting. If the password changes (and by default it is changed every 30 days or so) the token also changes and must be re-entered.

• You have to know the host of the account, which can be difficult to get right. For instance while most european accounts would use emea.salesforce.com to access CSV, our account uses na7 (North America 7) even though we're located in ireland. I'm currently sending the page host to the app and parsing it to calculate the correct subdomain to use, but I think there has to be a better way to do this.

Salesforce really needs to sort this out by supplying an API call which allows custom report results to be exported on the fly and allowing us to use OAuth to connect to it. But of course, this is unlikely to happen.

Answer 2

In the SalesforceSpring 11 update, it seems you can obtain more informations about the Reports: As stated in the API for Report and ReportType, you can access via Apex the fields used in the query by the Report, reading the field "columns", as well as the field used to represent the filters called "filter".

Iterating through this objects, should allow you to build a String representing the same query of the Report. After building that string you can make a dynamic query with a Database.query(..) call.

It seems to be a little messy, but should work.. (NOT TESTED YET!)

As header states, this works only with Custom Reports!