2

I need to write a specific report for my company which cannot be accomplished using the standard/custom reports on Salesforce as it will calculate Year today sales, months today sales, etc.

My problem is that I have to count those sales against invoices and then each invoice line item which in average for a fiscal year is about 4 millions records. I'm just wondering what is the best approach to get this. It can't be a batch as this report is only for the executive team and once they run the report it must bring all the data.

I'm planning to do it with Visualforce page and apex classes. At the end is a summary of sales for this fiscal year and the previous one.

Any advice will be much appreciate it.

Improve this question
4
  • 2
    I would add file/object to support the report and use a daily batch to update it. After initial update you would only have to iterate over new/updated data. Doing it on a vf page would be painful for the user. Although if you got that rout do tell us how long it takes to load/crash
    – Eric
    Commented Sep 26, 2016 at 4:10
  • @Eric I've just read your comment and stole it by mistake although it's true this is more or less how I solved the million+ record problem. :)
    – Dan Jones
    Commented Sep 26, 2016 at 11:02
  • @Poet - No worries, The more comments on the same lines the better chance the OP will understand it is the best way :)
    – Eric
    Commented Sep 26, 2016 at 11:10
  • @Eric Sorry about that! But it is good to know other people feel it's a viable solution as I felt I was shooting in the dark with that one!
    – Dan Jones
    Commented Sep 26, 2016 at 11:12

2 Answers 2

Reset to default
4

You will be pushing the boundaries of the apex and Visualforce here and the best you can do is to run batches to process this data and keep it updated on a custom object nightly .

The visualforce can reference only the summarised custom object records .You can look at ETL Tools like Mulesoft , Informatica Cloud ,etc to process data using bulk API and off platform and get you the reports .

The other way would be to use solutions like Analytics cloud ,Tableau or Domo,BIRST etc (BI tools ) which has ability to handle huge data volumes and do analytics for you.

Improve this answer
1
  • Thanks for your answer. Is a valid approach but is not suitable for me as the users need to see this data and because this report is against sales we need real live data and not batch process as we will be missing the daily sales. Do you have any other idea? Thanks.
    – MANUELAN00
    Commented Sep 27, 2016 at 0:54
2

Although this may not be the best answer in the world, I had a similar problem with having to report on 4,000,000+ records.

What I did was to create a custom object that stored calculated records obtained by a batch class.

The specific scenario for us what that we have Applications with other objects as children of the Applications (we're a recruitment company) and we needed to calculate how many Applications had Interviews, Submitals, Deals etc...

I decided to write a relatively simple scheduled batch class that gathered the sum of these from yesterday (i.e. CreatedDate) (as Applications could come in late in the evening and we didn't want to miss those!) and stored them in an object that held the figure (plus a few other bits of 'metadata', such as the date that collection of records were created). That way:

  • Rather than querying millions of records, we'd be querying 360 for a years worth of Applications
  • All of the leg work done by the batch class could be run out of office hours (e.g. at 1am) so Users wouldn't be affected by performance (although this didn't seem to hit us too hard anyway)

The only problem with this is that you'd need to run this batch class for each day that you'd want to report on, keep in mind governor limits dictate you can only schedule up to 100 at a time.

Hopefully, this makes sense and you can translate it to your scenario and gives you at least a little bit of an idea as to solve this as I know it's nightmarish!

Improve this answer
5
  • The only other problem I see with approach is what if customer already has 4million rows?
    – Mohith Shrivastava
    Commented Sep 26, 2016 at 12:33
  • Also if batch fails due to some reason you need retry mechanisms
    – Mohith Shrivastava
    Commented Sep 26, 2016 at 12:34
  • @MohithShrivastava I addressed that in saying "you'd need to run this batch class for each day that you'd want to report on", I agree it's a problem. Definitely agree with retry mechanisms as well. Like I say, I had a similar problem already with 4m+ records, this is how I got around it without buying packages. :)
    – Dan Jones
    Commented Sep 26, 2016 at 12:42
  • Thanks for your answer @Poet. Is a valid approach but is not suitable for me as the users need to see this data and because we these report is against sales with need real live data and not batch process as we will be missing the daily sales. Do you have any other idea? Thanks.
    – MANUELAN00
    Commented Sep 27, 2016 at 0:55
  • @MANUELAN00 You can of course schedule the batch multiple times per day or have a button or command that executes the Batch class as and when you need the data. Once you've got the top level numbers, it's then more viable to "drill down" to see yearly/monthly figures in greater details. Outside of that, I may well be out of ideas!
    – Dan Jones
    Commented Sep 27, 2016 at 8:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .