Users may want to exclude an entire Tier 1 record from a report if at least one of its Tier 2 records meets a certain criteria. For example, if you track goal progress over time or course completion in the same form across multiple records, it can be beneficial to exclude participants who have completed this evaluation process from your report entirely.
Follow the steps listed below to learn how to filter out Tier 1 records from report results based on their associated Tier 2 records.
Step 1: Select your column of interest
For our example, we'd like our report to only show Tier 1 Participant records who have never participated in an Intake Orientation. While they may have received other services that were recorded through the Tier 2 Service Tracking form, we want to have this baseline form recorded in their Document Folder to better track their progress over time with our organization's services.
If we created a filter that excluded any Tier 2 Service records with an Intake Orientation service type, we would still see Service records of other service types for Tier 1 records who have already had an Intake Orientation. Therefore, we'll be using a combination of custom expressions and group filters to achieve the results we actually want.
Step 2: Create a custom expression
Open the Column Properties of the field you're filtering by (ex. Service Type). Select the Custom Numeric data style, then copy the following expression into the box:
IF ([column] = "Criteria Value", 1, 0)
Replace "Criteria Value" with the column value you're looking to filter out of the report. In our report, we're removing any Tier 2 record with a Service Type value of "Intake Orientation".
This will change the value for any record with the Intake Orientation service type to "1" and all other records with different service types to "0". Refresh your Summary options and select Total, then click Apply to update the column.
Step 3: Add a group filter
Open the Column Properties of a Tier 1 form field used to identify each record, like the Record ID.
Group the report section by this column, then select Apply. This will establish the groupings we need to apply a group filter.
Next, click the + icon next to Group Filters at the top of the report section and select the Tier 2 column that's displaying the Total Summary value in the first dropdown field (ex. Total Service Type). We'll set the rest of the rule to "Equals 0". This will filter out all grouped Tier 1 records that have a Total Service Type not equal to 0.
In our example, this excludes all individuals with any Intake Orientation record currently displaying a "1" from the report.
Step 4: Save and publish the report
Publish your report to view it in Run mode. You can then expand any and all groupings to find that no Tier 1 record included in the report has a Tier 2 record matching the criteria you filtered out.
