How can I create a report showing the most recent records from three different categories?

Question

So for instance, if I have three types of Accounts ('1s', '2s' and '3s'), how would I show the 1, 2 and 3 with the most recent dates in a single report?

I've tried doing this through dashboards (using three separate reports for each type of account as sources, then limiting each source/table to one value - so one dashboard table would show 'Account 1 2017', another would show 'Account 2 2017' and so on), but since I can't use a dashboard to create a report, that doesn't work.

I also tried creating a new Report Type and limiting the number of rows shown, but that doesn't work for groupings, and still doesn't allow me to control from which categories records are shown (for instance, I can sort the report by the date field and limit the rows, but since there hasn't been a '3' record created since 2016, it doesn't show up with the '1' and '2' record within the report).

This seems like it should be doable though - am I missing something?

Answer 1

I don't think you can do this in Salesforce with reports. The problem is that each Account record does not know about the other Accounts records. So even if you got a report, summarize by type then sort by last activity date or last modified date you'll still get all the records for that type. To get down to one record you'd have to filter but how would a record know that it's the latest?

I think this might have to be get all the records in Salesforce export to excel and then filter from there.