Apex Code Development You need to sign in to do that
Don't have an account?
dxfilo@mac.com Report Automation: Refresh Existing Report, Convert Worksheet to PDF, then Email
Background
We built an Analytic Snapshot to capture Opportunity Pipeline data so we can report historical trends. Now, we are interested in reporting on this data to do a period-on-period comparison between this week vs last week data (with an option to compare other periods e.g. Month-on-Month, Quarter-on-Quarter, Year-on-Year). The problem is that the standard SFDC report output is not 'presentation ready', and produces a report with unsightly rows and columns. Excel is much more flexible in terms of reordering/renaming headers, layout, and not including summary columns/rows. For this reason, we are trying to find a solution for SFDC's rigidity.
In addition, we would like to automate this process to schedule the report to run at a specific time and deliver it to a user in a 'presentation ready' format such as an Excel or PDF file. I am aware of the 'Schedule Report' feature in salesforce.com but as I mentioned we have abandoned salesforce.com's report builder.
We are evaluating several other solutions and work arounds. I have successfully imported a data-set from the custom Analytic Snapshot object into a target excel worksheet using the Excel Connector and created a pivot table in another worksheet. I would like to export only the pivot table report (and not the raw data-set worksheet) to a PDF and send it to an email recipient. Ultimately, I would like to automate the refresh of this data-set (and pivot table) then automatically create a PDF and send it to a designated recipient via email every Friday at 5PM without ever opening the workbook. Is this possible, how would I go about accomplishing this?
Considerations:
- Authenticating the Excel Connector with salesforce.com's login credentials and security token.
- Producing the PDF through an authoring plugin.
- Exporting and Emailing the PDF output to the designated recipient.
I am open to ANY suggestions and would appreciate any guidance.
Skills
Salesforce.com: Visual Force, APEX, Excel Connector
Excel: VBA, Macros
Some smart woman, and may be a man, somewhere will know how to do this. Me I would build a report in Salesforce.com and schedule it to be sent in an email to the people I want to get it. The users don't have to have log ins unless they want to drill down the report.
Sorry if this doesn't help. But that is how I would do it.
Dan
dreyes,
Thanks for the suggestion. We have attempted this solution and it works beautifully. Unfortunately, the standard SFDC output is not 'presentation ready'. Excel is much more flexible in terms of reordering/renaming fields, layout, and not including summary columns/rows. For this reason, we are trying to find a solution for SFDC's rigidity.
Jimmy
Hello.
I've also started building custom reports as Visualforce pages rendered as PDFs, but due to the limitation of the scheduler class not being able to utilize getContentAsPdf, I haven't found a way to have these reports emailed on a schedule.
There's a method at this link that describes creating a Visualforce email template that allows you to attach a PDF, which might work in your case: http://wiki.developerforce.com/index.php/Visualforce_EmailQuote2PDF
Unfortunately, it seems limited in that the Visualforce PDF page cannot use a custom controller. I'm curious as to whether you found a solution for this.
-Greg