Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

Is there any workaround to send out scheduled reports as Excel attachment than actually embedding it within an email?

Salesforce scheduled reports embeds the report in email making further drill down hard.

I did see that this is a Idea Exchange topic and everyone seems to be upvoting on this, but no actual solution yet!!!

Please suggest some workaround!!!!

share|improve this question
    
maybe this post will help you with attaching the CSV file. boards.developerforce.com/t5/Apex-Code-Development/… –  Susan Sep 26 '13 at 18:55
add comment

3 Answers 3

up vote 28 down vote accepted

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">&nbsp;</td>\n'
            + '<td class="nowrapCell" align="right">&nbsp;</td>\n'
            + '</tr>\n'
            + '</table>\n'
            + '</div><table ><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</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"'));
        }
    }
}
share|improve this answer
1  
Thanks eyescream, that's exactly what I was looking for...just tried it on a basic report with 11k records and no problem, will be interesting to see how many Ids can be loaded up before it hits any limits. –  user2403 Feb 14 '13 at 8:40
    
@eyescream I am Resurrecting this question again. I see that the endpoints are urls constructed based of the report Id's can i do the same for a VF page. My scenario is that I want to email a visualforce page that has a data table and instead of embedding the data can i send it as csv attachment?Is this possible with the above approach ? I tried to create custom component but unfortunately salesforce does not understand html / apex tags inside the component tag even when I wrap it in a htmlemailbody. Is there any alternative for this? –  rao May 7 '13 at 17:05
    
I can at the end render it as PDF but the use case is to attach the contents of the datatable as a CSV. I already have the code that generates the email as embedded text within the body of the email. When I try to use HTML elements and use messaging:attachment in my component the attachment comes out with html tags attached along with the data –  rao May 7 '13 at 17:09
    
The important thing I missed out in the above criteria is that i want to schedule this report and send the attachment to the user. so scheduler --> batch apex(execute) --> visualforce template --> component that generates the attachment is the route I choose but unfortunately this seems to be failing to create the attachment the way I want it –  rao May 7 '13 at 17:14
    
@eyescream when i copy component to my organization at that time it showing an error <apex:attribute assignTo> cannot be same as the <apex:attribute name> ( encoding ) can you tell me where i want to change –  Sathya Feb 18 at 10:07
show 1 more comment

If you're not looking to build it yourself you can use Conga Courier which allows scheduled deliveries of reports to contacts and users. At $30/mth for an entire organization it's a pretty awesome deal.

share|improve this answer
add comment

it's a very nice piece of code but it will not work with apex scheduler
"You can't use the getContent and getContentAsPDFPageReference methods in scheduled Apex."
I found this solution interesting. It utilizes workflow email message on field update
trigger blog post

I hope somebody finds it useful too.

share|improve this answer
    
restRequestNeeded = System.isFuture() || System.isScheduled(); - the way I've dealt with it in scheduled context is a REST request to same organisation (which was also not too pretty thing because you have to get the SessionId from somewhere). Still - an interesting alternative! Do you mind writing a bit more in the answer? It's bit "link only" at the moment = will stop being useful if that blog ever goes down... –  eyescream Apr 30 at 20:02
    
Good point @eyescream I didn't want to steal credits from the author. The idea is: because you can't call getContent from scheduled context you need to split the actions. <br/> 1) create a custom object with a field to update e.g. ExportDate <br/> 2) write scheduler to update this field (e.g. to today) at the time you want <br/> 3) write a workflow & email action on this field (e.g. if export date = TODAY())<br/> I tested it & it works.I send my reports daily and I store the endpoint mail in the custom object's field.<br/>It's nice that one can disable the report's footer now. –  user682217 May 5 at 9:12
add comment

protected by Community Sep 26 '13 at 21:04

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.