Salesforce Developer Reports

salesforce-developer

How can we create a report from a SOQL statement?

What are my options?

  1. Because we know the ID of the report, we can perhaps make a GET / REST request to /REPORTID and see if we can grab the "Grand Totals (259,093 records)" line
  2. Create a tabular report upfront in salesforce with some filters whose values can be passed dynamically from link. And you can embed the link in your email template. <a href="https://cs21.salesforce.com/00Oq0000000YUvUEAW?pv0=" + "report filter value" + "&pv1=2018-01-01">Tabular report Name</a>. Or, you can create a formula field which will show hyperlink to this report and use that formula in email template. pv1 is just the second report filter; if your prebuilt report needs different filters, use pv0, pv1, pv2, etc. Read up on URL hacking reports.

How can we create a report from a SOQL statement using the 'visualforce page' approach?

This does not really create a saved report like a normal report in Salesforce. However, it may just be a 'good enough' band-aid. We need one controller class and two Visualforce pages.

Your controller:

public class MyApexClass{
    public Opportunity[] GetOpportunityList() {
        Opportunity[] opps = [
            SELECT name,account.name,amount,owner.name 
            FROM Opportunity
            LIMIT 10
        ];
        return opps;
    }

    public pageReference generateReport() {
        return Page.secondPage;
    }
}

Your fist page:(the button is in this page.)

<apex:page controller="MyClass">
    <apex:form>
        <apex:commandButton value="Generate Report" action="{!generateReport}"/>
    </apex:form>
</apex:page>

Your second page:(this is the report.Name this page as "secondPage")

<apex:page renderAs="pdf" Controller="MyClass">
    <apex:datatable value="{!opportunityList}" var="opp" columnsWidth="25%,25%,25%,25%">
        <apex:column value="{!opp.name}" headerValue="Name"/>
        <apex:column value="{!opp.amount}" headerValue="Name"/>
        <apex:column value="{!opp.account.name}" headerValue="Account Name"/>
        <apex:column value="{!opp.owner.name}" headerValue="Owners Name"/>
    </apex:datatable>
</apex:page>

See https://jpescador.com/blog/create-reports-using-javascript-remoting-and-datatables/

What are the current limitations of the Salesforce Report and Dashboard API?

  1. Cross filters, standard report filters, and filtering by row limit are unavailable when filtering data.
  2. Historical trend reports are only supported for matrix reports.
  3. The API can process only reports that contain up to 100 fields selected as columns.
  4. A list of up to 200 recently viewed reports can be returned.
  5. Your org can request up to 500 synchronous report runs per hour.
  6. The API supports up to 20 synchronous report run requests at a time.
  7. A list of up to 2,000 instances of a report that was run asynchronously can be returned.
  8. The API supports up to 200 requests at a time to get results of asynchronous report runs.
  9. Your organization can request up to 1,200 asynchronous requests per hour.
  10. Asynchronous report run results are available within a 24-hour rolling period.
  11. The API returns up to the first 2,000 report rows. You can narrow results using filters.
  12. You can add up to 20 custom field filters when you run a report.
  13. Asynchronous report calls are not allowed in batch Apex.
  14. Report calls are not allowed in Apex triggers.
  15. There is no Apex method to list recently run reports.
  16. The number of report rows processed during a synchronous report run count towards the governor limit that restricts the total number of rows retrieved by SOQL queries to 50,000 rows per transaction. This limit is not imposed when reports are run asynchronously.
  17. In Apex tests, report runs always ignore the SeeAllData annotation, regardless of whether the annotation is set to true or false. This means that report results will include pre-existing data that the test didn’t create. There is no way to disable the SeeAllData annotation for a report execution. To limit results, use a filter on the report.
  18. In Apex tests, asynchronous report runs will execute only after the test is stopped using the Test.stopTest method.

All limits that apply to reports created in the report builder also apply to the API. For more information, see “Analytics Limits” in the Salesforce online help.

How can we determine the ID of a report given its name?

List <Report> reportList = [SELECT Id,DeveloperName FROM Report where DeveloperName = 'Closed_Sales_This_Quarter'];
String reportId = (String)reportList.get(0).get('Id');

How can we run report synchronously?

Reports.ReportResults results = Reports.ReportManager.runReport(reportId, true);

How can we run report asynchronously??

Reports.ReportInstance instance = Reports.ReportManager.runAsyncReport(reportId, true);

How can we obtain the instance ID of a report after running it?

Reports.ReportInstance instanceObj = Reports.ReportManager.runAsyncReport(reportId,reportMetadata,false);
String instanceId = instanceObj.getId();

instanceObj = Reports.ReportManager.getReportInstance(instanceId);
System.assertEquals(instanceObj.getStatus(),'Success');
Reports.ReportResults result = instanceObj.getReportResults();
Reports.ReportFact grandTotal = (Reports.ReportFact)result.getFactMap().get('T!T');
System.assertEquals(1,(Decimal)grandTotal.getAggregates().get(1).getValue());
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License