+ Start a Discussion
dpawndpawn 

How can I export a report as a CSV (ideally scheduled to email or FTP)

I need to export a report as a CSV to an FTP site or email it on a daily or weekly basis.

 

I have a Report.  I need to get the report out of Salesforce somehow into CSV format.

 

Ideally, this would be scheduled.


I'm a developer so I can do developer stuff.  However, I don't know APEX and I don't know VisualForce.  I can get someone to do that stuff if we need to.


Thanks!

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox
global class Exporter implements System.Schedulable {
    global void execute(SchedulableContext sc) {
        ApexPages.PageReference report = new ApexPages.PageReference('/00O500000000000?csv=1');
        Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
        attachment.setFileName('report.csv');
        attachment.setBody(report.getContent());
        attachment.setContentType('text/csv');
        Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
        message.setFileAttachments(new Messaging.EmailFileAttachment[] { attachment } );
        message.setSubject('Report');
        message.setPlainTextBody('The report is attached.');
        message.setToAddresses( new String[] { 'asdf@asdf.com' } );
        Messaging.sendEmail( new Messaging.SingleEmailMessage[] { message } );
        
    }
}

Here's an example schedulable class that might be used to email a file. Note that the last five lines need to be ignored or stripped; I'll leave this as an exercise to the reader.

 

You could also build a SOQL query, execute it, then build the CSV yourself, but that means the system is hard-coded and less flexible. Of course, you could build an interactive configuration (perhaps by the use of a custom object or custom settings), which would help work around that particular limitation of flexibility, but that is by far more complex.

All Answers

sfdcfoxsfdcfox

There's two ways you can accomplish this:

 

1) Apex Code. This is ideal, because you can have the code run weekly, scheduled inside your salesforce.com instance. No visualforce necessary in this case, because the results could be emailed or submitted to an HTTP server (not FTP, because Apex Code can't yet do that).

 

2) Export from salesforce.com remotely. Set up a cron job on the server to extract the data. Have the script/program log in programmatically to salesforce.com (API should be okay, or just use the normal login.salesforce.com portal). Then, call your report using the following HTTP request:

 

GET /<report-id>?excel=1 HTTP/1.1
Host: <instance>.salesforce.com
Cookie: sid=<session-id>
<other HTTP headers here> 

A C, C++, Perl, or PHP programmer should be able to accomplish this in just a few lines of code. You could even use a shell script with curl or some other utility. At that point, it's just a matter of setting up a cron job. The downside here is you would need to validate that the server is up and accepting requests, and delay execution until such time (i.e. maintenance).

 

Both sides have some pros and cons, but I think that a scheduled Apex Code class would be preferential to an external pull mechanism; you don't have to worry about logging in, sessions expiring, passwords changing, or most any other problem associated with external integrations.

samdsamd

Hi

 

I just came across this post while researching an issue with scheduled emailed reports.  Could you give a bit more info on how you would implement option #1 - i.e. using Apex to extract data from a report and put into CSV.  As far as I know this isn't possible, but I'm hoping you know something I don't!

 

Thanks

Sam

sfdcfoxsfdcfox
global class Exporter implements System.Schedulable {
    global void execute(SchedulableContext sc) {
        ApexPages.PageReference report = new ApexPages.PageReference('/00O500000000000?csv=1');
        Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
        attachment.setFileName('report.csv');
        attachment.setBody(report.getContent());
        attachment.setContentType('text/csv');
        Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
        message.setFileAttachments(new Messaging.EmailFileAttachment[] { attachment } );
        message.setSubject('Report');
        message.setPlainTextBody('The report is attached.');
        message.setToAddresses( new String[] { 'asdf@asdf.com' } );
        Messaging.sendEmail( new Messaging.SingleEmailMessage[] { message } );
        
    }
}

Here's an example schedulable class that might be used to email a file. Note that the last five lines need to be ignored or stripped; I'll leave this as an exercise to the reader.

 

You could also build a SOQL query, execute it, then build the CSV yourself, but that means the system is hard-coded and less flexible. Of course, you could build an interactive configuration (perhaps by the use of a custom object or custom settings), which would help work around that particular limitation of flexibility, but that is by far more complex.

This was selected as the best answer
samdsamd

Awesome, the ?csv=1 parameter is a new discovery for me!

 

Thanks very much, this will be extremely useful.

 

Sam

goabhigogoabhigo

