As promised at the end of the Cleaning Up Fields article, let’s organize the report a bit more using groups, sorting and totals. Here’s what the report looks like so far: Looking back at the notes on our mock-up we need to:
- Group gifts together by constituent
- Remove duplicate constituent names
- Sort by constituent’s last name, then by gift date
- Add total gift amount per constituent
- Add total gift amount for the entire report
Group gifts together by constituent
As the report is right now you might have one constituent appearing on multiple pages (depending on how many gifts they gave) and there really isn’t much order to anything. Let’s start organizing the report by grouping each constituent’s gifts together.
On the Experts Toolbar there’s a Group Expert button that looks like this:
Or, on the Report menu there is a menu item called “Group Expert…” This opens the Group Expert window:
The left side of the window shows the available fields, starting with fields you already have in your report. To create groups on the report, move the appropriate field to the right side of this window either by double-clicking the field in the left window, or by clicking the field and then clicking the arrow button.
For our report we want to group by constituent which, depending on which fields we included in the Raiser’s Edge export, could be done using one of a few different fields. We could group by constituent name but that might cause a problem if we have multiple constituents with the same name (yeah, I’m looking at you Robert Hernandez!). A better option might be to group by constituent ID or import ID but that would add a few steps in order to also sort by constituents’ last names. The best option is to use SortKey, which is a combination of name and import ID. Move that field to the Group By window and click OK.
Your report should now be grouped by constituent with the SortKey as a header for each group. You’ll also notice that the group is also being used to sort, which is why we didn’t use constituent ID or import ID.Remove duplicate constituent names
Now we no longer need to show the constituent name for each and every gift so let’s get rid of it. There are a few ways to do this, I’ll just show you how I would do it, quick and easy. At the same time we will also fix the group headers (unless you like having the SortKey for the header, I certainly don’t).
Click on the group header field and delete it. Then click and drag the first constituent name field up to where the group header used to be. Then press Crtl – B to make the font bold, if you’d like.
Sort by constituent’s last name, then by gift date
We’ve already sorted by constituent name so all we need to do is sort by gift date. Note that this will sort by gift date within each constituent’s group, not the entire report (which is a good thing). On the toolbar there is a Record Sort Expert button that looks like this:Or you can go to the Report menu and select the Record Sort Expert option.
Double-click the gift date field, select Ascending or Descending and click the OK button. Your report will now list each constituent’s gifts in date order.
Adding total gift amount per constituent and adding total gift amount for the entire report will be covered in Get Organized: Grouping, Sorting and Totals – Part 2.