This article will guide you through our best practices for reporting. Keep in mind that Apricot sites can be customized and vary greatly. Your individual setup may be completely different from our example to the point that this article may not apply. However, this is based on best practices (and our Best Practices: Form Structure article) and you should be able to apply most concepts to your site's unique setup. This article assumes you already know the basics of Report Building (such as adding fields to a report, creating multiple sections, and using Global Value filtering).
Global Value Filters
Global Values are values that can be referenced in multiple filters within multiple sections within a single report, and it is a best practice to utilize these as it gives you greater control of the report. Keeping filters "locked" means that standard users will not be able to inadvertently adjust filters in a way that can cause report errors. For example, adjusting an unlocked date filter and switching the referenced column to another which needs a text input instead will break a report section. Because filter criteria can be updated even for locked filters when using Global Values, we advise using Global Value Filters as much as possible to prevent this.
There are some scenarios where you shouldn't use Global Values, for example if you know you'll need standard users to filter for "Service" and you offer, let's say, 100 different services: we don't want 100 global value filters.
Most reports will need date filters for Start Date and End Date. In addition, many reports will need a Program filter, so most reports will need 3 Global Value filters.
In the screenshot above we can see our 3 filters. When constructing them make sure to choose the correct "Type" setting for each Global Value filter. For example, Start Date and End Date should be set to "Date" while Program should be set to "Text." Some additional notes:
For all Global Value Date filters you should enter in a "default" and these defaults should be in MM/DD/YYYY format. If you don't, when the report first loads up there will be no results and may appear to be broken by users running the report
For "Text" Global Value filters, whether or not you should use a default value will depend on the section filter it is feeding into. If the section filter is set to "Equals", leaving the global value blank will mean that the report is filtering only for null values. Alternatively, if the section filter is set to "Contains" you don't need to use a default value because a blank value will return every program. For that reason, using "Contains" is very advantageous because it provides the flexibility of filtering for a specific program or seeing results for every program.
The Root Form
In our screenshot above we have a brand new empty section. Our next move is the most important step when creating reports. We can't state it enough, what we do next is very important as it will dictate the success and accuracy of the report.
We need to pull one field into this section to begin our report building and in doing so we will determine our "Root" form, i.e., the form that everything else in the report relates back to.
You may be thinking, "I want to build a report to show all services provided so I'll start with the Services form". Stop. While we can see how this might seem to make sense, pulling in a column from the Services form first means that everything pulled in after will be forced to relate back to our Tier 2 services form. In reality, we know that any tier 2 form actually relates back further, to the Tier 1 form (Participant, Client Profile, Individual, etc.). Almost 100% of the time you will want to begin with your Tier 1 form as the root form.
However, there is a caveat here. If your system is configured with a Household Tier 1 form (that is linked to the Tier 1 Participant form) you will want to start with that form. This way, you can determine which Individuals are within which Household.
You may be wondering though, if the Household is linked to the Participant can't the Participant be the Root form and the Household be pulled in, kind of like a Tier 2? While this is technically possible, it is inadvisable because it will cause duplicated rows for individuals.
You might also wonder about scenarios in which you don't care about Household information for the report you're building, can you start with the Participant form then? Yes, this is a bit less inadvisable. However, if you start with the Household form this will be a good habit and will allow you to expand the reporting capabilities if needed in the future. Once a Root form is set you cannot change it, so a section would need to be rebuilt if the root form is set incorrectly.
Drag in a field from the Household form first (this sets Household as the Root form). Notice how it's at the top of the "Forms" list
Then, drag in a field from the Participant form (order is very important, we will start with just one field from each form we plan to use).
Notice how "Participant" is listed just under the "Household" form and also indented, this is what we want to see. This indicates that the configuration is correct.
Tier 2 Forms
Now that we have both of our Tier 1 forms in our report it's time to start bringing in our Tier 2 forms. Our end goal is to report on Services but that will not be the next form we bring in. Remember, order is important. Those Services may not mean much in this report if we don't know which Program they relate to, so our next step is to bring in our Program Enrollment.
Notice how we are still getting that nice "cascade" in the "Forms" list area. This is also important, this tells us each form relates specifically to the one above it, as indicated by the indentation. Now it's time to bring in our Services form.
Stop. We have a problem. Look closely at the "Forms" list area. Notice how Services is directly under the Program Enrollment form, and is not indented. This means that Apricot thinks that records of those two forms don't specifically relate. We know this isn't true because we know that we use a linking field which associates specific records of these two forms.
What happens if you leave it like this? You will see duplicates and that will throw off any Totals or Summaries (even calculations) that you may want to use. Before continuing, please review the Reporting with Multiple Tier 2 Forms and Linking Fields article for a very detailed explanation.
Now, let's remind Apricot that there is a linking field and that it should reference that linking field so we do not get any duplication.
First, click on the name of the Form in the Forms List, "Services Provided", to open it's properties
Choose "Program Enrollment"
Since our Services Provided form only has 1 link to the Program Enrollment form we don't have more than one option. Forms which have multiple linking fields will show multiple options, so be mindful of which is used.
You will need to refresh your browser for the changes to take affect
Now that we have at least 1 field from each form we want to reference in our section and have ensured that the relationships between them are configured correctly we should Publish the Report.
Click "Publish Report"
Now the report changes are "live" and we cannot undo changes prior to this point. This is a good thing, this allows us to use the "Revert Report" button in case we encounter an error while editing and can click that button to return to this published state.
Publish as often as you can. Let's say that you build a very large report but never publish it. Let's say it took you a week to get the report to this point, when suddenly you make a change and the report errors out and you reach out to Ongoing Support. Unfortunately, without a published state to return to the chances of the report being recoverable is very slim. If you had instead published at various points throughout the course of constructing the report, it would have greatly raised the odds of it being recoverable. Furthermore, if you had published often, the report would return to a state which requires far fewer edits to return to the needed outcome.
Add Remaining Fields/Set Column Properties
Now we can add any of the other fields from our forms at will. Since our form list is set, fields can now be pulled in from any form included in our section and in any order from here on out.
In the screenshot above we have added the minimum amount of fields that we believe will provide an optimal report without using too much of the client's personal information:
Household Record ID: Notice that we have this Grouped (column headers with a white font are grouped). Because it is grouped the report will allow us to see which individuals were served and to which Household they belong. We have also set a summary for this column, "Count Distinct"
Individual Record ID: This is set to Count Distinct and is a good alternative to using their name
Date of Birth: This can be used in a few different ways: to find "Current Age" or more importantly Age at Enrollment (we'll touch on this later)
Enrollment/Exit Record ID: This is set to Count Distinct and can allow us to find how many enrollments have occurred.
Start Date and End Date: These dates are pulled from the Enrollment/Exit records and will be used for filtering to determine who was enrolled in a Program during the reporting period.
Program: This will allow us to see the name of their Program (and determine which Service is related)
Service Record ID: This is set to Count Distinct so we can determine how many unique services we provided
Date of Service: This will be used to filter for Services within our reporting period
Service Provided: This will show us the specific service(s) a client received
Now that we have all of our columns in the report it's time to begin filtering. This is where it can become very complex and confusing. We will start by adding a minimal amount of features and then describe the type of results it will produce. Then, we will add a couple more and describe how that changes the results.
We've added the Program filter first. Make sure that Global Value is checked and pick your Program, then we chose "Contains" since it allows the most flexibility. Notice the open and close brackets with nothing in between indicating that our Global Value has no "default" but remember that since we are using "Contains" and not "Equals" that is okay. We picked this first because this will always be desired in our filtering.
At this point our report is looking for all Individuals with a Service record, with the possibility of limiting results by Program.
Next we have included a filter for Date of Service. Again, using Global Values (Start and End Date) with the "Is Between" option to make sure we include those dates.
At this point our report is looking for all Individuals with a Service record, with the possibility of limiting results by Program, who have a Service which occurred between 01/01/2021 - 12/31/2021
This means that the report isn't looking at anything beyond the Services, so we want to also make sure that the Program Enrollment itself was active in the time frame as well, further limiting our results.
This can be done by applying filtering to our Start Date and End Date, and then adjusting the Filter Logic.
Start Date: we need 2 filters (1) of "is" and (2) of "is before" since Apricot does not include an "is equal to or before" filter. Because of this we need to use "or" logic (change the "and" to "or" you can type in this box). Make sure to put the parentheses around the 3 and 4, it's super important to do so. Notice how the filters have numbers so you know which filter is which number
End Date: we need 2 filters for this as well (1) "Is Empty or After" and (2) "Is." Again, we need to update the Filter Logic, change the "and" to "or" and use parentheses to capture both end dates.
As a general rule, if you use "or" logic you will almost always want to use parentheses around the two filters that have the "or" in between them as long as they apply to the same field, such as End Date being used twice. You may also like to use them to ensure it is filtering as expected. There are more complex filter logic options too. Let's go over some hypothetical examples:
Program filter --> Program A
City Filter --> Austin
Start Date Filter --> 01/01/2021
End Date Filter --> 01/31/2021
Example One: Show us all Services in January 2021 where the Program = A as well as any service provided in the city of Austin even if it's not Program A
Filter Logic: (1 and 3 and 4) or 2
Example Two: Show us all Services in January 2021 where the Program = A and the city is Austin
Filter Logic: 1 and 2 and 3 and 4
Example Three: Show us all Services in January 2021 or any Service (regardless of date) that Program = A and city is Austin
Filter Logic: (1 and 2) or (3 and 4)