Create New Reports with NPSP
After completing this unit, you’ll be able to:
- Identify different report formats.
- Choose the right report type for a data set.
- Create new reports.
NPSP Has What You Need!
So we just learned that NPSP has some powerful out-of-the-box reporting functionality. But what happens when you can’t use an existing report or customize a standard report? You can create a new report—no matter what your reporting needs may be, NPSP has the tools to help you succeed! Let’s rejoin Anthony Hall, the No More Homelessness (NMH) Program Manager, as he explores how to create a new report.
On Not Reinventing the Wheel
When Anthony needs to answer some questions using data, his instinct is to first look for existing reports before doing anything else—and that’s a wise move. As we learned earlier, Nonprofit Success Pack (NPSP) comes with more than 60 fundraising and constituent reports. Most Salesforce admins also create several reports for their users during the initial Salesforce setup and configuration process. When you need a report, it’s best first to find a report that mostly meets your needs, and then customize it with filters. If you can’t customize an existing report to get what you need, then you should create a new report.
And now it’s time to do exactly that! After an introduction to two important report components.
These are the building blocks of all reports so it’s critical to understand how they shape your data before you get to the business of creating reports.
Every report is centered around a standard or custom object (or multiple standard or custom objects). Whichever objects you choose for the report type determine what data you see in the report and what options you have to filter the data. This can sometimes be a difficult decision, but one way to approach it is to think of the fields you need in the report.
Do you want to see volunteer names? Then you’ll need to select the contact object as your report type.
Do you want to see in-kind gifts? That’s the opportunity object.
Need information about households and in-kind gifts? Lucky for you, the opportunity report type includes opportunity and account information.
The report format determines what the report looks like. Want to use a table or a graph? Need any data summarized? That’ll be determined by the format. There are three different report formats, each designed for an increasing level of data complexity.
- Simple table (sometimes called a tabular report): An ordered set of fields in columns—similar to a spreadsheet. These reports are the simplest to create and are best for tasks like generating mailing lists.
- Grouping of rows (summary report): Summarize data, view subtotals, and create charts. These reports offer more options for organizing data than tables and are great for dashboards (don’t worry, dashboards coming soon!).
- Grouping of rows and columns (matrix report): Group records by both row and column. These are the most complex of the three report types, but they offer the most detailed view of your data. Matrix reports are best for an at-a-glance overview of totals for inventory or revenue.
Armed with some new vocabulary, NOW it’s time to create a new report for real.
Create a New Table Report (Tabular Report)
The grocery stores that partner with NMH have recently gone above and beyond their standard donations and have contributed additional resources to the food pantry during a period of unexpected need, and Anthony wants to send personal thank-you notes for their support.
Anthony always follows the recommended best practices, so he first searches to see if a similar report already exists. He tried both global search (which searches everything) and also the more targeted Reports search (which can restrict a search to a reports folder or visibility).
Because there doesn’t seem to be an existing report to answer his question, Anthony creates a new one.
1. From the Reports page, Anthony clicks New Report.
2. From the Choose Report Type window, he selects an object to build the report around. He uses the search box or browse using the options in the object menu. Anthony selects Accounts to build a report on all the grocery stores that donate to NMH, because he knows that every grocery store has an organization account record, and clicks Continue.
3. He clicks Filters from within the filters menu to refine the data.
4. To refine the existing standard filters, Anthony clicks Show Me, and selects All Accounts and clicks Done to apply. He then clicks Date Field, and selects Created Date from the Date dropdown selection, and All Time from the Range, and clicks Apply.
5. Anthony clicks into the Add filter… field to add more filters. He adds Account Record Type, selects equals as the Operator and selects Organization from the list below Value(s), and clicks Apply.
6. Anthony adds a second filter by clicking into the Add filter… field again. He adds Type, selects equals as the Operator and Grocery Store from the list below Value(s), and clicks Apply.
7. He clicks Outline from the filters menu to organize the report. He adds the address fields as columns.
8. Anthony then clicks Save to enter a new Report Name, add a description, and change the Folder. He titles his new report All Grocery Stores and clicks the final Save.
9. Although the report is saved, Anthony needs to click Run to filter and include all the data.
And now Anthony has a perfect list of addresses to export and use for mailing those thank-you letters.
Super easy, right? That’s a major advantage of table reports—you can set them up and start using them quickly, but because they’re so simple, there’s only so much information you can get from them.
After sending out the letters, Anthony turns his attention to another reporting task. Gordon, the Program Director at NMH, has recently been analyzing inventory trends to better understand their resource needs. Gordon knows that Anthony’s becoming quite the data wiz, and asks for a report on the current inventory of the different types of toiletry kits.
Gordon is right: Anthony is getting good with reports, so he already knows that he’ll need to create a new report and group by rows to best answer the question. This process is similar to what Anthony just did to create the table report, but with some important variations. Let’s see how it’s done.
Create a New Report Grouped by Rows (Summary Report)
1. From the Reports tab, click New Report.
2. From the Choose Report Type window, select an object to build the report around. Anthony selects Inventories for this one. Click Continue.
3. Click Filters from within the filters menu to refine the data.
4. Refine the existing standard filters. Anthony clicks Show Me, selects All Inventories, and clicks Done. He clicks Date Field, selects Inventory Created Date and All Time from the Range, and then clicks Apply.
5. In the Add filter... field, Anthony selects Inventory Sold Date and leaves the Operator as equals and the Date field blank. Click Apply to save. With these filters, the report will only display inventory that exists on-hand in the pantry, and not anything that’s already been distributed to clients.
6. Click Outline to organize the report. In the Add column… field, Anthony adds Kit Type, Inventory: Created Date, and Inventory Sold Date.
7. Now the magic happens. To change this from a table report to one grouped by rows, Anthony clicks Add group… in the Groups section within the Outline tab, and adds Kit Type.
8. Click Save & Run. Enter a new Report Name, add a description, and select the Folder. Anthony titles his new report Inventory Grouped by Kit Type.
9. Click Save.
This report shows the current on-hand inventory for the various toiletry kits at the food pantry, grouped by Kit Type, with a summary of each type. At the bottom of the report is the grand total of all kits, regardless of the type.
Gordon is so pleased with this inventory report that Anthony shares, he’s inspired to take it one step further. Would Anthony be able to create a report that could help plan for the coming year? Gordon is particularly interested in trends for in-kind gifts, particularly food donations from grocery stores. He’d like to see the amount donated by each store, month-over-month, over the past quarter.
Create a New Report Grouped by Rows and Columns (Matrix Report)
Anthony knows his way around reports so well now that he already knows he can find this data for Gordon. He’s going to create a new report grouped by rows and columns (a matrix report), because it’s the best report type for an overview of a data set, just like the one Gordon needs.
1. From the Reports tab, click New Report.
2. From the Choose Report Type window, select an object. Anthony selects Opportunities. Click Continue.
3. Click Filters from within the filters menu.
4. Refine the existing standard filters.
a. Anthony clicks Show Me, selects All Opportunities and clicks Done.
b. He clicks Probability, selects All, and clicks Apply.
c. He clicks Opportunity Status, selects Closed Won, and clicks Apply.
d. Anthony clicks Close Date and sets the dates to include the last three months.
5. In the Add filter... field, Anthony selects Opportunity Record Type, leaves the Operator as equals, and sets the Value to In-Kind Gift. Click Apply.
6. Now, on to organizing! Click Outline.
7. In the Group Rows field, Anthony selects Account Name. This selection ensures the report will include how much each grocery store (each with their own, unique account) has donated in total.
8. In Group Columns, he adds Close Month, so the report includes total per month.
9. For Columns, Anthony selects Opportunity Name, Stage, and Close Date.
10. This type of report can be overwhelming, so Anthony does everyone a favor and decides to hide the report details. He can do this by clicking Detail Rows to turn the toggle off.
11. Click Save & Run. Name the report, add details.
12. Click Save.
Anthony’s new report, In-Kind Food Donations By Month Sum, is all ready to go:
With just a glance, the team can see so much great information, including donation totals and how much each store has donated over the past three months. This is going to be so helpful for planning.
Anthony is now comfortable with customizing existing reports and creating several types of new reports. He’s excited by how quickly the team can arrive at good decisions with these overview reports. But all this reporting—creating new reports or customizing existing ones—won’t help unless you can share it with others. In the next unit, we’ll rejoin NMH’s development team to learn how to share, schedule, and subscribe to reports. Let’s do this!