Comparison reporting allows the Apricot Report Builder to compare data that has been collected in different records of the same form - for example, if you collect evaluation or test scores in one field on one form, comparison reporting makes it possible to compare the first evaluation or test record that was created for a person to any subsequent or final evaluation score that was created for the same person.
Uses for Comparison Reporting
Compare pretest and post test scores for an individual or household
Compare projected numbers against actual numbers
Compare an initial/baseline assessment score to the most recent score
Compare an initial/baseline score to an interim/incremental score and to a final score
Compare the most recent assessment to a previous assessment
Compare progress by year or by specific time frames or other filtered results
For each of the above cases, adding special columns like number calc, section summary, and section count columns to a report allows us to calculate the following:
Difference in scores
Percent of change based on that difference
Change relative to the desired change
Individual client/household/program's level of change relative to the group average
For more information on special columns and how to add them to your reports, please check out Calculation Columns and Summary Columns.
A Report Without Comparison Reporting
The example above shows a simple report that tracks points records for each of these participants. Multiple clients in this report have multiple "Points" tier 2 records stored in their Document Folders. Without comparison reporting, we have a lot of data but it is hard to figure out if there are any trends in what has been happening.
A Report With Comparison Reporting
Here is the same data set with comparison reporting. Now each client has one line of data that tracks their scores over time. Having their information on one line allows us to add mathematical functions to interpret the data.
Building a Comparison Report
Begin by building a simple report. Comparison reports operate by comparing records created under similar circumstances over time - these are usually Tier 2 records. Find the field on the Tier 2 form you would like to compare and pull it into the report at least two times. In the example above, we are going to compare the client's practice test scores, so this column has been brought in twice.
Adjust Form Properties
Click on the form which you want to compare records, in this scenario it's our "Points" form
We will first apply a "First Created" limit to the entire report section. What this does is ensures that at this point in time we only see the first created record for each tier 1 record (Participant Profile in this scenario).
Click the "+" sign to create our alias for our comparison
In this example we want to compare the first record to the last record so for the aliases' limit we will choose "Last Created"
In the Name box we will call this Last Created, but it can be whatever makes sense for your report scenario.
Once limits/aliases have been turned on for your report section, the report will automatically create one new limit section for each limit/alias.
These sections are hidden by default, but you can view them by clicking the "Show" button.
Aliases/limits are designed to reduce the rows of data in your report to fewer lines - maybe as few as one line per person or one line per Tier 1 record.
Limit sections are created as a way for your to double-check your data to make sure the record that has been pulled into your alias report is the one you intended.
More information about limit sections can be found here: Advanced Report Features: Limit.
Adjust Column Properties
Click on the gear box for the column(s) that we want to show for "Last Created" (Note: If you haven't already done so already you'll need to drag in the fields you want to compare from the records a second time)
Expand the Form Info area
For the Form dropdown click it to open it to see our limits/aliases to choose from. The "First Created" form limit (for the entire section) will have the name of the report. Since we know the report section, by our limit default, is showing the first created we want to apply the "last" created which is shown in brackets.
Choose the alias we want to show
Choose Apply and then repeat as needed
Add a Special Column for Interpretation
Now that the data has been reduced to one line per person, we can find how much has changed over time with a calculation column. In the example above, we added a Number Calc column to the report which:
Took the total points from the last created
Subtracted it from the total points from the first created
To see how much they changed over time.
If you have multiple clients in your database you can even set the Points Difference column to show the average change. Additionally, you can have multiple aliases to compare more than just the First or Last but First, Next, Next as an example. Note: Be aware for every alias the client must have at least that many matching records or they will be dropped, Apricot will not just add a 0. If there are 3 aliases and they only have 2 records, they will not show up.