When you build a basic report, the Apricot Report Builder tool will show you each record that has been created and that fits your criteria. For example, if you have a student who has filled out two evaluations, your report will show two rows of data for your student - one row for each evaluation they have completed.
If instead you would like to see these evaluations in one row - because you want to compare them to each other, for example - you can turn on aliases for a form that is being pulled into your report. When aliases have been turned on, you can indicate that you would like one column to show you data from the first created record, and the next column to show you data from the next or last created record. This will allow you to see data from different records in the same row of the report.
Please note that auto-run is not available when editing reports with Aliases applied. It will be necessary to publish and run the report to view the impact of your edits on your reporting results.
Begin with a Simple Report
Begin by building a simple report. In this report we will use "April Billingslea" as our example client. As you can see we have the Participant Profile tier 1 form and the Program Enrollment and Exit tier 2 form. We see there are 6 "Program Enrollment and Exit" records for April in this section, which we will use as "evaluations" for our real world example.
Comparing First & Last Created
First, we are going to limit our results. We know we will want to compare the most recently created record with the very first record created of this form. In this example, we don't care what the dates were, we just want to know the first and last created overall.
Click on the name of the form we want to limit
Click on "Last Created"
In our results we can now see we have our "last created" or most recent record displaying.
Now we will want to create our first alias. When we do this you'll notice nothing in the report has changed. We're just setting the stage for our next major steps.
Click the form for which we want to create the alias (this will always be the same form which we limited in our previous steps).
Click the "+" across from Aliases
Choose the limit "First Created" (we are choosing this because our section limit was Last and we want to compare the very First created)
Name the Alias, we can name it whatever we want but we will use "First Created" for our example.
Our next step is to bring in the fields that we want to compare from the First/Last created records into the section again. As you can see, we dragged in our "Enrollment Date" field into the section for a second time. As a side note, we would need to do this for each record we wanted to compare, for example, if we compared the "First, Next, and Next" record we would need to pull each field into the report a total of 3 times. However, in this scenario we will only be comparing First and Last.
In our final step, we need to apply the "First Created" alias to the columns that we brought into the report a second time from our previous step. The below steps will be repeated for each column to which we need to apply the First Created alias (highlighted in the screenshot):
Click on the gear icon for the column
Expand the "Form Info" by clicking on the triangle icon
In the dropdown box we will see the name of our form (the first option) and then we will see each alias. In this example, we see only the name of the form and "Program Enrollment and Exit [First Created]" which is the alias that we created earlier. Note: There will be 1 additional option per alias created. The naming format is "Name of Form [Alias name here]"
After applying this change we now see these results side-by-side instead of one row per record. Note: if our client "April Billingslea" only had 1 record within their document folder, they would not show up on this report. When working with aliases they must meet the First and Last requirement. The "Never" Form Requirement is incompatible with form limits.
Comparing First & Last Created Within a Time Range
Important: This is a continuation of our previous section, "Comparing First & Last Created", and the above steps will need to be taken before moving forward.
What if we have a unique scenario in which we don't want to compare the first created record with the last created record overall, but instead we want to compare the first created record with a specific date range. Since our data set is small let's say we want to compare the first created record with the most recently created record between the dates of 01/06/2022 and 01/07/2022.
Click the "Show" button for our "last created" limit section
In the limit section click the "+" so that we can limit our last created to a specific time range, instead of the last overall
Our next step will be configuring our Global Values at the top of our report, and then checking "Global" in the Options portion of our filter properties. This will allow us to plug the dates in our Globals into multiple filters in our report.
Choose the Date column from the limited form and select "Is Between" as your operator. In our scenario the date field is titled "Enrollment Date," and options we're selecting in the two dropdowns below the operator will be "Start Date" and "End Date," which are set to 1/6/2022 and 1/7/2022 respectively.
Now we can see in our results that our "last created" Enrollment Date (highlighted in the "Program Enrollment" section) is no longer 01/08/2022 as it was before. It now shows the date 01/07/2022 because this date now represents the last created record in our time range set by our Global Values. We told Apricot not to look overall but within a specific time period. Of course you may add more filters depending on what you need but a rule of thumb is that if you need to filter the form that is limited you will need to place the filter within the limit section. Notice: When we apply filters within a limit section we can see them in the "main" section in grey under the corresponding limit's section name.
Comparing Values of First and Last Created Within a Time Range
In this section we will continue to build upon our previous section. We already have our First and Last Created aliases. Our Last Created alias is being filtered for a specific time range and our First Created alias is just showing the first one created overall. Now let's say we want to see client progress in terms of their "Total Points," which we're now pulling from our "Points" tier 2 form. To do this we will compare their first score with their last score in the time range. This will be using a Comparison Filter.
Let's say we only want to show those clients for which their last created record within the time range contains a greater "Total Points" value than their first overall record (so anyone who improved in score).
Click "+" for the filter
Select the filter
Check the Comparison box
In the first dropdown we will select "Total Points (Last)", ensuring that we are selecting the column referencing the "Last Created" record, then we are going to select "Is Greater Than" the "Total Points [First Created]"
We can see that we have one result. Since we have a small data set, this is our only client. Now, let's say we want to see those who did not improve (this should show no results).
Click back into the filter
Click the "Inverse" checkbox and notice the "Is Greater Than" changed into "Is Not Greater Than"
We can now verify that we had no clients who did not improve. Now let's say we want to know by how much each client improved. To do this first we will need to undo our last steps. Please click into the filter and uncheck "Inverse" before continuing.
We need to add a Number Calc column to calculate the improvement: In the Field Choices palette click the triangle next to "Special Columns," then click the triangle next to "Calculation Columns," and finally drag the "Number Calc" column into the section.
Click the gear for the Number Calc column
You can name the column whatever you'd like, we named it "Improvement" in our example.
Click on Subtract
Select the columns you would like to include in the subtraction.
We can now see that the last created within the time range versus the first created evaluation had an improvement of 78. Note: You can reference the product of a calculation column within another calculation column for more complex calculations.
Tips and Tricks
If you have a report that has a First Created and a Last Created alias and there is only one record for the client, Apricot will show that record twice. This is because it was the first and last created record of that client.
For every alias the client must have at least one matching record to fill that alias. For example, you have a report that has a First Created, Next Created, and a Next Created alias. That client must have at least 3 matching records or they are dropped from the report. Note: Not only must they match the First, Next, Last, etc. requirements they must also match any filters applied
Filters applied in the "main" section function differently than filters applied in the "limit" section. More often than not filters will need to placed in the limit section, particularly date filters. Remember: Filter in Limit section: Shows any record that is within the time range (first or last) - generally has resultsFilter in Main section: Shows records only if the first or last record was created within that time range - generally does not have results
If you are using a filter that is from a form which is being limited, you must generally put all of those filters within the limit section. For example, if you are filtering on "Date" and "Test" (Pre Test vs Post Test) you will need to put those within the limit section. There are exceptions: if you are filtering on "Date" and "Class" the Date filter will need to be placed in the limit section while the Class filter might be able to be placed in the Main section, since all classes should be the same. When in doubt, place the filters in the limit section.
Order matters for aliases. You can do: First Created, Next Created, Next Created, Last Created. But you cannot do: First Created, Next Created, Last Created, Next Created. (Last Created doesn't have anything after it so Next will not work).
Applying a limit or alias adds an additional section to the report for every limit/alias applied, which can cause reports to run more slowly.
You will be unable to add an alias if the filter logic includes OR; every operator between filters must be AND