Use this link to share with your colleagues:
Advanced Reporting Customizations: https://help.pm.leapevent.tech/a/1173743
You've gotten comfortable with basic reporting customizations, but that's not always enough to give you what you need.
Maybe you're trying to pull an email list of Contacts, but you only want to see patrons who bought tickets at a particular Price Level. Maybe you'd like to see how many donors you have in various regions, but neither city nor state is quite the right demarcation. Or perhaps you'd like to quickly compare information from two different fields in the same report.
You've come to the right place! All these things (and more!) are possible, and this article will highlight some extra-useful tools that will take your reporting skills to the next level:
Ready to learn more? Let's go!
Filter Logic
Filter logic lets you apply multiple filters to your report and define how they should be combined. So, for example, what if you want to pull a report of Contacts who live in your state or have donated at least $100? Or what if you want to pull a report of Donations with a specific Fund and amount, or that have a specific Primary Campaign Source?
Filter logic allows you to pull a report with intricate sets of criteria all on one report.
Filter logic can be used to combine a variety of different filters! In this example, we're looking for Donations in our Education Fund that are over $100 or Donations that are in our 2021 Be Our Hero Appeal.
1. Start with a report that looks at the object you want to look at
In this case we're looking for Donations, so we'll start from the All Donations report. Remember to "Save As" so you don't overwrite your template!
2. Add your first filter
In this case, that's adding a filter for Fund equals Education.
3. Add the next filter
Since we also want to see Donations over $100 in the Education Fund, we'll add a filter for Amount greater or equal to $100.
4. Add another filter
In this example, this will be our final filter: looking for Donations that have 2021 Be Our Hero Appeal set as the Primary Campaign Source.
5. Time to add in filter logic
This will let us tell the report how to evaluate the filters we've added.
6. Edit filter logic
In the Edit Filter Logic box, we'll define our filter logic. This will tell the report how to look at the filters we've added.
By default, filter logic will be set to an "AND" between each filter. In this example, that means that filter logic defaults to looking for Donations where the Fund equals Education AND the Amount is greater or equal to $100.00 AND the Primary Campaign Source equals 2021 Be Our Hero Appeal.
Since this isn't what we want to see, we'll need to edit the filter logic. Since we want to see Donations where the Fund equals Education (filter 1) and the Amount is greater or equal to $100 (filter 2), we already have the "AND" logic set for these filters.
But since we want to also see Donations with a Primary Campaign Source equals 2021 Be Our Hero Appeal (filter 3), regardless of the Amount or Fund, we need to change the criteria before this filter to "OR".
Lastly, we need to group our filters in the filter logic. Since we want to see Donations where the Fund equals Education (filter 1) and the Amount is greater or equal to $100 (filter 2) or where the Primary Campaign Source equals 2021 Be Our Hero Appeal (filter 3), we need to group our filters with parentheses. Since filter 1 and filter 2 should both be evaluated together, we'll put parentheses around them.
Parentheses here act similar to the way they do in algebra! The filters in the parentheses are evaluated together first, then filters outside the parentheses are evaluated.
7. Click Apply
8. Save & Run the report
Cross filters
Cross filters allow you to filter a report by data that's closely related to the records in your report, without showing those separate records in the report itself. This keeps your report clean and tidy and helps you eliminate unnecessary duplicates, while still getting the accurate information you need.
Cross filters also make it possible to filter one report by data stored on more than just one related object, which makes them most useful tools, indeed.
Check out this article to learn all about cross filters, then test your skills with the example below.
Example scenario:
We've got donor appeal coming up, and it's a bit complex. When building an advanced report, it can help to write out the criteria first, so lets start there:
- We'll be sending this via email, so we'll need a list of Contacts with valid email addresses who have not opted out.
- We want to find patrons who have purchased student tickets in the past two years and solicit donations to support our newly-created Education program.
- We also want to make sure they haven't already donated to that specific program.
- We've got a special fundraising concert planned next month, so we want to filter out folks who have purchased tickets to that, as they'll be getting a separate packet of donor solicitation materials.
- Finally, we'll filter out folks who have given $5,000 or more in total this fiscal year, as we plan to reach out to them for a separate initiative.
How might you approach this? Hint: numbers 2, 3, and 4 will each involve a separate cross filter!
1. Start with a list of emailable Contacts
Make a copy of the "Collected or Confirmed Opt-In Contacts" report to start -- that's our best starting point for any email list like this!
2. Find folks with recent student tickets
Our second criteria calls for patrons who have purchased student tickets in the past two years.
To find them, we'll add a cross filter looking for active tickets with that Price Level purchased since in a relative date range:
3. Exclude patrons who gave at the office
Our third criteria says we should make sure they haven't already donated to the Education program, so we don't ask them twice for the same thing.
We'll use another cross filter here: this one will exclude patrons with Donations to a particular Fund:
4. Exclude patrons attending the fundraiser
The fourth criteria requires us to filter out patrons with tickets to our upcoming fundraiser, as they'll be getting a separate packet of donor solicitation materials.
We'll add another cross filter for this; similar to the first one, only exclusive instead of inclusive:
5.
Our final criteria says we should filter out folks who have given $5,000 or more in total this fiscal year, as we plan to reach out to them for a separate initiative.
Should we do this with a cross filter, or a regular field filter?
While cross filters are wonderful, they have an important caveat: a cross filter can only look for any one record that meets the criteria.
In this case, we need to filter based on the total giving in a particular timeframe (this FY), regardless of the number of gifts. A cross filter could find any one Donation of $5,000 or more, but it cannot look for two $2,500 gifts, or five $1,000 gifts, etc.
For this one, we'll use a regular field filter instead, like the last one shown here:
Bucket fields
Buckets let you sort information from a given field in your report into ranges or categories that may be more helpful than just the raw data. For example, you might want to group an Account report by the amount they've given in a certain timeframe, but rather than seeing separate groupings for every different number, you might prefer to have things grouped by ranges, like "$100-499" and "$10,000 and up".
Because bucket fields work a little differently depending on the value they're bucketing, we'll start with two different examples and walk through the steps for each.
For this example, let's group an Account report by Donor Level. We'll say that we have just four:
- Platinum: $1,000 and up
- Gold: $500-999
- Silver: $100-499
- Bronze: $1-99
Here are the steps!
1. Start with a report that contains the field you want to categorize
In this case we're bucketing an Account field, so we'll start from the All Accounts report. Remember to "Save As" so you don't overwrite your template:
2. In edit mode, click the drop-down arrow next to "Columns" and choose "Add Bucket Column"
3. Select the field you want to categorize, and name your new bucket field
4. Click "Add" to create the right number of categories
In our example, we have four (Bronze, Silver, Gold, and Platinum).
5. Name your categories/ranges
Note that when bucketing ranges, the smaller numbers will always be listed first.
6. Define the ranges
Pay attention to the "less than or equal to" and "greater than" symbols on each bucket row, and enter numbers appropriately to get the ranges you want.
For example, we want the Bronze level to stop just before $100, and the Silver level to pick up at $100, in other words for anything greater than $99.99:
7. Click "Apply" to save!
You'll almost always leave the checkbox at the bottom checked!
8. Your new bucket field will be added as a column in your report...
9. ...but you'll probably want to drag it up into the "Groups" area instead:
10. Remember: a bucket is not a filter
(although you can filter by bucket fields if you want!)
Note that we have folks included in the "Bronze" category who have blanks or zeros in the bucketed "Amount Donated in Last # Days" field:
You'll likely want to add a regular field filter as well, to specify that this field needs to be greater than zero:
And that's it! Don't forget to save your report!
For this example we'll look at donors again, but this time let's bucket some nearby cities into a "County" field, so that we can see where most of our donors live and focus our regional outreach efforts.
Here are the steps!
1. Start with a report that contains the field you want to categorize
We'll use an Account report again here, but we'll name it differently. Don't forget to "Save As" so you don't overwrite your template:
2. In edit mode, click the dropdown arrow next to "Columns" and choose "Add Bucket Column"
3. Select the field you want to categorize, and name your new bucket field
4. Select values and move to first new bucket
Check off the values you'd like to add to your first category, then click the dropdown arrow next to "Move To" and select "New Bucket"
Hint: use the "Search Values" box if needed!
5. Name the newly-created category
6. Repeat steps 4-5 to create however many categories you need
7. Check the box to "Bucket remaining values as Other", then click "Apply" to save your bucket
If you don't check that box, every unbucketed value will be shown as its own individual category, which can muddy up the report.
8. Your new bucket field will be added as a column in your report...
9. ...but you'll probably want to drag it up into the "Groups" area instead:
10. Remember: a bucket is not a filter
(although you can filter by bucket fields if you want!)
Since we created this report to group donors by category, we should now filter it to show only what we define as current donors: for this example, we'll say that the Amount Donated This Fiscal Year is greater than zero.
We might also want to filter out donors missing an address, with a filter for Billing city is not equal to [blank].
11. Adjust the display of your report as needed, and save!
For example, you may wish to add columns (like the "Amount Donated This Fiscal Year"), include row counts (to show the number of records, in this case Accounts) and hide detail rows (to show just a summary of your groupings):
And that's it! Don't forget to save your report!
Row-level formulas
It's now possible to build row-level formulas into a report! This allows you to analyze or compare fields in your report and show the results in a new column.
This used to only be possible with custom formula fields, and while that's still possible, row-level formulas are a great way to get the answer without going through the hassle of creating a new field.
Row-level formulas have a variety of applications, but using them with numbers and currencies is common. For this example, we'll look at all Accounts that gave last fiscal year, and use a row-level formula to quickly see if they've increased, decreased, or maintained their giving so far this fiscal year.
1. Start with a report that contains the fields you want to analyze
For this example, we'll start from the "All Accounts" report, since the fields we're interested in ("Amount Donated Last Fiscal Year" and "Amount Donated This Fiscal Year") both live on the Account. Don't forget to "Save As" so you don't overwrite your template:
2. Add any filters and adjust columns as needed
For this example, we only care about donors that gave the previous fiscal year, so we've filtered for "Amount Donated Last Fiscal Year greater than 0", and adjusted the columns to show the Account Name, Amount Donated Last Fiscal Year, and Amount Donated This Fiscal Year:
3. Click the drop-down arrow next to "Columns" and choose "Add Row-Level Formula"
4. Name your new column, add a description, and choose the output type
Give your column a name that will make sense in the report, add a description to clarify what it does for future users, and decide on the output type. In this example, we want the output type to be "Text" so that it will return a word (like "Increased") based on what the formula finds:
5. Build your formula
If you're not familiar with formulas yet, don't panic! We'll walk you through the steps for this particular example, but we'll also include the full formula for you to copy and paste below.
If you want to build other kinds of formulas or dive in a bit deeper, Salesforce has some great resources, including:
- This Trailhead unit about using formula fields (a row-level formula in a report isn't quite the same as a formula field on an object, but the same concepts apply. This Trailhead also has a list of common errors).
- This Trailhead unit about building advanced formulas for different outcomes
- This list of formula operators and functions
Start by typing "IF" and click that formula option when it appears. This brings up a template:
Enter the "logical test"
Let's start with "Amount Donated This Fiscal Year is greater than Amount Donated Last Fiscal Year". You can select a field from the list on the left, or start typing to bring up a list of options:
Add the "greater than" symbol, then enter the second field:
Enter the "value if true"
If it's true that the "Amount Donated This Fiscal Year is greater than Amount Donated Last Fiscal Year", then the patron's giving has increased. Therefore, enter "Increased" (include the quotation marks).
Enter the "value if false"
If the only answers we cared about were "Increased" or "Not Increased", we could enter "Not Increased" here -- but we also care about "Decreased" vs "Maintained", so instead we'll enter a second "IF" formula to ask our next question.
Create the second logical test and values if true/false
Our next logical test is "Amount Donated This Fiscal Year is less than Amount Donated Last Fiscal Year". If true, we want the system to return the text value "Decreased". If false, we'll ask one more question, so insert another "IF" formula.
Create the final logical test and values if true/false
Our final logical test is "Amount Donated This Fiscal Year is equal to Amount Donated Last Fiscal Year". If true, we want the system to return the text value "Maintained". We also have to insert a "value if false", so in this case we'll enter "Error" to help us find any problems later on.
Validate your formula...
This is the fun part... click the "Validate" button to check your formula for errors:
If you receive any errors, don't panic -- that's common! Read the error message carefully, and check your formula to make sure you:
- Have the right commas and parenthesis in the right places
- Entered the right field names
- Put quotes around anything that's supposed to be plain text
Click the "Validate" button again after each adjustment to see updated error messages, or a success message when it works:
If you're still stuck, check out the "Debugging Formulas" section of this Trailhead module for a bit more info, or you can always copy and paste this specific formula from the section below.
And click "Apply" to save!
Copy everything in the box below
IF(Account.PatronDonate__Amount_Donated_This_Fiscal_Year__c > Account.PatronDonate__AmountDonatedLastFiscalYear__c,"Increased",IF(Account.PatronDonate__Amount_Donated_This_Fiscal_Year__c < Account.PatronDonate__AmountDonatedLastFiscalYear__c,"Decreased",IF(Account.PatronDonate__Amount_Donated_This_Fiscal_Year__c = Account.PatronDonate__AmountDonatedLastFiscalYear__c,"Maintained","Error")))Paste it into the formula box here
Click "Apply" to save
View your results, and don't forget to save the report!
Summary Formulas
Summary formulas are great if you want to summarize data from a specific field, or column, in your report.
For example, what if you'd like to know the average amount that each of your donors' Accounts donate to help inform your upcoming asks? Summary formulas can help you do that.
Here, we'll use the example of finding the average amount that each Account donates.
1. Start with a report that contains the field you want to look at
In this case, we're starting from the All Donations by Account report. This not only has the Amount field that we want, but it's also already grouped by Account for us! Make sure to Save As to keep the base report in tact for future use.
Before adding a summary formula to a report, it must have at least one row group!
2. Click Add Summary Formula by Columns
3. Give the Summary-Level Formula Column a Column Name and Description
4. Select a Formula Output Type
Since we're looking for the amount, in dollars, that each Account donates on average, we've selected Currency as our Formula Output Type.
5. Build your Formula
Since we'd like to get the average of the Amounts of each Donation, we selected Average as our function and Amount as the field.
There's a lot that you can do with building these formulas! While our example here is fairly simple, you can get really complex with your formulas. For more on creating summary formulas, check out Salesforce's documentation.
6. Click Insert
This will plug the function and field you selected into the Formula section
7. Click Validate
This ensures that the Formula you've built makes sense to the report.
8. Click Apply
9. Save & Run the report to see your summary formula in action!
Group Dates
Group Dates are useful if you'd like to organize your report by dates, but by larger increments than each individual day, such as by month or year.
For example, what if you want to see all your Donations by Close Date each year to compare them? Group Dates can help you do that.
1. Start with a report that contains the field you want to look at
Here we'll use the All Donations report, since it contains the Close Date field we want.
Make sure to Save As to keep the base report in tact for future use.
2. Add the date field you want to use in Group Rows
In this case, since we want to organize by Close Date, we're adding that to Group Rows.
3. Click for more options for the date grouping in the Preview
4. Click Group Date By
5. Select the desired grouping
There are several options here, and you can select the one that best fits your needs:
- Day: The default; each individual date (i.e. 1/1/2025)
- Calendar Week: Groups by each calendar week (i.e. 12/29/2024 - 1/4/2025)
- Calendar Month: Groups by each calendar month (i.e. April 2025)
- Calendar Quarter: Groups by three month quarters (i.e. January - March 2025 appears as Q1 CY2025)
- Calendar Year: Groups by calendar year (i.e. 2025)
- Fiscal Quarter: Groups by three month quarters according to your fiscal year (i.e. Q1 FY2025)
- Fiscal Year: Groups by fiscal year (i.e. FY 2025)
- Calendar Month in Year: Groups by month, but combines all years (i.e. May will show all Donations with a Close Date in May, regardless of year)
- Calendar Day in Month: Groups by date, but combines all years and months (i.e. 1 will show all Donations with a Close Date on the 1st, regardless of month or year)
In this example, we'll select Calendar Year.
6. Save & Run the report to see the Group Date in action!
