Path: Reporting > Ad hoc Reporting > Filter Designer
Use Query Wizard to create dynamic Ad hoc filters that return current data based on selected fields, filter criteria, output settings, and aggregation options. This guide explains how to configure each Query Wizard screen to create a filter for reporting, exports, and other Ad hoc processes.
Before you start
Before creating a filter:
- Determine the data you need to report and whether it is Student, Census/Staff, or Course/Section data. Student and Course/Section queries use the calendar selected in the Campus toolbar; Census/Staff queries use data from the entire Campus database.
- Identify the fields you want to include in the filter. For complex queries, identify the fields you want to display and any criteria needed to limit results.
- Large queries can affect system performance. Apply filters whenever possible to limit the number of records returned.
- Verify you have the appropriate tool rights to create and save filters.
Step 1 - Select the filter and data type
Specify the Filter Type and Data Type to create a new filter.
- Select the Query Wizard Filter Type.
- Select a Data Type. The selected data type determines which fields are available when building the filter.
- Student
- Census/Staff
- Course/Section
- Click Create.
Step 2 - Select fields for the filter
The Field Selection screen determines which data elements are available for filtering, output, grouping, and aggregation.
- Only selected fields can be used later for filtering, output formatting, grouping, and aggregation.
- Short and Long Descriptions help users understand the purpose of saved filters.
- Query Wizard filters are dynamic and always return current database information.
- Enter a Query Name.
- Enter a Short Description and/or Long Description to provide additional information and context about the filter. Select the (+) icon to access the Long Description text box.
- Use Filter By to search for a field by entering part of its name and clicking Search OR expand the categories to locate available fields and subcategories.
- Click the field name displayed in blue in the All Fields list to move it to the Selected Fields list.
- Optional: Click Add Function to apply functions to selected fields.
- Click Next to continue configuring the filter. This may require that you scroll down the page.
- Select Save or Save and Test to save the filter before completing the remaining configuration steps
- Click Return to List to navigate back to the Filter Designer home page.
- Click Next to open the Filter Parameters screen.
Custom Tool field behavior
When a filter includes fields from a Custom Tool, the Query Wizard returns all students in the result set, even if a student does not have a corresponding record in the custom dated tab.
To exclude students who do not have records in a custom tab that uses the Table or List Element tab type, set the statusDate operator to IS NOT NULL. This limits results to students with associated custom-tab records.
When Query Wizard returns data from Table or List Element custom tabs, the filter logic evaluates all available date and time combinations stored for those records and returns every applicable combination. Table tab types store both date and time values, while List Element tab types always store a time value of 12:00 AM. This behavior can cause additional records to appear in the filter output when multiple dated custom-tab records exist for a student.
For more information about custom tab types and how custom data is stored, see Custom Tool Setup.
Step 3 - Define filter parameters
Use Filter Parameters to determine which records to include in the results.
- Review the selected fields.
- Select an Operator for each of the applicable fields.
- Enter a Value for each operator. The value works with the selected operator to determine which records are returned.
- Result: Only students enrolled as of today whose last names begin with "B" and have birthdays in November are included in the results.
- When using string-based operators such as LIKE, CONTAINS, STARTS WITH, or ENDS WITH, you can use wildcard characters to broaden your search criteria. Common wildcard characters include:
- % = zero or more characters (for example, L% finds names that start with L)
- _ (underscore) = a single character (for example, L__ finds three-character names that start with L)
- [token] = a range of possible characters (for example, L[ae]% finds names that start with La or Le)
- ^ = excludes characters within a token set (for example, L[^ae]% finds names that do not start with La or Le)
- Click Add to apply multiple conditions to the same field. Click the "X" at the start of the row to remove that data element.
- Optional: Enter a Logical Expression to control how multiple conditions are evaluated.
- Select Save or Save and Test to save the filter before completing the remaining configuration steps.
- Click Next to open the Output Formatting screen.
Step 4 - Configure output formatting
Use Output Formatting to determine how data displays when exported or reported.
- Select Output distinct records to remove duplicate records from the results.
- Verify the Output checkbox is selected for any fields that should appear in the filter output.
- Enter a Sequence number to determine the order of fields in the output.
- Enter a Sort value to define the sort order.
- If a Sort value is entered, select a Direction of Ascending or Descending.
- Enter a Column Header for each field (for example, State ID). If a column header is not specified, the system will use the field name as the default column header (for example, student.stateID).
- Select an Alignment option (Left, Right, Center).
- Select a Formatting option to determine how values are reported for the field when used in reports and exported files. Formatting options are important for filters used in reports that require specific formatting to ensure the file is submitted correctly to an entity or system.
- Enter a Length value if needed. Length determines the maximum number of characters returned for the field.
- Select Save or Save and Test to save the filter before completing the remaining configuration steps.
- Click Next to open the Grouping and Aggregation screen.
Step 5 - Configure grouping and aggregation
Use the Grouping and Aggregation page to organize query results into groups and calculate summary values for each group.
- Select a Group By field for each grouping level. Grouping organizes related records into sections.
- Select a Group Order for each grouping level to determine how group aggregate/sub-totals are reported when exported via the Data Export tool.
- Select a field to Aggregate/SubTotal by. This determines which field within the filter is used for the 'Group by' fields.
- Select an Aggregate Type to calculate totals or other sum values for grouped data.
- Continue configuring additional grouping levels as needed.
- Click Save and Test to save the filter and review the results.
- Optional: Mark the Force Order checkbox for complex filters that require performance optimization.
Step 6 - Generate a saved filter
Filters saved to the Saved Filter list are available for immediate testing, reporting, and data exports.
- Select the filter from the Saved Filter list.
- Select the appropriate Calendar.
- Click Test. The filter opens in a separate window for review.
- For Census/Staff filters, a calendar selection is not required
Impact
Users with appropriate permissions can reuse, modify, and share the filter with authorized user groups
What's next
- Modify the filter to add additional criteria or formatting.
- Save the filter to a folder for organization and easier access.
- Share the filter with authorized user groups.
- Use the filter in Data Export, Letter Builder, and other Ad hoc reporting tools.
- Modify saved filters to add fields, update criteria, or adjust output formatting as reporting requirements change.
Appendix A - Common operators
| Operator | Results | Example |
|---|---|---|
= (Equals) |
Returns exact match of value. |
student.grade=3
|
< > (Does not equal) |
Returns results not equal to the value. |
student.gender < > M
|
> (Greater than) |
Returns results that are greater than the entered numeric value. |
student.age > 16
|
> = (Greater than or equal to) |
Returns results that are greater than or equal to the entered numeric value. |
student.age >= 16
|
< (Less than) |
Returns results that are less than the entered numeric value. |
student.age < 16
|
< = (Less than or equal to) |
Returns results that are less than or equal to the entered numeric value. |
student.age <= 16
|
IN |
Includes value.
Do not include spaces after the comma when using this format. |
student.grade IN 9,10
|
NOT IN |
Excludes value.
Do not include spaces after the comma when using this format. |
student.grade NOT IN 11,12
This operator allows NULL values.
|
BETWEEN |
Filters data between two specified values. Works with numbers, dates, and strings.
|
For BETWEEN: student.stateID BETWEEN 00001 THROUGH 100000.
|
IS CURRENT USER |
Returns the current user's ID. |
Learner Plan Manager Setting learningPlan.planManagerPersonID IS CURRENT USER reports the current user's ID, along with data only applicable to that user.
Current Teacher Sections For courseSection.personID IS CURRENT USER limits the results to students in the current teacher's section. This is useful for a report on student birthdays by homeroom, or for a Spirit Squad Advisor who needs to make locker signs and a list of participants and locker information. |
LIKE |
Searches for test string in the field. |
course LIKE hist
|
NOT LIKE |
Searches for test string and filters data that is not like the user-defined value. |
course NOT LIKE hist
|
SOUNDS LIKE |
Uses a database function to return names with similar sound patterns. |
student.lastName SOUNDS LIKE Ball
|
CONTAINS |
Searches for strings that include the same data entered by the user in the field. Any string that does not contain the user-defined value is filtered out. Any wildcard characters entered are treated as standard SQL wildcards. |
student.birthCountry CONTAINS Cana
|
STARTS WITH |
Searches for strings that begin with the same data the user entered in the field. Any string that does not contain the user-defined value is filtered out. Any wildcard characters entered are treated as standard SQL wildcards. |
student.birthCountry STARTS WITH Mexi
|
ENDS WITH |
Searches for strings that end with the same data entered by the user in the field. Any string that does not contain the user-defined value is filtered out. Any wildcard characters entered are treated as standard SQL wildcards. |
student.birthCountry ENDS WITH many
|
IS NULL |
Returns fields that are completely NULL (0 is considered a value). |
student.stateID IS NULL
|
IS NOT NULL |
Returns all fields that are not NULL (0 is considered a value). |
student.ssn IS NOT NULL
|
IS TODAY |
Returns result dates as the current date. |
start.date IS TODAY
|
IS YESTERDAY |
Returns results as of yesterday. |
start.date IS YESTERDAY
|
IS TOMORROW |
Returns result dates as of tomorrow's date. |
end.date IS TOMORROW
|
IN THE MONTH |
Returns all database field data for the month entered.
|
employment.districtStartDate IN THE MONTH October
|
=TRUE |
Returns checkbox values of "true" (checkbox is marked) |
enrollment.stateExclude = TRUE
|
=FALSE |
Returns checkbox values of "false" (checkbox is not marked) |
enrollment.stateExclude = FALSE
|
Appendix B - Aggregation types
| Aggregation Type | Description |
|---|---|
| Record Count | Counts all records |
| Distinct Count | Counts unique records |
| MIN | Returns the lowest value |
| MAX | Returns the highest value |
| SUM | Adds all values |
| AVG | Returns the average value |