I am amazed how it solved your problem?

 

getContent() method isn't supported by Apex scheduler !!!

 

Please let me know how it solved the problem? I am running into same, so...

Niraj SaxenaNiraj Saxena

Great Solution !! Really helped me I only require the CSV part and CSV=1 worked like wonders for me.. Thanks for sharing.. Much appreciated...

Vaibhav JainVaibhav Jain

Yes you are right, it cannot be done for Scheduled Apex.

 

Can you please let me know if you have been able to achieve it? Any info will be appreciated

CNimmaCNimma
This is working absolutely perfect. Is there a test class for this code? Since getContent() method isn't supported by Apex scheduler, I am not able to come up with a test class. Thank you very much in advance!
TESTOURSFDCTESTOURSFDC
Small modification to the getcontent() method, as we can't use as it is in a schedulable class. This is working perfectly fine.
global class Exporter implements System.Schedulable {
02
    global void execute(SchedulableContext sc) {
03
        ApexPages.PageReference report = new ApexPages.PageReference('/00O500000000000?csv=1');
04
        Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
05
        attachment.setFileName('report.csv');
06
        attachment.setBody(Blob.valueof(report.getContent().toString()));
07
        attachment.setContentType('text/csv');
08
        Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
09
        message.setFileAttachments(new Messaging.EmailFileAttachment[] { attachment } );
10
        message.setSubject('Report');
11
        message.setPlainTextBody('The report is attached.');
12
        message.setToAddresses( new String[] { 'asdf@asdf.com' } );
13
        Messaging.sendEmail( new Messaging.SingleEmailMessage[] { message } );
14
         
15
    }
16
}

 
Naveen Reddy 8Naveen Reddy 8
Hi,
If I run these code through Developer console I'm able to recieve attachment properly.
i.e.
User-added image

If I schedule this class I'm recieving the attachment as like this.
User-added image
 
global class LeadReport3 implements System.Schedulable {
    global void execute(SchedulableContext sc) {
        ApexPages.PageReference report = new ApexPages.PageReference('/00O90000008euMX?csv=1');
        Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
        attachment.setFileName('Leads.csv');
        attachment.setBody(Blob.valueof(report.getContent().toString()));
        attachment.setContentType('text/csv');
        Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
        message.setFileAttachments(new Messaging.EmailFileAttachment[] { attachment } );
        message.setSubject('Lead Report');
        message.setPlainTextBody('The report is attached.');
        message.setToAddresses( new String[] { 'naveen.bpy@gmail.com' } );
        message.setCcAddresses(new String[] { 'naveen.catch@gmail.com'});
        Messaging.sendEmail( new Messaging.SingleEmailMessage[] { message } );
        
    }
    }


Note: A report is there in my org with id 00O90000008euMX

Please help..

Thanks,
Naveen
Jyoti Shah 31Jyoti Shah 31
Hello,

I tried this code, but the report that is sent in email is empty.
A report is there in the org with the ID
When I run the report on the instance, it has some data as a result. Not sure what am I missing.

Thanks
Jyoti
Sam SatterfieldSam Satterfield
I want to confirm whether or not, this is schedulable via the apex scheduler?

Best,
Sam S.
Andrew ToewsAndrew Toews
I am using the code posted by TESTOURSFDC and it works perfectly as a scheduled job in our Sandbox on Winter '16.  However, when I activate the Page Reference critical update the following error is returned: 

"Scheduler: failed to execute scheduled job: jobId: 7071100000glzc3, class: common.apex.async.AsyncApexJobObject, reason: Callout from scheduled Apex not supported."

Any ideas on how to modify this code so it works after the critical updates are activated on January 8th?  Appreciate any help!
kory108kory108
Building off of sfdcfox's answer, I have created an admin managable custom object for this. Without the custom object, a developer would need to go into the code and update any of the items, such as the report ID, email subject, or email recipients. With the custom object added, an admin just needs to edit a record.

Custom Object
Label: Exporter
Object Name: Exporter__c
Record Lable: Exporter Name
Record Data Type: Text

Custom Object Fields
Attachment_Name__c - Text(255)
Email_Body__c - Text(255)
Email_Recipients__c - Long Text Area(1000)
Email_Subject__c - Text(255)
Frequency__c - Picklist (Weekly, Monthly)
Monthly_Day__c - Picklist (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, last)
Report_ID__c - Text(18)

Once you've created the custom object, you can then create a custom Tab to access the records.

Apex Class
global class Exporter implements System.Schedulable {
    global void execute(SchedulableContext sc) {
        List<Exporter__c> exportList = new List<Exporter__c>{};
        Datetime dt = DateTime.newInstance(Date.today(), Time.newInstance(0, 0, 0, 0));
        Date d = Date.today();
        Date firstDate = d.toStartOfMonth();
        Date lastDay = firstDate.addDays(Date.daysInMonth(d.year(), d.month())-1);
        
        system.debug('First Day: ' + firstDate);
        system.debug('Last Day: ' + lastDay);
            
        exportList = [Select Id, Name, Report_ID__c, Attachment_Name__c, Email_Subject__c, Email_Body__c, Email_Recipients__c, Frequency__c, Weekly_Days__c, Monthly_Day__c from Exporter__c];
        
        for(Exporter__c e : exportList){
            //Determine if Exporter record is scheduled to run today.
            Boolean process = Test.isRunningTest() ? true : false;
            
            //Test for Weekly frequency.
            process = e.Frequency__c == 'Weekly' && e.Weekly_Days__c.contains(dt.format('EEEE')) ? true : process;
            
        	//Test for Monthly frequency.
        	process = e.Frequency__c == 'Monthly' && (e.Monthly_Day__c == String.valueOf(d.day()) || e.Monthly_Day__c == 'last' && d == lastDay || lastDay.day() <= Integer.valueOf(e.Monthly_Day__c)) ? true : process;
                       
            //Run process if scheduled.
            if(process){
                System.debug('Starting message processing for: ' + e.Name);
                ApexPages.PageReference report = new ApexPages.PageReference('/' + e.Report_ID__c + '?csv=1');
                Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
                attachment.setFileName(e.Attachment_Name__c);
                Blob content = Test.isRunningTest() ? Blob.valueOf('UNIT.TEST') : report.getContent();
                attachment.setBody(content);
                attachment.setContentType('text/csv');
                Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
                message.setFileAttachments(new Messaging.EmailFileAttachment[] {attachment});
                message.setSubject(e.Email_Subject__c);
                message.setPlainTextBody(e.Email_Body__c);
                String[] emailRecipients = e.Email_Recipients__c.split(',');
                message.setToAddresses(emailRecipients);
                Messaging.sendEmail(new Messaging.SingleEmailMessage[] {message});
                System.debug('Completed message processing for: ' + e.Name);
            }
        }
    }
}

Apex Test Class
@isTest
	private class TestExporter {
	   // CRON expression: midnight on March 15. Because this is a test, job executes immediately after Test.stopTest().
	   // =========================================================================
	   public static String CRON_EXP = '0 0 0 15 3 ? 2022';
	   static testmethod void test() {
          Exporter__c e	= new Exporter__c();
  			e.Name = 'Test Exporter Record';
            e.Report_ID__c = '00OU0000001xEWe';
            e.Attachment_Name__c = 'Test Attachment Name';
            e.Email_Subject__c = 'Test Email Subject';
            e.Email_Body__c = 'Test Email Body';
            e.Email_Recipients__c = 'abc@abc.com';
          Insert(e);
           
	      Test.startTest();
	 
	      // Schedule the test job
	      String jobId = System.schedule('ScheduleApexClassTest',
	                        CRON_EXP,
	                        new Exporter());
	          
	      // Get the information from the CronTrigger API object
	      CronTrigger ct = [SELECT Id, CronExpression, TimesTriggered,
	         NextFireTime
	         FROM CronTrigger WHERE id = :jobId];
	 
	      // Verify the expressions are the same
	      System.assertEquals(CRON_EXP,
	         ct.CronExpression);
	 
	      // Verify the job has not run
	      System.assertEquals(0, ct.TimesTriggered);
	 
	      // Verify the next time the job will run
	      System.assertEquals('2022-03-15 00:00:00',
	         String.valueOf(ct.NextFireTime));
	      Test.stopTest();
	 
	   }
	}

You'll need to schedule the Apex Class as a batch job - I would recommend setting the batch job to run every day, regardless of what business requirements you have around the schedule. Your administrators can then create new Exporter records and set the exact schedule they want from there. Note that the Attachment Name should include .csv at the end and the Email Recipients should be on one line separated by commas.

