BASIC REPORTING PART V: Adjusting Report Output (STRONGLY RECOMMENDED)

BASIC REPORTING PART V: Adjusting Report Output (STRONGLY RECOMMENDED)

1. The report output is what you will see on the "published"/run version of the report. That is, instead of seeing field names, you'll see the actual data being pulled into and displayed on the report. Below are screen shots of an Express View and an Advanced Report in edit mode and in run/live data mode. When we talk about the report output, this is what we are talking about. In these screen shots, you can see "edit mode" and the run report (live version) in Express View (first two screen shots) and in Advanced Reports (2nd two screen shots).
2. The report output is what you see on the finished report - it may not yet be formatted (made pretty) or it can be the finished, "pretty" version of the report (with formatting, borders, sub-totals, etc.). For purposes of this tutorial, we're going to talk about things like font, formatting, borders, etc. as "formatting" (which is covered in the Basic Reporting Part VII tutorial) and manipulating the way the data displays on the report as "output." For example, manipulating the output to hide data you don't want to see (and that couldn't be filtered out) using Conditional Formatting will be covered in this step (because you're manipulating the data being shown). Conditionally formatting the report output to highlight any donations over $500 in green background shading will be covered in the Report Formatting tutorial (because in this case you'd be manipulating the formatting of the report, not the data/output).
3. Generally, when writing reports, you will:
   • a) decide what kind of report you are trying to create (data, information/comparison, or knowledge/analysis),
   • b) pick the correct report writing/creation tool/interface (advanced search, event attendee export, financials export, Smart Dashboard, default report, custom report using Express Report interface, custom report using the Advanced Reports interface, custom report using the Dashboard interface, custom report using the Cross-Tab interface, etc.),
   • c) add filters to the report,
   • d) choose how to sort the output, and then
   • e) add fields to the output
   • f) troubleshoot the report/determine if the report is pulling the correct data
   • g) format/finalize the report
   • h) use the report for its intended purpose (export it, read it, use it to conduct analysis, etc.)
