I have a report with more than 2000 recodes and scheduled weekly. As per Salesforce standard limitations we get only 2000 records to our email. Is there any possibility to get more records? please help me. thanks in advance
[SalesForce] How to get more than 2000 records in case of schedule reports to our email in Salesforce
Related Solutions
Here's what I've just finished building. It's a wall of code but honestly I don't know how to cut it down ;) Flexible enough for my current needs, might have to be improved in future...
1. Controller
/* Allows programmatic retrieve of data generated by any report for further processing in Apex or attaching to emails.
Uses Page.getContent() by default but in future/scheduled context will fall back to REST GET (remember to add "Remote Site Setting" if you plan to use it like that).
Use with care, you might still hit heap size limits with bigger reports!
Of course it can fail anytime Salesforce changes the layout of report pages, their output or parameters that can be passed via URL etc.
*/
// TODO: add support for query string pv0 etc. params for runtime filtering?
public with sharing class ReportExportController {
public static final Set<String> validEncodings = new Set<String>{'UTF-8','MS932','Shift_JIS','GB18030','GB2312','Big5','EUC_KR','UTF-16','Big5-HKSCS','x-SJIS_0213'};
public static final Set<String> validformats = new Set<String>{'printable','csv','xls'};
public static final Set<String> validShowDetails = new Set<String>{'yes','no',null};
public static String mockOutput; // to be used in unit test. getContent() can't be called in test,
// REST way means need to make a mock anyway (but then you'd need remote site setting for both production and all possible sandboxes...)
// Basic options.
public Id reportId {get;set;}
public String format {get;set;} // See list above for available options
public Boolean prettify {get;set;} /* If set we'll attempt to return only the core info without:
- headers: who run it, when, which company, what's the report title, filters used
- footers: "Confidential Information - Do Not Distribute" etc.
*/
// Option for format = 'printable' only
public String showDetails {get;set;} // like "show details" button. See list above for available options; null = fallback to however the report was saved
// Option for format 'csv' and 'xls'
public String encoding {get;set;} // See list above for available options
private String requestUrl, output;
private Boolean restRequestNeeded;
public ReportExportController(){
prettify = true;
restRequestNeeded = System.isFuture() || System.isScheduled(); // System.isBatch() too?
}
public String getOutput(){
if(requestUrl == null) {
getRequestUrl();
}
fetchData();
if(prettify && output != null && output.length() > 0){
prettify();
}
return output;
}
public String getRequestUrl(){
sanitizeInput();
requestUrl = '/' + reportId + '?';
if(format == 'printable'){
requestUrl += 'excel=1' + (showDetails != null ? '&details=' + showDetails : '');
} else {
requestUrl += 'export=1&xf=' + format + '&enc=' + encoding;
}
if(restRequestNeeded) {
requestUrl = URL.getSalesforceBaseUrl().toExternalForm().replace('http:', 'https:') + requestUrl;
}
return requestUrl;
}
private void sanitizeInput(){
if(reportId == null){
throw new ReportExportException('Missing argument: reportId');
} else if(!String.valueOf(reportId).startsWith('00O')) {
// Length - based check can be skipped - it will fail on attempt to assign too long string to Id variable
throw new ReportExportException('Suspicious reportId: ' + reportId + '. Were you trying to export something other than a report?');
}
if(encoding == null || !validEncodings.contains(encoding)){
encoding = 'UTF-8';
}
if(format == null || !validformats.contains(format)){
format = 'xls';
}
if(!validShowDetails.contains(showDetails)){
showDetails = null;
}
}
private void fetchData(){
if(Test.isRunningTest()){
output = mockOutput;
} else if(restRequestNeeded){
HttpRequest req = new HttpRequest();
req.setEndpoint(requestUrl);
req.setMethod('GET');
req.setHeader('Cookie','sid=' + UserInfo.getSessionId());
output = new Http().send(req).getBody();
} else {
output = new PageReference(requestUrl).getContent().toString();
}
}
private void prettify(){
Integer startIndex = 0;
Integer stopIndex = output.length();
if(format == 'csv'){
stopIndex = output.lastIndexOf('\n\n\n'); // "lastindexOf" because we look for last occurence of 3 x "Enter". So it should work even if there's data in report (textarea?) that contains such line
} else {
String endToken = '';
if(format == 'xls'){
startIndex = output.indexOf('<table>');
endToken = '</table>';
} else if(format == 'printable'){
startIndex = output.indexOf('<div id="fchArea">');
endToken = '</table>\n</div>';
}
stopIndex = output.lastIndexOf(endToken) + endToken.length();
}
if(startIndex != -1 && stopIndex != -1) {
output = output.substring(startIndex, stopIndex);
}
}
public class ReportExportException extends Exception{}
}
2. Component
<apex:component controller="ReportExportController" access="global">
<apex:attribute name="reportId" description="Id of the report to be retrieved." type="Id" assignTo="{!reportId}"/>
<apex:attribute name="format" description="'printable', 'csv' or 'xls'. Corresponds to 'Printable view' or 'Export Details' options."
type="String" assignTo="{!format}"/>
<apex:attribute name="prettify" description="Select if you want to try cutting out headers and footers." type="Boolean" default="true" assignTo="{!prettify}"/>
<apex:attribute name="encoding" description="Text encoding (UTF-8 etc)." type="String" default="UTF-8" assignTo="{!encoding}" />
<apex:attribute name="showDetails" description="If 'printable' is used - choose if you want to override report's show/hide details setting. Use 'yes' or 'no'"
type="String" assignTo="{!showDetails}" />
<!-- {!requestUrl} -->
<apex:outputText value="{!output}" escape="false" />
</apex:component>
3. Sample email template
<messaging:emailTemplate subject="{!relatedTo.Name} Data Export" recipientType="Contact" relatedToType="Account">
<messaging:plainTextEmailBody >
Dear {!BLANKVALUE(recipient.Name, 'Sir or Madam')},
please find attached the report(s) you have requested...
Kind regards,
{!$Organization.Name}
</messaging:plainTextEmailBody>
<messaging:attachment filename="excel.xls">
<c:ReportExport reportId="00OD0000005rcm1" format="xls"/>
</messaging:attachment>
<messaging:attachment filename="csv (with original report footer).csv">
<c:ReportExport reportId="00OD0000005rcm1" format="csv" prettify="false"/>
</messaging:attachment>
<messaging:attachment filename="pretty.htm">
<c:ReportExport reportId="00OD0000005rcm1" format="printable" />
</messaging:attachment>
<messaging:attachment filename="pretty.pdf" renderAs="pdf">
<style>
.headerRow {background-color:#aaaaff}
.grandTotal {background-color:#aaaacc}
</style>
<c:ReportExport reportId="00OD0000005rcm1" format="printable" />
</messaging:attachment>
</messaging:emailTemplate>
4. Unit test (for sake of completeness)
@isTest (seeAllData=true) // Needed because otherwise Report table is blank
private class ReportExportControllerTest {
static ReportExportController ctrl;
static String o;
@isTest
static void testErrorFlow() {
ctrl = new ReportExportController();
try{
o = ctrl.getOutput();
System.assert(false, 'Exception was expected to be thrown');
} catch (ReportExportController.ReportExportException e){
System.assertEquals('Missing argument: reportId', e.getMessage());
}
ctrl.reportId = UserInfo.getUserId(); // valid Id but not a valid report Id
try{
o = ctrl.getOutput();
System.assert(false, 'Exception was expected to be thrown');
} catch (ReportExportController.ReportExportException e){
System.assert(e.getMessage().startsWith('Suspicious reportId:'));
}
}
@isTest
static void testPrettyCsvExport(){
ReportExportController.mockOutput = '"Account ID","Account Name","Full Name","Account Record Type","Created Date"'
+ '"001M000000KbxBu","Abc","","Customer","01/11/2012"\n'
+ '"001M000000J9aYP","Cde","","Supplier","31/08/2012"\n'
+ '\n'
+ '\n'
+ '"Accounts without Contacts\n"'
+ '"Copyright (c) 2000-2012 salesforce.com, inc. All rights reserved.""\n'
+ '"Confidential Information - Do Not Distribute"\n'
+ '"Generated By: X Y 03/12/2012 13:24"\n'
+ '"' + UserInfo.getOrganizationName() + '"\n';
List<Report> reports = [SELECT Id, Name FROM Report ORDER BY Name LIMIT 1]; // Even a fresh uncustomized org should have some sample reports but it doesn't hurt to play safe.
System.debug(reports);
if(!reports.isEmpty()){
ctrl = new ReportExportController();
ctrl.reportId = reports[0].Id;
ctrl.format = 'csv';
ctrl.prettify = true;
ctrl.encoding = ctrl.showDetails = 'Some random gibberish';
System.assert(ctrl.getRequestUrl().endsWith('/' + reports[0].Id + '?export=1&xf=csv&enc=UTF-8'));
String output = ctrl.getOutput();
System.assert(!output.contains('"Confidential Information - Do Not Distribute"'));
}
}
@isTest
static void testPrettyPrintableViewExport(){
ReportExportController.mockOutput = '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">\n'
+ '<html>\n' // skipped a bunch of lines
+ '<!-- Start report output -->\n'
+ '<div id="fchArea"><table class="reportTable tabularReportTable" border="0" cellspacing="0" cellpadding="0"><tr id=\'headerRow_0\' bgcolor=\'#aaaaff\' class=\'headerRow\'><TH align="left" scope="col">Account ID</TH><TH align="left" scope="col">Account Name</TH><TH align="left" scope="col">Full Name</TH><TH align="left" scope="col">Account Record Type</TH><TH align="right" scope="col">Created Date</TH></tr>\n'
+ '<tr class="odd" valign="top"><td >001M000000KbxBu</td><td >Abc</td><td >-</td><td >Customer</td><td align="right">01/11/2012</td></tr>\n'
+ '<tr class="even" valign="top"><td >001M000000J9aY3</td><td >Def</td><td >-</td><td >Supplier</td><td align="right">31/08/2012</td></tr>\n'
+ '<tr bgcolor=\'#aaaacc\' class=\'grandTotal grandTotalTop\'><td colspan="7"><strong>Grand Totals (25 records)</strong></td></tr>\n'
+ '<tr bgcolor=\'#aaaacc\' class=\'grandTotal\' valign="top"><td class="nowrapCell" align="right"> </td>\n'
+ '<td class="nowrapCell" align="right"> </td>\n'
+ '</tr>\n'
+ '</table>\n'
+ '</div><table ><tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>\n'
+ '<tr><td colspan="5"><span class="confidential">Confidential Information - Do Not Distribute</span></td></tr>\n'
+ '<tr><td colspan="5">Copyright (c) 2000-2012 salesforce.com, inc. All rights reserved.</td></tr>\n'
+ '</table></div></div></div></div><div class="pbFooter secondaryPalette"><div class="bg"></div></div></div></div></body>\n'
+ '</html>\n';
List<Report> reports = [SELECT Id, Name FROM Report ORDER BY Name LIMIT 1];
System.debug(reports);
if(!reports.isEmpty()){
ctrl = new ReportExportController();
ctrl.reportId = reports[0].Id;
ctrl.format = 'printable';
ctrl.showDetails = 'yes';
System.assert(ctrl.getRequestUrl().endsWith('/' + reports[0].Id + '?excel=1&details=yes'));
String output = ctrl.getOutput();
System.assert(output.startsWith('<div id="fchArea">'));
System.assert(!output.contains('"Confidential Information - Do Not Distribute"'));
}
}
}
The documentation is fairly explicit here:
The API returns up to the first 2,000 report rows. You can narrow results using filters.
You'll need to execute your report multiple times, using filters that you can guarantee will return fewer than 2000 rows (such as by using date ranges, report run #1 uses THIS_QUARTER, run #2 uses LAST_QUARTER, or you use first letter of name starts with A on run #1, then B on run #2)
Best Answer
In answer to your question, you can't. This is because the Salesforce native functionality won't give you more than 2,000 rows in a scheduled HTML email and this can't be worked around by coding a custom solution since the Analytics API limitation is also 2,000 rows per report.
My view is that the 2,000 row limit is currently in place for good reasons. Firstly, there are Email limits in Salesforce. The maximum size of a single email sent from Salesforce is currently 25MB. If the limit was increased beyond 2,000 rows, then that would potentially be beyond the 25MB limit.
Secondly, generally speaking, HTML email reports are quite burdensome to use and some organizations may not even permit them in the first place due to potential security risks.
So, what's the best approach? Sending a scheduled email with an attachment would be the best approach, in my opinion. Cloud4J offer a very solid tool at an excellent price to achieve this. Furthermore, you could code this solution too. A great starting link with code examples is here.
It's a shame that sending an attachment isn't already a Salesforce feature however, as per the Idea, it is on 'the road map' with a huge number of votes.
Lastly, there is also an Idea to increase the Salesforce HTML report row limit beyond 2,000 here but due to the above reasons, I am not sure this will be increased anytime soon.
I hope this helps.