When you build a report, always provide a guide

You’ll find few fans of Salesforce reporting bigger than me but it does have its limitations. Sooner or later, every Salesforce user or admin is going to want to grab some Salesforce data, export it, get it into Excel, and mess around with it. This happens every day, every hour, all the time. Using Excel or another tool to get more out of your Salesforce data is great — it shouldn’t matter what tool you use, it’s your data, make the most of it!

The only problem is, once the data is in Excel, it’s easier for it to get confused. Why do the numbers in your spreadsheet not match mine? Is this an Opportunities report or an Account report? Wait, did I pull this based on Close Date or Open Date? If you’re spending time on answering questions like that, you’re missing out on time that could be spent making important discoveries from your data and decisions based on them.

Here is one simple technique I use to make sure that every report I pull for a client remains free from distracting debate.

Build a report guide

One one tab of your spreadsheet — I prefer the farthest to the left — build a guide to the component reports and data that come together in your spreadsheet.

  • Base Files – list every report you used in the spreadsheet with a report name and link to the report in Salesforce. If you’re building for regular refreshes, leave a note in the name or description of the report that it should not be edited.
  • One Row Equals – this is the most common mistake that causes confusion, especially if you ever want to count the number of rows in a report. What does one row equal? In an Opportunity report, one row equals one Opportunity. In an Opportunities with Products report, one row equals one Opportunity Line-Item or product. If you try to count the number of rows in the second report to the total number of Opportunities in a segment, you will come up with a very wrong and confusing figure. Eliminate that mistake before it happens.
  • Fields Used – for each Salesforce Object used in your source reports, list out the fields you’ve selected. This is particularly important for value fields, to ensure there’s no confusion over whether you’re looking at a per client value, a per Opportunity value, or a per product value.