A sample report to help identify and return only the new families that have students enrolled in the next school year, but not the current school year.
The key to this report is using the Advanced Filtering option of a custom report. For some background on this, see Filter Reports Like a Pro.
What type of Report?
The data that determines if a Family has a student(s) enrolled in a given school year resides on the student's User Info Form. Therefore, you will create a custom User Report. It is also possible to pull a custom Families Report and get similar results. That Families Report is also described below, but it will only return data based on Family Applications (entirely new families to the school) without student names or Academic Levels. The Families Report will also not include Student Applications (new students to the school from current families).
In this example, we will select Active Students. You could also include Applicants if you're trying to project the number of students you MAY have in the next school year.
It's time for the Advanced Filter
We want to know Active Students that ARE enrolled in the next school year, but are NOT enrolled in the current school year. This is how we define a "New Family" in the school.
1) Click the green Add a Filter... button
2) Select Create Advanced Filter
3) Use the pulldown to Select a Property to add a rule for
4) scroll down (under the Basic page of the User Info Form data) until you find the next Academic Level: 2023-2024 field and select it. In our example, this is the NEXT Academic Year.
5) If a student has an Academic Level for the next school year, that means the student IS enrolled in that school year. So, we will select the condition to be Academic Level: 2023-2024 "Is Not Empty" as out first rule.
6) The first rule will give you a list of ALL the students enrolled in the next school year, so we need one more rule - students that ARE NOT enrolled in the current school year. The second rule is Academic Level: 2022-2023 "is Empty." Note that this year is the current school year.
That's all you need to do to get your list of new families. The report output is going to be up to you, but could go as follows...
Report Output Options
We chose to output Last Name, First Name and Academic Level: 2023-2024 of each new student. You can also include other information if desired. The Application data on a User Report will only return a value for Student Applications of current school families. To get the Application: Date Approved or other Application data for Family Applications, see the Families Report below.
This report is sorted by "Last Name" and will be output to a CSV file.
The CSV file can then be saved and opened in a spreadsheet program to further improve and refine the output data. For example, you can get a COUNT of new families and students and you can filter and count students by Academic Level if you choose to include that in your output.
It is also possible to pull a custom Families Report and get similar results. The Families Report will only return data based on Family Applications (entirely new families to the school) without student names or Academic Levels. The Families Report will also not include Student Applications (new students to the school from current families).
For this report, select custom Families, then Active Status before you create your Advanced Filters. The Application: Date Approved can be used to limit the date range of your applications, but the main filters to use are the Application: Status is Equal to "Approved" and the Application: School Year is Equal to "2023-2024".
Again, output of the report is up to you, but this is an example of what you could use. In this report, the Application: Date Approved column or any Application column will return data on the Family Application.
It is possible, with MS Excel, to combine the outputs of both of these reports in CSV format and using a Power Query, merge the data from both reports together. Please contact ClassReach Support for some recommendations on how you can do this.
This Excel sheet combines the two CSV files together, matching the Student Info with the Family Info, including the Application Date Approved.