Use Formulas in report criteria

You have some core reports that reference the same criteria over and over, right?

Like you’ve got this in 37 reports and views:

Lead Source does not contain Partner

What happens if you need to add another Lead Source?

How many dozen reports will you need to find and update that lead source value?

Make this the last time you edit all those reports! Use a formula field in your criteria!

By adding another layer, you’ll enable yourself to be able to edit the formula field only, and it will logically allow all your reports to move fluidly over without you having to do anything.

Equipment needed

  • a formula field on the opportunity called “Lead Source Type”
  • Change all your reports’ criteria (for the last time) to reference this new field instead of “Lead Source”

Use the CASE() formula function to set the value for the Lead Source Type formula:

CASE( LeadSource,
"Email Campaign", "Marketing",
"Webinar", "Marketing",
"Trade Show", "Marketing",
..and so on... "Referral Partner", "Channels",
"Reseller Partner", "Channels",
"Other")

And then have the report criteria work like this:

Lead Source Type Contains Marketing

By using these kinds of universal report writing formula fields on your core objects and their major categories, it also will allow you major flexibility as your organization grows or undergoes change.

To get you thinking, the kinds of things you may want to consider formula-izing:

  • Account Types and owner groupings
  • Product Groupings
  • Opportunity Won, Lost, Open
  • Opportunity Types

Leave a Reply