0

So I've been given the task to create a weekly report that is sent by email (as an Excel file) to the administrator.

The report has to contain all the open leads, and the ones that were closed in the last week. And it also has to contain history data (meaning that we need to see the dates when the status of the lead was changed).

I looked into the built-in SalesForce reports, and I saw that there's one report on "Lead History" but I couldn't join it with the "Lead" report type.

I might've been looking at the wrong place, but it sure seams that if I want to have a report on "Leads", I can't see the history, and vice-versa.

Then, as a second complication, the administrator needs this report as an Excel file, where each row describes one lead.

After seeing that I can't join the "Leads" and "Lead History" report types, I thought that I would have to develop some batch Apex code, that runs through the leads, and creates some kind of .csv file, that is then attached as an email attachment, and sent to the administrator. Then this thing would be scheduled to run each week.

Am I on the right track here? Or it shouldn't be necessary to do a complete custom apex development for this? If the lead history fields would be accessible from the lead report, then it would be really great...

0

I would simply create a Process on the Lead, set to run when the lead is edited or created, and configure that Process to populate TODAY() in a new custom field Closure_Date__c when the Lead's status is updated to Closed.

Then, you just need to run a Lead report, with no mucking about with the history. You can filter on your new Closure Date field and also include it in the body of the report without duplicating rows.

If you need to report on all status changes - i.e., more than one status change per Lead - you're not going to get that one-row-per-Lead report you want without doing some elaborate and ugly roll-up mechanics to concatenate data into a field.

  • Thanks David for the reply! Sadly, we need to have a more elaborate "history" on each row, so the report will give us the info about the time that the lead stayed in each and every status. So there would be a column for each status, and we could see there the time it was put into that status. But from your response I can see that you're trying to tell me that I should be able to get the right data from the Lead History, onto the Lead object, and then I could just create a report on that? – Laureant Jan 7 at 12:52
  • If I have that, would I still be able to send an Excel report weekly, generated from that Report object? – Laureant Jan 7 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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