Example Exporter Record
Bryan TelfordBryan Telford
Thank you, kory108. This is really awesome.
Monique AmmiMonique Ammi
Thanks for this... I am having trouble finding the Report ID. Any suggestions on where to get it?

Do I need it if I have a unique Report_Name field in the Exporter object and use that instead?
kory108kory108
Monique,

When creating a new Exporter record, the Report ID field should be filled in the with ID for the report you want to auto-run in Salesforce. To find the report's ID, open the report in Salesforce, copy the last bit from the URL, and paste that portion into the Report ID field on your new Exporter record.

For example, say the URL for your report is below. The report ID is the last portion in bold.

https://na12.salesforce.com/00OU0000002CuN3
Monique AmmiMonique Ammi
Ah! Just like the contact and account... I should have thought of that! I appreciate the quick reply!
Sandra WicketSandra Wicket
@kory108 

thumps up! ;)  i simply copy paste your code for my developer edition ^^ but i didnt receive an Email. Our System is "german" .. maybe that is the problem? maybe the time code ? In your Example i should get the report at 00:00 am right ?  
kory108kory108
Jorma,

I'd first verify it's not your email deliverablility settings. Go to Setup > Email Administration > Deliverability and verif that the Access Level is set to All email. I usually forget to turn this on myself, so hopefully that's the issue.
Sulabh KapoorSulabh Kapoor
We need to run this logic of sending an email through a future method otherwise an exception would come that Callout from scheduled Apex not supported.

I am facing an issue that when an email is received, I am unable to open the attachment - the attachment doesn't seem to be a csv file. Any idea?
Sulabh KapoorSulabh Kapoor
Never mmind, I was able to figure out the problem of sending the attachment extension as .csv.

And, I would recommend to use the sendEmail out of the loop - just for the sake if you have more than 1 record to be iterated.
Dilshan PereraDilshan Perera
@kory108

I tried your solution with the custom object and the scheduled apex class, on execution of the scheduled job i'm getting the following error "Callout from scheduled Apex not supported." from the getContent() call.

Is there any workaround for this?
Mudasir WaniMudasir Wani
@Dilshan as of now slaesforce is not supporting the use of getContent() in batch that is the reason you are getting this error.
Colin KenworthyColin Kenworthy
As someone mentioned previously, the report.getContent() is causing a "Callout from scheduled Apex not supported" error and that the workaround is using a Future method. Just move all the code from the execute() into a Future method:
global class Exporter implements System.Schedulable {
    global void execute(SchedulableContext sc) {
        DoExport();
    }

    @future(callout=true)
    public static void DoExport() {
        ApexPages.PageReference report = new ApexPages.PageReference('/00O500000000000?csv=1');
        Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
        attachment.setFileName('report.csv');
        attachment.setBody(Blob.valueof(report.getContent().toString()));
        attachment.setContentType('text/csv');
        Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
        message.setFileAttachments(new Messaging.EmailFileAttachment[] { attachment } );
        message.setSubject('Report');
        message.setPlainTextBody('The report is attached.');
        message.setToAddresses( new String[] { 'asdf@asdf.com' } );
        Messaging.sendEmail( new Messaging.SingleEmailMessage[] { message } );
    }
}
You can easily apply this to @kory108's solution.


 
Mudasir WaniMudasir Wani
Batch is itself a future and we can't call a future with in a future method.
But definitely you can try Queueable Apex: More Than an @future that will solve your problem.

https://developer.salesforce.com/blogs/developer-relations/2015/05/queueable-apex-future.html

Hope this helps.
Donot forget to mark the solution as best answer as it helps others !
Nicole Young 2Nicole Young 2
This is awesome! It worked perfectly to deliver the report to me in a .csv format. However, I'm receiving the following error form the test class: Error: Compile Error: Invalid type: Exporter__c at line 7 column 29

Please advise. Thanks!
kory108kory108
Since I last posted here, Salesforce send out a critical update that broke the old code I had posted. In addition, I ran into an issue where more than 10 email addresses needed to receive the same report, and this hit a limit. As such, below is the new code along with the original setup instructions for reference. This has been tested and is working as of the Winter '17 release.

I have created an admin managable custom object for this. Without the custom object, a developer would need to go into the code and update any of the items, such as the report ID, email subject, or email recipients. With the custom object added, an admin just needs to edit a record.

