Advanced Filtering
Overview
The Advanced Filtering option uses Compass Query Language (CQL) to enable you to search for more complex conditions outside of the Basic filter capability to generate reports of data contained in user accounts. There is the option in both Basic and Advanced Filtering to save your filters for regular ongoing access.
Once you have created your filter, you can export the data to a .csv file if required.
Permissions
Staff who have access to the People Management page (either with the PeopleManagementBase permission or UserRecordsAdmin permission) will have access to Advanced Filtering.
For information on permissions in Compass, please refer to the 'Permissions' article from our Knowledge Base.
Creating an Advanced Filter
To create an Advanced Filter, on the People Management page (under the Organisation menu) click 'Advanced'.
You can then use the available field to enter your query using Compass Query Language (CQL).
A query constructed in CQL will need a 'field', followed by an 'operator', followed by one or more 'values' i.e. if you are wanting to extract a list of all user accounts with the first name of 'John', the CQL query would look like this, "Firstname" = 'John'. In this example, 'Firstname' is the field being searched, '=' is the operator and 'John' is the value.
If the query you have entered is valid, the field will change to green and you can click 'Apply' to commence the filtered search.
The results will then populate based on the query.
When creating a query, when you begin to type a field, it will display the list of possible fields available.
Once you have selected the field, it will prompt you to select the applicable operator from the available options.
Once you have selected the operator, depending on what field you selected, you will need to either choose the value from a list of options or type in the value (i.e. if the field is 'Title' you will see a range of possible titles to choose for the value, whereas if the field was 'Firstname' you will be able to type the name you are searching for as the value). Please ensure if you are typing in the value that you use single or double quotation marks around the value.
You can create more complex queries that look for users with a combination of data by using 'keywords' i.e. you could search for users with the first name John and who were born in Australia by using "Firstname" = 'John' AND "BirthCountry" = 'Australia' as your query with 'AND' being the keyword in this query.
Compass Query Language Options
Available Fields:
| Field | Value Type | Field Content |
| ArrivedInCountryTimestamp | Date | A date in dd/mm/YYYY or ddmmYYYY format (i.e. 01/01/2001 or 01012001) |
| AuthenticationMode | Text | One of a defined range of options |
| BirthCountry | Text | A country from a defined range of options |
| BirthTown | Text | A town from a defined range of options |
| CEIDER | Text | A sequence of entered numbers i.e. "123456789" |
| ContactNotesAh | Text | Text as entered/imported |
| ContactNotesBh | Text | Text as entered/imported |
| CustomFields.AncillaryStaffFTEFortnightlyHours | Text | Text as entered/imported |
| CustomFields.AncillaryStaffHours | Text | Text as entered/imported |
| CustomFields.CurrentTeachingYears | Text | Text as entered/imported |
| CustomFields.CurrentTeachingYearsAtCatholicSchools | Text | Text as entered/imported |
| CustomFields.FirstDateAtDiocese | Text | Text as entered/imported |
| CustomFields.StaffCustomFields | Text | Text as entered/imported |
| CustomFields.TeachingStaffPrimaryFTE | Text | Text as entered/imported |
| CustomFields.TeachingStaffSecondaryFTE | Text | Text as entered/imported |
| DateOfBirth | Date | A date in dd/mm/YYYY or ddmmYYYY format (i.e. 01/01/2001 or 01012001) |
| DateOfDeath | Date | A date in dd/mm/YYYY or ddmmYYYY format (i.e. 01/01/2001 or 01012001) |
| Disability | Boolean | Will be either 'True' or 'False' |
| DisabilityFunded | Boolean | Will be either 'True' or 'False' |
| DoNotContact | Boolean | Will be either 'True' or 'False' |
| EmailAddress | Text | Text as entered/imported |
| Employer | Text | Text as entered/imported |
| FeeMode | Text | One of a defined range of options |
| Firstname | Text | Text as entered/imported |
| FormerLastName | Text | Text as entered/imported |
| FormerName | Text | Text as entered/imported |
| FormGroups | Text | One of a defined range of options |
| Gender | Text | One of a defined range of options |
| HeadLiceCheck | Boolean | Will be either 'True' or 'False' |
| Houses | Text | One of a defined range of options |
| ImportMode | Text | One of a defined range of options |
| IndigenousType | Text | One of a defined range of options |
| Integration | Boolean | Will be either 'True' or 'False' |
| InterpreterRequired | Boolean | Will be either 'True' or 'False' |
| LanguageBackgroundOtherThanEnglish | Boolean | Will be either 'True' or 'False' |
| LastName | Text | Text as entered/imported |
| LivingArrangements | Text | One of a defined range of options |
| MiddleName | Text | Text as entered/imported |
| Nationality | Text | One of a defined range of options |
| NonSchoolEducationBackground | Text | One of a defined range of options |
| Occupation | Text | Text as entered/imported |
| OccupationalStatus | Text | One of a defined range of options |
| OrganisationTitles | Text | One of a defined range of options |
| OrganisationUserEmail | Text | School email from staff/student tab |
| Parish | Text | Text as entered/imported |
| PersonalEmail | Text | Email from Basic Details tab |
| PhoneNumbers | Text | Text as entered/imported |
| PreferredLastName | Text | Text as entered/imported |
| PreferredName | Text | Text as entered/imported |
| ReceivesEslAssistance | Boolean | Will be either 'True' or 'False' |
| RefugeeStatus | Text | One of a defined range of options |
| Religion | Text | One of a defined range of options |
| ReligiousInstructionAllowed | Boolean | Will be either 'True' or 'False' |
| ReligiousOrder | Text | Text as entered/imported |
| ReligiousOrLay | Text | One of a defined range of options |
| RequiresEslAssistance | Boolean | Will be either 'True' or 'False' |
| ResidentialStatus | Text | One of a defined range of options |
| Role | Text | One of a defined range of options |
| SchoolEducationBackground | Text | One of a defined range of options |
| SchoolName | Text | Text as entered/imported |
| SecondMiddleName | Text | Text as entered/imported |
| Status | Text | One of a defined range of options |
| StudentType | Text | One of a defined range of options |
| TimeAllocation | Integer | Number as entered/imported |
| Title | Text | One of a defined range of options |
| UserIdentification.Aus.Govt | Text | ID Number as entered/imported |
| UserIdentification.AustralianVisa | Text | ID Number as entered/imported |
| UserIdentification.BirthCertificate | Text | ID Number as entered/imported |
| UserIdentification.Chilred-relatedEmploymentCheck | Text | ID Number as entered/imported |
| UserIdentification.CPRQualification | Text | ID Number as entered/imported |
| UserIdentification.DisabilityId | Text | ID Number as entered/imported |
| UserIdentification.DriversLicence | Text | ID Number as entered/imported |
| UserIdentification.EnrolmentAttachment | Text | ID Number as entered/imported |
| UserIdentification.FirstAidQualification | Text | ID Number as entered/imported |
| UserIdentification.HealthCareCard | Text | ID Number as entered/imported |
| UserIdentification.HumanResourcesId | Text | ID Number as entered/imported |
| UserIdentification.IdentityManagementIdentifier | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(ArchdioceseofCanberraandGoulburn) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(ArchdioceseofHobart) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(ArchdioceseofSydney) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(CEnet) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofArmidale) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofBathurst) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofBrokenBay) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofCairns) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofLismore) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofMaitland) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofParramatta) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofRockhampton) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofTownsville) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofWaggaWagga) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofWilcannia-Forbes) | Text | ID Number as entered/imported |
| UserIdentification.IDMHRIdentifier(DioceseofWollongong) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(ArchdioceseofCanberraandGoulburn) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(ArchdioceseofHobart) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(ArchdioceseofSydney) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(CEnet) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofArmidale) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofBathurst) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofBrokenBay) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofCairns) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofLismore) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofMaitland) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofParramatta) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofRockhampton) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofTownsville) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofWaggaWagga) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofWilcannia) | Text | ID Number as entered/imported |
| UserIdentification.IDMStudentNumber(DioceseofWollongong) | Text | ID Number as entered/imported |
| UserIdentification.InternationalDriversLicence | Text | ID Number as entered/imported |
| UserIdentification.InternationalStudentId | Text | ID Number as entered/imported |
| UserIdentification.JCExaminationNumber | Text | ID Number as entered/imported |
| UserIdentification.Keypass | Text | ID Number as entered/imported |
| UserIdentification.LCExaminationNumber | Text | ID Number as entered/imported |
| UserIdentification.LearnersPermit | Text | ID Number as entered/imported |
| UserIdentification.LegacySisDebtorCRN | Text | ID Number as entered/imported |
| UserIdentification.LegacySisId | Text | ID Number as entered/imported |
| UserIdentification.MazeRegistration | Text | ID Number as entered/imported |
| UserIdentification.MedicalCard | Text | ID Number as entered/imported |
| UserIdentification.MedicareCard | Text | ID Number as entered/imported |
| UserIdentification.Non-UniversityQualifications | Text | ID Number as entered/imported |
| UserIdentification.NSWEducationStandardAuthority(NESA) | Text | ID Number as entered/imported |
| UserIdentification.OtherIdentification | Text | ID Number as entered/imported |
| UserIdentification.ParkingPermit | Text | ID Number as entered/imported |
| UserIdentification.Passport | Text | ID Number as entered/imported |
| UserIdentification.PrimaryIDMUsername | Text | ID Number as entered/imported |
| UserIdentification.PrintedSignature | Text | ID Number as entered/imported |
| UserIdentification.PrivateHealthInsurance | Text | ID Number as entered/imported |
| UserIdentification.TeacherRegistrationNumber(ACT) | Text | ID Number as entered/imported |
| UserIdentification.TeacherRegistrationNumber(NSW) | Text | ID Number as entered/imported |
| UserIdentification.TeacherRegistrationNumber(NT) | Text | ID Number as entered/imported |
| UserIdentification.TeacherRegistrationNumber(QLD) | Text | ID Number as entered/imported |
| UserIdentification.TeacherRegistrationNumber(SA) | Text | ID Number as entered/imported |
| UserIdentification.TeacherRegistrationNumber(TAS) | Text | ID Number as entered/imported |
| UserIdentification.TeacherRegistrationNumber(VIC) | Text | ID Number as entered/imported |
| UserIdentification.TeacherRegistrationNumber(WA) | Text | ID Number as entered/imported |
| UserIdentification.UnknownIdentification | Text | ID Number as entered/imported |
| UserIdentification.VETUS! | Text | ID Number as entered/imported |
| UserIdentification.VictorianInstituteofTeachingAccreditation | Text | ID Number as entered/imported |
| UserIdentification.VictorianStudentNumber(VSN) | Text | ID Number as entered/imported |
| UserIdentification.WorkingwithChildrensCheck(Generic) | Text | ID Number as entered/imported |
| UserIdentification.WorkingwithChildrensCheck(NSW) | Text | ID Number as entered/imported |
| UserIdentification.WorkingwithChildrensCheck(NT) | Text | ID Number as entered/imported |
| UserIdentification.WorkingwithChildrensCheck(TAS) | Text | ID Number as entered/imported |
| UserIdentification.WorkingwithChildrensCheck(VIC) | Text | ID Number as entered/imported |
| UserIdentification.WorkingwithChildrensCheck(WA) | Text | ID Number as entered/imported |
| UserIdentification.WorkingwithChildrensCheck/BlueCard | Text | ID Number as entered/imported |
| UserIdentification.WorkingwithVulnerablePeople(ACT) | Text | ID Number as entered/imported |
| UserIdentification.WorkingwithVulnerablePeople(TAS) | Text | ID Number as entered/imported |
| Username | Text | Text as entered/imported |
| VisaExpiryDate | Date | A date in dd/mm/YYYY or ddmmYYYY format (i.e. 01/01/2001 or 01012001) |
| VisaSubclass | Text | One of a defined range of options |
| WelfareRisk | Boolean | Will be either 'True' or 'False' |
| YearLevelId | Text | One of a defined range of options |
| YouthAllowance | Boolean | Will be either 'True' or 'False' |
Available Operators:
| Operator | Description | Example |
| Equals: = | This symbol looks within the specified field for the specified value and displays users were this information is true. | "Occupation" = "Doctor" Will display all users who have Doctor in their Occupation field |
| Not Equals: != | This symbol looks within the specified field for the specificed value and displays users were this information is not true. | "Role" != "Parent" Will display all users who do not have Parent as their Role field |
| Greater Than: > | This symbol is used to calculate after a certain date (Currently only available for DateOfBirth & DateOfDeath fields) | "DateOfBirth" > "dd/mm/YYYY" (or date format can be "ddmmYYY" i.e. 01/01/2001 or 01012001) Will display all users with a date after the set date in the selected field |
| Greater Than Equals: >= | This symbol is used to calculate after or the same as a certain date (Currently only available for DateOfBirth & DateOfDeath fields) | "DateOfBirth" >= "dd/mm/YYYY" (or date format can be "ddmmYYY" i.e. 01/01/2001 or 01012001) Will display all users with the date of or after the set date in the selected field |
| Less Than: < | This symbol is used to calculate before a certain date (Currently only available for DateOfBirth & DateOfDeath fields) | "DateOfBirth" < "dd/mm/YYYY" (or date format can be "ddmmYYY" i.e. 01/01/2001 or 01012001) Will display all users with a date before the set date in the selected field |
| Less Than Equals: <= | This symbol is used to calculate before or the same as a certain date (Currently only available for DateOfBirth & DateOfDeath fields) | "DateOfBirth" <= "dd/mm/YYYY" (or date format can be "ddmmYYY" i.e. 01/01/2001 or 01012001) Will display all users with the date of or before the set date in the selected field |
Available Keywords:
| Keyword | Description | Example |
| AND | Joins two or more clauses that must be true in order to be a result | "Firstname" = "David" AND "Lastname" = "Morgan" Search results will display all users called David Morgan |
| OR | Joins two more more clauses where either must be true in order to be a result | "Firstname" = "David" AND "Occupation" = "Doctor" Search results will display all users called David and all users with Doctor as their occupation. |
| EXISTS | Determines if the field contains any content | "CEIDER" EXISTS Search results will display all users who have content in their CEIDER field. |
| CONTAINS | Determines if the value is within the field | "Firstname" CONTAINS "Jack" All firstnames that have Jack in them i.e. Jack, Jackie, Jackson |
| NOT | Disregards a clause after the keyword | "Firstname" = "David" NOT "Lastname" = "Morgan" Search results will include all users named David except those with the surname of Morgan. |
| IN | Allows multiple values of the one field to be searched | "Firstname" IN ("John", "Steve", Matthew") Search results will show all users with either of these first names. Please Note: When using multiple values ensure the overall values are within brackets and that there is a comma after each value per the example here. |
Saving an Advanced Filter
Once you have created an Advanced Filter, you can save the filter for future use. To do so, click 'Save As'.Name the Query and if you want this filter available to all staff using Advanced reporting, tick the 'Publish to Staff' option. If you are just using the filter as your private filter, leave this unticked. Click 'Create'.
This will add the query to your filter options in the right hand section.
Accessing a Saved Advanced Filter
The range of existing filters are contained for use in the right hand section of the tab.
Click the plus or minus icons to expand or collapse the different filter categories to locate the filter you require.
Editing Saved Filters
To edit the name or settings of a saved Filter or remove one from the list, click the tool icon next to the filter.
Exporting Filtered Data
To export query results, once you have applied the filter, click 'Export CSV' to download the .csv file to your device.
Filtered Reports
You can use filtered data to generate letters. The letters are generated from a template which you select from the list of available templates you have created for your school.
Please refer to the 'Reports' section of the 'People Management' article from our Knowledge Base for details.
Related Articles
People Management (as SIS)
Overview People Management is where your school's user record data is managed. Some schools use People Management exclusively as their Student Information System (SIS), others use People Management to integrate with the Student Information System ...People Management (with SIS Integration)
Overview People Management is where your school's user record data is managed. Some schools use People Management exclusively as their Student Information System (SIS), others use People Management to integrate with the Student Information System ...Financial Management Administration Guide
Overview The Financial Management module is located under the Organisation (grid icon) menu and is where Purchase Orders and Sub-Program Budgets are managed. Permissions For staff to access the Administration features of the Financial Management ...Adding New User Accounts
Overview When a new student, parent or staff become part of your school they will need to have a Compass user account created. This is managed within People Management. Please Note: For schools who have an external SIS (CASES21, Maze, SAS, Denbeigh ...VIC - Financial Management and CASES21
Importing Financial Data from CASES21 into Compass School Manager There are 3 exports that need to be run from CASES21 into Compass to set up the Financial Management modules (Budgeting and Purchase Ordering) in Compass. These are all exported from ...