Real Talk: Relationship Reports

What’s the deal with these relationship reports? How can I create a report that gives me information about two related accounts? Why isn’t my report showing me all of the relationships? As an eTap analyst, I’ve heard all of these questions. Moreover, I get why you’re asking them! Relationship reports require a very clear intention from the get-go so that the connection between the query and the report is correct. Ready for the best news you’ll hear all day? The custom relationship reports don’t have to be over complicated. We’re going to walk through a scenario and break down each component of the report so that you have a clear understanding of how the pieces fit into the puzzle.
 
Before we begin, I just want to clarify right out of the gate that custom relationship reports can only report one Relationship Type at a time. If you want to export all of the Relationship Types, we have a Standard Export for this, but we can’t customize any of the columns; the steps to export all of the Relationship Types for an account are available at https://kb.blackbaud.com/articles/Article/105488. Ok, now that we’ve gotten that out of the way, let’s begin working on our custom relationship report.
 
Let’s say I’m a fundraiser for a university alumni association (Go Hoosiers!), and I have a relationship between current students and their parents. I want to see how much parents of current students have donated so far this year and I would like to see the parent’s address and the student’s name in addition to the amount that the parent has donated. I also want to see the Parent and Child even if they haven’t donated to the organization this year. We’ll need to create a relationship report in this case because we want to see information for two related accounts.
 
We’ll start with the query to find any current students; for this example, I know that a student is current because I have a Defined Field on their account that has their graduation year (hint- https://kb.blackbaud.com/articles/Article/103649). I know what you’re thinking- why wouldn’t we just search for parents? Well, I want to make sure that I only include parents of current students. I want to see the parents whether they gave or not, but I want to limit it my search to parents of current students. Since I can use a relationship report to include both parents and students (more on that part in a bit), I’m going to build my query around my limitation of current students. The steps to create that query are listed below, but please keep in mind that your query criteria might be different depending on how you track information:

1. Click Queries
2. Click Manage Queries
3. Select the category where you would like to store this query
4. Select New Query under the Tasks menu
5. Name the query Current Students
6. Set the Starting Query to Base/All Constituents
7. Set the Data Return Type to Accounts
8. Under Criteria Matching select Match Each Criteria
9. Select UDFs- Constituent from the Browse Fields drop down menu and click on Graduation Year
10. Fill in the years you’d like to search
11. Click Save and View Queries

Let’s focus on Step 7 in the instructions above. Why did we choose Accounts instead of Related Accounts for the Data Return Type? Again, we can include both sides of the Relationship in the report. If I used the Related Accounts Data Return Type, I would return all accounts related to the current student. I don’t want that since the student might have many different types of relationships on their account that are not relevant to my search.
 
Once you have a query, it’s time to start with the Relationship Report. I’m going to list the steps to create the report below, but we’re also going to walk through the steps to clarify the process.

1. Click Reports
2. Click Manage Reports
3. Select the Category where you’d like to store your report
4. Click New Relationship Report
5. Fill in a name for the report (for example, This Year’s Donations from Parents of Current Students)
6. Select the Relationship Type from the drop down menu; I’m going to select Parent/Student from my drop down menu
7. Select the Grouping from the second drop down menu; I’m going to select Parent for this one (we’ll discuss why below)
8. Select Commonly Used Fields from the Browse Fields drop down menu and click Account Name twice and Full Address Without Country (Multi-Line)
9. Select Summary Fields from the Browse Fields drop down menu and click on Year to Date Received Total
10. Click the three stacked dots for the first instance of Account Name and set the Account drop down menu to Parent
11. Click the three stacked dots for the second instance of Account Name and set the Account drop down menu to Child
12. Click the three stacked dots for Full Address and set the Account drop down menu to Parent
13. Click the three stacked dots for Year to Date Received Total and set the Account drop down menu to Parent
14. Click Save and Run
15. Select the Category and Query name for the Current Students query that we made with the steps above
16. In the drop down menu that asks “The results of my query correspond to which half of the relationship?” select Child from the drop down menu
17. If you want to exclude people who are in the query results who do not have this type of relationship, we’ll mark the checkbox that says Exclude accounts who do not have any matching relationships?
18. Select your preferred Report Format from the drop down menu
19. Click Submit

By this point, you’ll have your Relationship Report that shows the Parent’s contact information and the amount they gave, as well as the child’s (student’s) name. Let’s dive into some of these steps to fully understand how we returned this report.
 
Steps 1-5 are the same steps you would use to approach many of the other reports you’ve created. Steps 6 and 7, though, are a bit different. This section in the report is asking us what kind of relationship we want to report and how we want to group the information. This is the first time we’ve told the database that we want to look at Parent/Child relationships (remember, the query was only looking for Student/Child information). In Step 7, we selected Parent for the Grouping because it’s possible that the Parent has multiple children in the query results. Again, I can read your mind and I know what you’re going to say- “what if the child has two parents who each have a separate account?” If that’s the case, you’ll see the child’s name listed under both parent accounts. Now, maybe you’d prefer to see the parent accounts grouped below the child’s name. No problem! You could change the Grouping in Step 7 to Child if you’d prefer. If we change the Grouping drop down menu to Child, we’ll see the Child’s name and then the Parent account(s) listed below.
 
You’ll notice that Steps 8-13 are very similar to adding report columns for your other reports. The biggest difference we have to make is that we assign an account for each column. In this scenario, we just wanted the student’s name and the parent’s contact information and giving history so most of the columns are assigned to the Parent role. However, we could have also included the student’s address and giving history as well as the parent’s; we would just need to select those fields twice and make sure each instance of the field is assigned to the correct role. In the event that you include a field more than one time (like we did for the Account Name), I suggest referring to the steps listed at https://kb.blackbaud.com/articles/Article/103977 to rename the report columns. Otherwise, you’ll have two columns titled Account Name. With only four columns in the report, like we have, it’s easy to remember which column refers to which account. However, as you start to add columns remembering which column belongs to which account can get tricky, which is why I would suggest change the column titles.
 
If you’ve run reports in the past, you’re undoubtedly familiar with Step 14 and 15, but Step 16 is a little different. This step asks us which half of the relationship the query results correspond to, which is a fancy way of asking “when you created your query, did you create a list of parents or children?” Remember how we discussed that it would be important for the Data Return Type in the query to be Accounts? This is why. If we were to preview the results of the query, we would generate a list of current students, or, people with the Child role. So, in that drop down menu, we’ll select Child. Step 17 is also unique to the Relationship Reports; that step asks if I want to include accounts that are in the query, but don’t have a Parent/Child relationship (remember, the query didn’t say anything about a relationship, it just looked for current students). In this case, I would probably mark the checkbox to exclude those accounts since my end goal is to see how much parents of current students have donated this year. If a current student doesn’t have a parent relationship listed, I don’t really need to include that student’s account in my report.
 
Steps 18 and 19 are the same final steps we would take to generate a regular old report. Remember, you can always return to your query to refine the people who return in the results or change the report set up to include different field. As I mentioned above, the Relationship Reports take some planning, but they can be extremely valuable reports that help you learn more about your data. Have any great examples of relationship reports or questions? Feel free to let us know in the Comments section below! Happy Reporting!