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 our "First Limits" client as our example client. As you can see we have the Client Profile tier 1 form and the Limits Example tier 2 form. You'll also notice I created "Global Values" for the dates but they are not tied into anything yet (notice there are no actual filters within the Evaluations Compared section yet. we see there are 5 "Limits Examples" records 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"
- Choose Apply
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.
- Choose Apply
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 "Made," "Date," and "Points(of 100)" fields 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 "Limits Examples [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]"
- Choose Apply
After applying this change we now see these results side-by-side instead of one row per record. Note: if our client "First Last" 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 (01/01/2016 through 01/05/2016) let's say we want to compare the first created record with the most recently created record between the dates of 01/03/2016 and 01/04/2016.
- 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
- A reminder, earlier in this article we mentioned that we will create Global Values in order to filter within a limit section, this is where we do that. Check Global
- Choose the Date column from the limited form ("Form Name - Column Name") in our scenario it's "Limits Examples - Date Is Between the Start Date (01/03/2016) and the End Date (01/04/2016)
- Click Apply
Now we can see in our results that our "last created" (highlighted in the "Evaluations Compared" section) is not "5th made" as it was before. It now reads "4th" on "01/04/2016" because that was the last created record in our time range. 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 "Points (of 100)." 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 "Points" value than their first overall record (so anyone who improved in score).
- Click "+" for the filter
- Check the Comparison box
- In the first dropdown we will select "Points (of 100)", ensuring that we are selecting the column referencing the "Last Created" record, then we are going to select "Is Greater Than" the "Points (of 100) [First Created]"
- Choose Apply.
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"
- Click Apply
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 section (yours will say "Number Calc" but it has already been renamed to "Improvement" in our screenshot)
- 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.
- Click Apply
We can now see that the last created within the time range versus the first created evaluation had an improvement of 75. 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