Custom Object
Label: Exporter
Object Name: Exporter__c
Record Lable: Exporter Name
Record Data Type: Text

Custom Object Fields
Attachment_Name__c - Text(255)
Email_Body__c - Text(255)
Email_Recipients__c - Long Text Area(1000)
Email_Subject__c - Text(255)
Frequency__c - Picklist (Weekly, Monthly)
Last_Ran__c - Date/Time
Monthly_Day__c - Picklist (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, last)
Report_ID__c - Text(18)

Once you've created the custom object, you can then create a custom Tab to access the records.

Apex Class
 
global class Exporter implements System.Schedulable {
    global void execute(SchedulableContext sc) {
        DoExport();
    }
    
    @future(callout=true)
    public static void DoExport(){
        List<Exporter__c> exportList = new List<Exporter__c>{};
        List<Exporter__c> exportUpdateList = new List<Exporter__c>{};
        List<Messaging.SingleEmailMessage> mails  = new List<Messaging.SingleEmailMessage>();
        Datetime dt = DateTime.newInstance(Date.today(), Time.newInstance(0, 0, 0, 0));
        Date d = Date.today();
        Date firstDate = d.toStartOfMonth();
        Date lastDay = firstDate.addDays(Date.daysInMonth(d.year(), d.month())-1);
        
        system.debug('First Day: ' + firstDate);
        system.debug('Last Day: ' + lastDay);
            
        exportList = [Select Id, Name, Report_ID__c, Attachment_Name__c, Email_Subject__c, Email_Body__c, Email_Recipients__c, Frequency__c, Weekly_Days__c, Monthly_Day__c, Last_Ran__c from Exporter__c];
        
        for(Exporter__c e : exportList){
            //Determine if Exporter record is scheduled to run today.
            Boolean process = Test.isRunningTest() ? true : false;
            
            //Test for Weekly frequency.
            process = e.Frequency__c == 'Weekly' && e.Weekly_Days__c.contains(dt.format('EEEE')) ? true : process;
            
        	//Test for Monthly frequency.
        	process = e.Frequency__c == 'Monthly' && (e.Monthly_Day__c == String.valueOf(d.day()) || e.Monthly_Day__c == 'last' && d == lastDay || lastDay.day() <= Integer.valueOf(e.Monthly_Day__c)) ? true : process;
                       
            //Run process if scheduled.
            if(process){
                System.debug('Starting message processing for: ' + e.Name);
                ApexPages.PageReference report = new ApexPages.PageReference('/' + e.Report_ID__c + '?csv=1');
                Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
                attachment.setFileName(e.Attachment_Name__c);
                Blob content = Test.isRunningTest() ? Blob.valueOf('UNIT.TEST') : report.getContent();
                attachment.setBody(content);
                attachment.setContentType('text/csv');
                Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
                message.setFileAttachments(new Messaging.EmailFileAttachment[] {attachment});
                message.setSubject(e.Email_Subject__c);
                message.setPlainTextBody(e.Email_Body__c);
                String[] emailRecipients = e.Email_Recipients__c.split(',');
                message.setToAddresses(emailRecipients);
                mails.add(message);
                System.debug('Completed message processing for: ' + e.Name);
                
                //Update Last Ran date and add to update list.
                e.Last_Ran__c = System.now();
                exportUpdateList.add(e);
            }
        }
        //Send the email messages all at once to avoid limits.
        Messaging.sendEmail(mails);
        System.debug('Send emails.');
        
        //Update Export record with Last Ran date.
        update exportUpdateList;
    }
}

Apex Test Class
@isTest
	private class TestExporter {
	   // CRON expression: midnight on March 15. Because this is a test, job executes immediately after Test.stopTest().
	   // =========================================================================
	   public static String CRON_EXP = '0 0 0 15 3 ? 2022';
	   static testmethod void test() {
          Exporter__c e	= new Exporter__c();
  			e.Name = 'Test Exporter Record';
            e.Report_ID__c = '00OU0000001xEWe';
            e.Attachment_Name__c = 'Test Attachment Name';
            e.Email_Subject__c = 'Test Email Subject';
            e.Email_Body__c = 'Test Email Body';
            e.Email_Recipients__c = 'abc@abc.com';
          Insert(e);
           
	      Test.startTest();
	 
	      // Schedule the test job
	      String jobId = System.schedule('ScheduleApexClassTest',
	                        CRON_EXP,
	                        new Exporter());
	          
	      // Get the information from the CronTrigger API object
	      CronTrigger ct = [SELECT Id, CronExpression, TimesTriggered,
	         NextFireTime
	         FROM CronTrigger WHERE id = :jobId];
	 
	      // Verify the expressions are the same
	      System.assertEquals(CRON_EXP,
	         ct.CronExpression);
	 
	      // Verify the job has not run
	      System.assertEquals(0, ct.TimesTriggered);
	 
	      // Verify the next time the job will run
	      System.assertEquals('2022-03-15 00:00:00',
	         String.valueOf(ct.NextFireTime));
	      Test.stopTest();
	 
	   }
	}

