(re)portable

Yesterday, my partner and I were shopping for winter jackets.  I opted for a durable, warm jacket with a nice, fluffy hood.  Amit was investigating the “all in one” combo jackets that have a zip-in/zip-out liner.  One jacket, many permutations!  If you need to prepare for a different temperature, you can just add or remove a layer.  This ‘coat quandary’ is a phenomenal metaphor for building a reports library in a database.  Do you save many coats (that are similar, except for one or two factors) or do you select a “master coat” that you can modify over time (Amit’s coat plan)? 

Tempting you with templates

Recently, my co-workers needed to pull a report for their next, big email blast.  Just like we practiced, they selected an appropriate report type in Salesforce, added criteria, and spot checked their results.   Then, they saved the report as “Email blast contacts as of DATE.”  (I was so proud!  This is a big milestone!).  Like any teacher, once your students master the basics, you can kick things up a notch. There may be a few scenarios where you want to save a report with a date in the name – but in my opinion, they are few and far between.  For example,

• Report is the source for a dashboard chart at a specific (unusual) date interval
• Report uses “relative date filters” (continue below for more explanation!)
• User needs report, but doesn’t have access to modifying reports or other permissions (or, hasn’t received adequate training)

Out of date

Saving reports one at a time is a good place to start (especially if you are new to reporting from a database like Salesforce), but the problem is….you can very quickly end up with a LOT of saved reports, many of which become out of date (sometimes, even, immediately).  What good is a report library with reports like “active grantees as of 11/19/2018″… “active grantees as of 11/18/2018” etc?  Will you really need to reference them again next year?  Why save so many versions when you can just have an “Active Grants as of…” report and change the date as needed?  Or even, an “Active Grans as of TODAY” report that auto-updates the date criteria the moment you run it.  All of this is possible!  Successful template building requires a conceptual shift from report (singular) to template (general); from working as an individual (MY reports) to working as a team (OUR common reports); from solving a specific problem to developing a reporting strategy.

Let’s think about this from the perspective of a nonprofit or social movement organization.  You built a trusty template for a “donations” base report; the default is “ALL donations EVER recorded in the system!”  Then, perhaps you want to restrict the report to just donations “this year, so far.”  Or you want donations from Quarter 4 last year to compare to your projections for this year.  Or unfulfilled pledges – those are important too, but we can’t confuse them with ACTUAL donations received.  Maybe you want to ‘zero in’ on donations from Giving Tuesday (hopefully more than $0!)  In any of these scenarios, you’ll essentially want to start with the same “base” report – the only thing that has changed is one criterion, the date range!  My advice is to save a template inside of your database (non-date specific) and then enter the date you need when running it.  (In Salesforce, you would accomplish this with a Saved Report.  In Nationbuilder, you might copy it with a Saved Filter.  Other databases will likely their own version of this functionality).  No need to “Save As” a copy of your template each and every time.  Then, when you export the data into Excel or Google Sheets, I recommend that you notate ALL of your criteria in a handy-dandy “read me” tab.

Why is this approach a good idea?

1. First of all, it’s simply less work to use a template than to start over each time!
2. Second of all, your criteria will be easily reproducible!
3. Third of all, your results are likely to be more accurate!
4. Finally, it’s more accessible so more users can get the benefit of reports without having to invest the time in learning how to start from scratch.

Then again, it’s all relative!

As you forge ahead in your reporting journey, you may want to run reports that reference date RANGES like “last month” or “last year.”  And while you certainly CAN use filters like “Date > 10/1/2018” and “Date < 10/31/2018,” when the month rolls over to December, your report will always show you data from October instead of the “previous month” whatever the previous month may be at any given time.  Fortunately for us, Salesforce has a HOST of built-in relative date ranges, from “yesterday” to “last year” to “last quarter” to “this year and last year” and beyond.  You can reference an entire list here.  If you’re using relative dates, it’s a good idea to save different versions of a report since the dates will automatically fall into place based on the day you run it.  You might take your donations report and save one version with a relative date of This Month and another with a relative date of Last Month. Chances are, you’ll run those again and again.  Huzzah – it’s an example where I recommend saving versions of a template!

There’s so much more you can do with Salesforce reports, from building dashboards, to subscribing/scheduling reports, to creating elaborate formula fields a la Salesforce legend Steve Mo, to adding summaries.  However one thing that Salesforce CAN’T do (out of the box, anyway), is provide reporting “as of” dates. That means that Salesforce reports will query your real, live, current data based on your report criteria, even if your underlying data have changed since the last time you ran the report.  Usually this is a good thing but sometimes it can get you into trouble, especially if you’re re-running a report with the same criteria, but your results are different than last time (aka your data have changed).  That’s why I advocate for users to EXPORT your reports when you are satisfied with your results, and then save your criteria and a link to your saved report template in a trusty “read me” tab.

Ok, so how do you actually operationalize a reporting strategy with relative dates, report folders, exports, and more?  Even though this Medium piece is a few years old, I think a lot of the principles still apply.  A few things I would add… (1) make a “Read Me” tab in all of your report exports (yes, it’s so important that I WILL reference it 3x in this post; (2) come up with a consistent “naming convention” for your locally-saved Excel versions of reports; (3) establish a cut-off period where if a Saved Report hasn’t been modified or run, it is eligible to be deleted (I usually recommend 13 months); (4) Consider adding charts to take a report and start making meaning right away).

It seems as if this post that started about whether to purchase a polar-proof jacket or a 3-in-1 coat has meandered into uncharted territory where I am waxing on about reports and exports. By way of conclusion, and since it’s Thanksgiving week after all, I want to thank Jamie, Kelsey, and Ryan who’s questions about reporting and end-of-year fundraising created the jumping-off point for this post.  Inspired?  Hopelessly confused?  Send me your feedback, good, bad, and ugly, so I can keep making TDAA the best it can be.