4. You may do some basic arranging of the output fields on the report to get the layout you want (vertical versus horizontal, get the fields in the order you want them to appear, etc.), but generally, you do not yet want to format the report ("make it pretty") because you first want to verify that the data is correct (which is covered in the next tutorial). Generally, after laying out the report and manipulating the output, you will then troubleshoot the report, and finally, then you will add formatting/make the report "pretty." At first, it may seem strange to think about the output and the formatting as separate steps, but it's important to think about "manipulating the output of the data" as different/a separate step from "manipulating the report's appearance."
5. When working with reports, it's important to understand some of what you might see on the report output and why the data is appearing the way that it is. Additionally, there may be times you want to manipulate the report output before you get to the verifying the data and "making it pretty" stages. This can include hiding data you want to see but couldn't filter out, rolling up multiple rows per contact so that you only see one row per contact, applying formulas to the report to calculate totals or convert data from one format to another, and applying formulas/functions to combine date from multiple fields to show in one field.
6. There is an endless amount of ways to manipulate the report output. One of the most basic is adjusting the table joins. When you combine data from two or more tables on a report, the report has three options of which combination of data to show you: a) all the data from Table A and any data from Table B that matches, b) All the data from Table B and any data from Table A that matches, or c) Only data where there there is a match between Table A and Table B. For example, let's say we were writing a report that includes Donors (Table A) and Members (Table B). The report could show us: Option 1 - All of our donors and any memberships they have, Option 2 - all of our members and any donations they have, or Option 3 - only people who are both donors and members/only donors who have memberships and/or only members who have donations. Table Joins are the ways the various tables in the report talk to each other and control which of these three options the report chooses to display the data/output. If you find that your report isn't showing displaying the combination that you want (for example, showing all of your contacts even if they haven't made a donation even though you included a filter on the report for "contact type is donor"), then you need to adjust the table joins. This tutorial will explain how to do that. Table joins can only be adjusted in Advanced Reports. They cannot be adjusted in an Express Report. Advanced Search will always only utilize the third option (only show contacts that meet your criteria). In the screen shots below we have adjusted the table joins. See how the report output/the data shown on the report changes just by changing the table joins? We haven't done anything else to the report except adjust the table joins.
7. Another adjustment you might want to make to the report is to display sub-totals or counts (for example, the total amount donated per person or the total # of people who attended your events each month). In the Express View interface (top screenshot below), this is pretty straight forward (just add grouping and the sub-total row appears automatically). In the Advanced Reports interface (2nd screen shot below), it involves adding grouping and then a formula, but it's fairly straightforward once you understand the steps. Refer to the Advanced Reports interface tutorial for how to add grouping and sub-totals to an Advanced Report.
8. Using a variety of formulas/functions, you can convert data from one format to another or change the way the output of a field displays. Formulas and Functions can be used in Express View reports, Cross-Tab Reports, and Advanced Reports.
   
   • For instance, perhaps you don't want to see the exact date of each donation; instead, you just want to see the Month the donation was made in (perhaps you plan to group the report by Month so you get a sub-total of the amount raised per month). You can apply the "Month" function to the donation date field to display just the Month of the donation instead of the entire date (display "2" instead of "02/10/2017".
   • You can use the "Month Name" function to convert the month number to the month name (display "February" instead of "2") to make the report easier to read.
   • You can use the "Concatenate" function to display the value of two or more fields in one cell/field on the report output (for example, use "Concatenate" to create a custom salutation field that displays the first name of Person 1 (adult), Person 2 (adult), Person 3 (child), and Person 4 (child) in the household together (such as "Dear Lisa, John, Micheal, and Katie").
   • You might want to use the "Filter" function to display the filter values on the report, so that when you run or print the report it shows what criteria the report was given (for example, to display the date range for the donation date that was put into the filters).
   • You can use an "if/then" statement to display one value in a field if a certain condition is met and a different value if it's not. For example, you could chose to display in one field/cell the household salutation if the contact is in an household and the individual salutation if they are not. In this way, you can create one field that has the correct salutation no matter what type of contact it is to use for a mail merge. Or you could use an "if/then" statement to display on the report the word "Major Donor" next to any donors who donated more than $1,000 and "General Supporter" next to any donors who donated less than $25. Or the word "New Member" next to anyone who just became a member for the first time this year. Or to display the donation amount if the donation was made during a certain time period, as we have done in the screen shot below, to create a psuedo-crosstab report.
   • You can also use if/then statements to segment data and/or make data go across the page instead of down, such as in our example of the pseudo cross-tab report of donations per year.
   • Additionally, you can use "cell references" to display the value of one cell/field on another part of the report. For example, charts and sub-totals must go in a group, page, and/or report footer and can't be put in a header or the details section. But using cell references, you could display what's in the footer in the details or header. In this example below, we are using an if/then statement to show just the 2016 donations in Column E, just the 2017 donations in Column F, etc. and then we're redirecting those amounts to the sub-total row and using the AutoSum function to total up all those amounts (we can't just use the "AggSum" formula in the footer here because this isn't excel - there's no way to say "sub-total this column/the values above this cell" and we can't say "AggSum/total up the donation amount" because we want it to only total 2016 donations in Column E, just 2017 donations in Column F, etc. We could have written a more complicated formula in which we said "AggSum(If(year(donation date)="2016",donation amount,"") [in lay terms "total up the donation amount only for donations made in 2016) but using a cell reference and the auto sum function to get the sub-totals is a bit more elegant/involves having to write a less complicated formula. But both are correct/can be done.

     

9. When you create a report, it's important to understand that the report will display one row per unique combination of data. For example, if you have a report that shows each person and all their event registrations and all their memberships, the report has no way to logically display memberships and event registrations on the same row, because they don't go together. If Sally went to your 4th of July Afternoon Tea and your Art in the Park Tour and also, unrelatedly, holds two memberships, how can the report display that data? Which donation goes with which event? They don't - they don't go together at all. So the report will display all possible combinations of the data - EVEN IF YOU AREN'T DISPLAYING THE EVENTS AND/OR MEMBERSHIPS ON THE REPORT. See the two screen shots below - in the first, we are displaying events and memberships. See how Abby Jacks has her events and memberships repeated, one row for each combination? This is normal/expected behavior in the report because the report has no logical way to display the data otherwise. In the second screenshot, we have removed the memberships from the report but the events are still repeated multiple times.
10. You can manipulate the output of the report in the Advanced Reports interface to control/adjust for this several different ways:
    • remove any unused/unneeded tables from the report
    • toggle the Cartesian Processing setting on the table joins pop up box to "true" (only available in Advanced Reports interface)
    • collapse multiple rows per person/event/fund, etc. using grouping. Grouping can be added in Express View and Advanced Reports. Advanced Search, by definition, only returns one record/row per person. See this tutorial for more information on how to adjust the output to get one row per group (person, event, date, fund, etc.).
    • use repeating groups (available only in Advanced Reports) to separate out the data that can't be combined (such as memberships and donations) into their own sections so the report isn't trying to combine them (and thereby repeating data/making extra rows). In this report, we've added memberships to their own repeating group, following by events in their own group. Now the data isn't combined into multiple rows and is much easier to read.
11. Additionally, you might want to hide data off the report. Sometimes we can't use filters or table joins to keep data off a report. For example, there is no way to filter by a formula, so it's not possible to filer a report based on a sum (for example, if you only want to see people who gave above or below a certain total amount in 2017, there is no way to do that using filters, because you can only filter on individual donation amounts, not a total/AggSum formula). So sometimes you have to let data come into a report and then you can use conditional formatting or if/then formulas to hide data.
12. Keep in mind that changes to the output (data being displayed on the report) do not in any way affect the back end of the report (the mechanics the system is using to build the report). Not displaying fields on a report does not impact the structure of the report or stop that data from being pulled in; it just keeps it from being displayed on the output/finished report.
13. We have numerous tutorials following this one that cover several formulas/functions that will allow you to manipulate the output of the report. We encourage you to peruse them to get a sense of the kinds of things you can do inside of the reports module to control and change the way the report output displays. Certainly, there are many, many more beyond what we have enumerated in our tutorials. You can manipulate the output of a report in countless ways. If you have any questions about how to manipulate the output, just email us at support@fundly.com and ask!