You'll need to schedule the Apex Class as a batch job - I would recommend setting the batch job to run every day, regardless of what business requirements you have around the schedule. Your administrators can then create new Exporter records and set the exact schedule they want from there. Note that the Attachment Name should include .csv at the end and the Email Recipients should be on one line separated by commas.

Exporter Record Example
Nicole Young 2Nicole Young 2
Got it. Thanks!
V.Jayarami ReddyV.Jayarami Reddy
Is there any way to send/brust SFDC report to server location
Ashique Khan 7Ashique Khan 7
@kory108 this is amazing!!! One quick question though. Does it need the 18 digit SFDC ID or will it work with the 15? Also, I have the scheduler running however, the report is not being refreshed or sent. 
kory108kory108
@Ashique I personally have only tried 15 digit IDs, however I believe the code should work with either 15 or 18 digit IDs. 

Have you tried running a debug log for yourself when the scheduler is set to run? Hopefully that should give you some insight into what's going wrong. I would also check to verify both the Frequency and either the Weekly Days or Monthly Day fields are filled in. If any are blank, the code won't pick up on the Export record. 

If you're still having trouble, paste in the debug log from the scheduler and I can take a look.
Vignesh Vaithi 5Vignesh Vaithi 5
@Kory108 Can i please ask, with your approach, how to send the generate csv file and send it to a FTP location? Also, the batch execution should be dynamic (based on an event), not hourly basis.
Jeff HagopianJeff Hagopian
@Kory108, Your approach is incredibly helpful.  How owuld I modify the Exporter Apex Code and Custom Object to be able to run the report export on a daily bases, say at a certain time every day?
Helen Arabanos 8Helen Arabanos 8
This works great! thank you! One question, is there a way to change the email address that "sends" the email with the CSV attachment? We'd like the email to appear to come from the person that is supporting that particular report request. 
Sulabh KapoorSulabh Kapoor
@helen Arabansos 8 - you can enable Org Wide Email Address for that profile (user who manages this report) and use the setOrgWideEmailAddressID to the ID of that email address. Something like ---

OrgWideEmailAddress[] owea = [select Id from OrgWideEmailAddress where Address = 'salesforce@xyz.com'];
email.setOrgWideEmailAddressID(owea.get(0).id);

 
Sulabh KapoorSulabh Kapoor
@Jeff Hagopian - You can schedule it through Schedule Apex button on the Apex Class page. Go to Setup-> Developer - > Apex Classes -> Schedule. Make your selections 
kory108kory108
@Jeff - If you want to run the report at a certain time every day, and other reports at other times during the day, you'd need to add an additional picklist for Time (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23). The code would then need to be updated to check the current hour and see if the picklist Time selected is the same. 

You'd also need to schedule the batch job to run every hour one the backend. Note that Salesforce limits each org to a maximum of 25 batch jobs, so you may want to do one every 3 or 4 hours and then update your Time picklist to match.
Helen Arabanos 8Helen Arabanos 8
@salabh Kapoor - thanks so much! Really appreciate your help with this.
Jeff HagopianJeff Hagopian
@kory108    How can I put this....I aint that smart.   Is there any chance you could provide the apex code for the process to run every 3 hours?  I can add the custom picklist of course...just need help on the apex.   Would be much appreciated.  Jeff
Sulabh KapoorSulabh Kapoor
Hi,

This scheduler works great but now I am working on enhancing this feature to NOT send an email if there is no record in the report. Has anyone already done this ?

