The Limit feature in the Report Builder can display the record that was created the earliest (First Created) or latest (Last Created) for a Tier 1 record. You can also apply filters to a limited report to define a time period in which you're looking to display records from. Whether you apply the filter to the limit section or the "main" report section determines how limited the returned data set is.

Follow the steps listed below to learn how to create a report with limits and filters to show first or last records within a certain time period.

In our first report, we'll look for the first service provided to each participant within a specified time period. To do this, we've pulled fields from our Tier 1 Participant Profile form as well as our Program Enrollment and Service Tracking Tier 2 forms into our report section. The report then shows all of our participants' Service records.

We want the report to only display the first Service record created for each participant, so the form we'll limit is the Service Tracking form. Click the name of the form at the top of the report section, then select the First Created limit in its Form Properties and click Apply.

This will generate a limit section that shows every participant's first Service record created no matter when it was recorded. You can click "Show" in the main report section to expand the limit section.

Note: The fields that appear in a limit section vary based on the form you limit. Limiting a Tier 2 form will show the record IDs for both the Tier 2 form and the form it's linked to as well as the first five fields set to Quick View in the Tier 2 form. Limiting a Tier 1 form that's linked to another Tier 1 form will pull the record IDs for both forms and the field which determines the Document Folder name for the limited form. If this is a name field, it will pull both the first and last name.

Add two Global Values at the top of your report that correspond with the start and end dates of the time period you want to report on. To learn more about Global Values, click here.

In our example, we want to see the first created Service record for participants between 6/1/2023 and 6/30/2023, so we've added those dates as our Global Values.

Note: We always recommend using Global Values when filtering within limit sections (especially with date filters). Without Global Values, users are unable to change filter values within limit sections in Run mode unless you un-hide the limit section, which makes the report more confusing for most end users.

Now we can add a global filter within the limit section (as opposed to the main report section). Click the + icon to the right of Filters and check the Global box, then create a rule that filters your limited form's date field between your two Global Values.

Click Apply, then refresh the limit section to display the updated data set. Now the limit section shows whichever record is the participant's first record created within our Global Value date range. If a participant had no records created within this time period, they are not included in the limit section.

You can use limits and filters slightly differently from the previous example to only show participants in a report section if the very first record of the limited form is within the selected time period. The main difference is that the filter is placed in the "main" report section as opposed to the limit section, which is a rarer instance in Native Reporting as it's more exclusionary and produces a smaller data set.

Copy the first section we created to the same report so we can make this change without disrupting our previous work. To learn how to do this, click here. This will bring in the same forms and maintain the First Created limit we applied to the Services form.

In the copied section, Show the limit section then click its global filter to delete it.

Then, scroll back up to the (copied) main report section and add the same global filter here then Apply.

After publishing and running the report, there is one participant who was included in the first section that is not included in this new section. While they had a Service record created between 6/1/23 and 6/30/23, their very first Service record was created before this date range which excluded them from our new filter.