TIA
Colin KenworthyColin Kenworthy
I can think of 2 approaches.
1.  Create an Integer custom field in the Exporter__c object that holds the number of lines in the csv when there is nothing to report.  Then count the number of lines in the csv blob and if they match do not send the email.
2. Create a long textarea field in the Explorer__c object that holds a SOQL query.  It would imitate the report type and filters/ranges of the report.  Run the query and if it returns zero rows do not send the email.
Sulabh KapoorSulabh Kapoor
@Colin Kenworthy - Thanks for the 2 approaches but I was looking for a dynamic approach. Option 1 was returning incosistent count with the blob.getContent and I was unable to determine the count of lines and moreover that number would have changed if the columns had changed in the report. Option 2 would not have worked if a user had added different filter criteria in the report.

On reading more about the reportResults class - https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_analytics_report_data.htm. I used something as below - 

// default the value to false so that we send the email for every report
boolean doNotRun = false; 
// get the report information from the exporter object and then find the metadata information
Reports.reportResults results = Reports.ReportManager.runReport(e.Report_ID__c);
//Reports.ReportMetadata rm = results.getReportMetadata();
system.debug('report name --- '+e.Name);

Reports.ReportFactWithSummaries factDetails =(Reports.ReportFactWithSummaries)results.getFactMap().get('T!T');
system.debug('in the if condition -- '+factDetails.getAggregates()[0].getLabel());
// check the value of the first aggregate. this will be zero if the aggegate is on Record Count or will be 0.00 if aggregated on the dollar amount
if(factDetails.getAggregates()[0].getLabel() == '0' || factDetails.getAggregates()[0].getLabel() == '0.00'){
    doNotRun = true;
}
Fabian PetersFabian Peters
In additon to best answer by sfdcfox:
If you have a user working in Lightning Experience, you have to append the URL Parameter isdtp=p1 to the report URL to avoid lightning redirection, which results in an html redirect page output instead of the report results.
Marla GabyMarla Gaby
@kory108: I'm using your Exporter and it's working great for my users. They asked if the file can be exported with pipe delimiters instead of commas. If it's possible, how would I add that in the Apex (novice Apex user!). Thanks.
kory108kory108
@Marla: I don't believe this is possible without a large amount of custom code (if even then). The current code utilizes the standard Salesforce csv (Comma seperated value) extract functionality, so it's pretty simple. I don't think it would be possible to code this for pipe delimiters instead, as this is not supported by Salesforce at all.
Colin KenworthyColin Kenworthy
@Marla your question really deserves its own thread.
This post shows how to break a string containing a csv into rows and individual columns.
It should be trivial to join all the columns in a line back into a string but pipe delimited.
Similarly it's simple to convert between blob and string.
 
https://developer.salesforce.com/forums/?id=906F000000091aKIAQ
Nicole WarshauerNicole Warshauer
@kory108 Is there a way to email Excel report attachments instead of CSV?
Colin KenworthyColin Kenworthy
Use excel=1 instead of csv=1 in the report url:
ApexPages.PageReference report = new ApexPages.PageReference('/00O90000008euMX?excel=1');
also your attachment filename should have an .xls extension (not .csv) and the content type should be 'application/vnd.ms-excel' (not 'text/csv')


 
Nicole WarshauerNicole Warshauer
@Colin Thanks!  I had made the second update but not the first one.  Now that I've made both changes, the attached file is just a file when it comes across.  If I navigate to the download of the file and add .xls, it opens and looks great.  Any ideas on why it is missing the .xls extension in the email attachment?
Rama krishna 190Rama krishna 190

Hi Great people, 

Just a small question , on code give by kory108 is it possible to attach multiple reports as attachments in same email 
eg: all reports in a "Single Report folder" need to be send as attachments in single email .

I have a similar requirement and the solution which kory108 gave is very much helpful except it is working for single report , 

Can anyone help in this regards ? , Suggestions are also most welcome.

Todd KadasTodd Kadas
@kory108-thanks for posting this solution! I have no idea how you even came up with this approach but it enabled me to schedule a report and receive a file as an attachment to an email.  To be honest, I'm not sure what the file type is that I received but I am able to save it and then import into xls as text and use text to column to parse data.  The only issue I have, other than the file type, is that the report I chose to export contains some long text fields including commas and carriage returns.  Text values within this field on some of the records get parsed to separate rows and renders the output unusable (nothing to do with your process but rather the data I am trying to retrieve).  Thanks again!
@Sulabh-how did you manage to extract csv file type?
@Colin-thanks for posting update on how to export to xls file type! Thinking I will try this to determine impact