Query Wizard

Tool Search: Filter Designer

In the Query Wizard, elements are organized in a straightforward pattern, so it is easy to select the elements needed. Filters can be designed with student information, census/staff information or course/section information. Queries for students and course/section data pulls results from the calendar selected in the Campus toolbar. Census/Staff data pulls results from the entire Campus database, regardless of the calendar selected.

screenshot of the filter designer tool with query wizard highlighted

Users need at least R(ead) rights to the Filter Designer tool and at least A(dd) rights for Query Wizard Filters in order to properly use this tool. 

For more information about Tool Rights and how they function, see the Tool Rights article.

Unless using the Data Warehouse, queries should be created in such a way to avoid large results. Generating large queries may cause performance issues.

An ad hoc row limit is set on the database at 5 million rows. Any query that returns more than this is shortened. A warning message displays when this occurs.
When generating large queries and the Ad hoc Row Limit is met:
  • Select fewer fields to include in the query
  • Add more filters (see Functions) to reduce the number of records
  • Use direct SQL access

Filters including GPA fields may task the server. It is recommended that these queries be generated after normal school hours.

Filters built in the the Filter Designer display in HTML format. The HTML output allows for column sorting, filtering, grouping, and exporting to Excel or PDF.

screenshot of the filter designer display in HTML formatHTML Filter Display

To view the output in a simple HTML table, click the link at the top of the output. This displays the output without the ability to sort, group and organize the columns.

Query Wizard functionality allows users to easily create Ad hoc filters by organizing elements in a straightforward manner. Query Wizard filters are dynamic and always pull current information from the database based on the fields and filter options selected.

When using Custom Tab fields within Ad Hoc Query Wizard, all students are included in the results even if the student does not have a record within the custom dated tab. To exclude students without records for fields from a custom tab that is Table or List Element tab type, set the statusDate Operator to IS NOT NULL. When pulling in fields from a custom tab that is a Table or List Element tab type, Ad Hoc logic outputs every possible combination based on a specific date and time. The Table Tab Type stores specific times. The List Element Tab type always stores 12:00 AM. See the Custom article for more information.

Query Wizard Features

Short and Long Filter Descriptions

This provides additional information and context about the filter. It's displayed when a user selects that filter from the Saved Filters list and when the filter is being modified.

screenshot of the short and long description fields highlighted

Click here to expand...

To attach short and/or long descriptions to a filter, enter this information within the Short Description and Long Description text fields. To access the Long Description text box, select the (+) icon. All descriptions entered are saved once the filter itself is saved.

Once a filter has a long description entered and saved, this information displays on the Filter Designer editor when the filter is selected in the Saved Filters window. This is useful when determining what filter to use as well as communicating any important information about the filter prior to editing or making modifications. If a short description has been entered, this information displays when the cursor hovers over the filter within the Saved Filters window.

Both the Short and Long Descriptions display during the editing/modifying of a saved filter. Although the Long Description field appears locked, this field can be modified by selecting the (+) icon.

screenshot of the description appearing when a filter is selected in the saved filters windowFilter Description Display

Filter Operators

Filter operators allow users to set specific parameters per field within a filter. These parameters uniquely filter each field while maintaining the filter as a whole.

screenshot of the operator field highlighted
Users may apply multiple operators to the same field by clicking the Add Filter button and selecting a field. If a Logical Expression exists, all fields assigned an Operator must be included within the expression.

Click here to expand...

The following table describes each available filter operators:

OperatorResultsExample

= (Equals)

Returns exact match of value.

student.grade=3
     
Only students in grade 3 are returned.

< > (Does not equal)

Returns results not equal to the value.

student.gender < > M
   
Students who have a Gender = F assigned on their Identities record or who do not have a value entered in the Grade field are returned.
 
This operator allows NULL values.

> (Greater than)

Returns results that are greater than the entered numeric value.

student.age > 16
     
All students older than 16 years of age are returned.

> = (Greater than or equal to)

Returns results that are greater than or equal to the entered numeric value.

student.age >= 16
   
All students 16 years of age and older are returned.

< (Less than)

Returns results that are less than the entered numeric value.

student.age < 16
   
All students under the age of 16 are returned.

< = (Less than or equal to)

Returns results that are less than or equal to the entered numeric value.

student.age <= 16
   
All students 16 years of age and younger are returned.

IN

Includes value.

student.grade IN 9,10


All students in 9th and 10 grade are returned.


When using this format, do not put spaces after the comma.

NOT IN

Excludes value.

student.grade NOT IN 11,12

   
All students not in 11th or 12th grade are returned.


This operator allows NULL values.


When using this format, do not put spaces after the comma.

BETWEEN

Filters data between two specified values. Works with numbers, dates and strings.
     
If a date field is selected, the following options are available:

  • DATE - Returns data based on the specified date range (where the starting date is sub-option 1 and the ending date is sub-option 2).
  • TODAY - Filters data based on dates that occur from a specific date through today or vice versa.
  • TOMORROW - Filters data based on dates that occur from a specific date through tomorrow or vice versa.
  • YESTERDAY - Filters data based on dates that occur from a specific date through yesterday or vice versa.
  • DAYS BEFORE - Filters data based on the number of days (sub-option 1) prior to sub-option 2 through sub-option 2.
  • MONTHS BEFORE - Filters data based on the number of months (sub-option 1) prior to sub-option 2 through sub-option 2.
  • DAYS AFTER - Filters data based on sub-option 1 through the number of days (sub-option 2) after the sub-option 1 date.
  • MONTHS AFTER - Filters data based on sub-option 1 through the number of months (sub-option 2) after the sub-option 1 date.

For BETWEEN: student.stateID BETWEEN 00001 THROUGH 100000.
 
All students with a State ID between 00001 - 100000 are returned.

For DATE: student.birthDate BETWEEN DATE 10151995 THROUGH DATE 10152010.
         
All students with a birth date between 10/15/1995 - 10/15/2010 are returned.
   
For TODAY: student.startDate BETWEEN TODAY THROUGH TODAY.
   
All students who began an enrollment in the school today (current date) are returned.
   
For YESTERDAY: student.startDate BETWEEN YESTERDAY THROUGH DATE 10152010.
   
All students who began an enrollment in the school yesterday through 10/15/2010 are returned.
   
For DAYS BEFORE: student.startDate BETWEEN DAYS BEFORE 4 THROUGH YESTERDAY.
   
All students who began an enrollment in the school 4 days before yesterday through yesterday are returned.
   
For MONTHS BEFORE: student.startDate BETWEEN MONTHS BEFORE 5 THROUGH TODAY.
   
All students who began an enrollment in the school 5 months prior to today through today are returned.
     
For DAYS AFTER: student.startDate BETWEEN DATE 10152010 THROUGH DAYS AFTER 5.
 
All student who began an enrollment in the school on 10/15/2010 through 10/20/2010 (5 days after) are returned.
   
For MONTHS AFTER: student.startDate BETWEEN DATE 10152010 THROUGH MONTHS AFTER 5.
   
All student who began an enrollment in the school on 10/15/2010 through 3/15/2011 (5 months after) are returned.

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 of student birthdays with a homeroom, or a Spirit Squad Advisor who needs to make locker signs and needs a list of participants and locker information. 

LIKE

Searches for test string in field.

course LIKE hist
   
All courses like History 101 are returned.

NOT LIKE

Searches for test string and filters data that is not like the user-defined value.

course NOT LIKE hist
   
All courses not like Hist are returned.
 
This operator allows NULL values.

SOUNDS LIKE

Uses a database function to return names with similar sound patterns.

student.lastName SOUNDS LIKE Ball
     
Names such as "Ball," "Bell" and "Boll" are returned.

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
 
All students with a Birth Country that contains "Cana" are returned.

STARTS WITH

Searches for strings that begin 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 STARTS WITH Mexi
   
All students with a Birth Country that begins with "Mexi" are returned.

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
   
All students with a Birth Country that ends with "many" are returned.

IS NULL

Returns fields that are completely NULL (0 is considered a value).

student.stateID IS NULL
   
All students who do not have a state ID are returned.

IS NOT NULL

Returns all fields that are not NULL (0 is considered a value).

student.ssn IS NOT NULL
   
All students who do not have a stateID are returned.

IS TODAY

Returns result dates as the current date.

start.date IS TODAY
   
Entries where the start.date is the current date are returned.

IS YESTERDAY

Returns result dates as of yesterday's date.

start.date IS YESTERDAY
   
Results for one day previous to the current date are returned.

IS TOMORROW

Returns result dates as of tomorrow's date.

end.date IS TOMORROW
   
Results for one day after the current date are returned.

IN THE MONTH

Returns all database field data for the month entered.
   
This operator allows both numbered dates and spelled out dates (i.e., 10 or October). This operator also allows for both upper and lower case letters. If spelling out a month, users must entered at least the first three characters (i.e., Oct for October).

employment.districtStartDate IN THE MONTH October
 
All employees who have a district employment Start Date within the month of October are returned. This operator does not look at the Year or Calendar selected in the Campus toolbar. All historical and current district employment records with a Start Date in October are returned.

=TRUE

Returns checkbox values of "true" (checkbox is marked)

enrollment.stateExclude = TRUE
   
All students who have the State Exclude checkbox marked on their enrollment records are returned.

=FALSE

Returns checkbox values of "false" (checkbox is not marked)

enrollment.stateExclude = FALSE
     
All students who do not have the State Exclude checkbox marked on their enrollment records are returned.

In addition to the options above, wildcard searching is also available. The following is a list of options:

Wildcard or PatternSQL MeaningStandard Examples

%

0 or more characters

Entering the word Man returns the same results when entering Man%.

%son finds names that end in -son: Johnson, Manson, Jason-Benson, etc.

_ (underscore)

One character

Olson_Zierke and Olson Sierke returns the same results.

L__ (with two underscores) does not look only for 3-character names that start with L, but _L_e_ finds names where L is the first and e the third character (e.g. Lee, Luewenhook).

If the three underscores are entered at the end of a name, like Dan___, results list names with three additional letters (Daniel).

[token]

A range of possible characters

L[ae] finds names that start with La or Le.

,James

No SQL wildcard

Searches for first name equal to or beginning with James.


This can only be used in the Quick Search fields.

Gonzales-Uribe

Compound name

Finds that last name.


This returns compound names regardless of whether they are linked by a space or hyphen.

Gonzales Uribe or
Gonzales_uribe or
Gonzales%uribe

A compound name with a space.

Finds the name with or without a space or hyphen.


Try wildcards if there is a space between the compound name.

Users can also use the following combinations when using the Like operator:

Wildcard or PatternSQL MeaningStandard Examples

%

0 or more characters

L% finds names that start with L

L finds names that contain an L

LAN finds names containing LAN (Blanko, Landesburg, Blankenship, etc.)

_ (underscore)

One character

L__ (two underscores) finds Lee and Lor, not Luewenhook.

[token]

A range of possible characters

L[ae]% finds names that start with La or Le.

^

Negation of token

L[Query Wizard^ae] finds names that do not start with La or Le.

Rules for Operators by Data Type

The following table describes all rules for allowing or disallowing operators by data type where: Y= Allowed, N = Not Allowed and D = Depends on Field.

OptionNumberFloatStringDateTextBit

>

Y

Y

Y

Y

Y

N

>=

Y

Y

Y

Y

Y

N

<

Y

Y

Y

Y

Y

N

<=

Y

Y

Y

Y

Y

N

< >

Y

Y

Y

Y

Y

N

=

Y

Y

Y

Y

Y

N

IS NULL

D

D

D

D

D

N

IS NOT NULL

D

D

D

D

D

N

BETWEEN

Y

Y

Y

Y

Y

N

IS TODAY

N

N

N

Y

N

N

IS YESTERDAY

N

N

N

Y

N

N

IS TOMORROW

N

N

N

Y

N

N

IN

Y

Y

Y

Y

Y

N

NOT IN

Y

Y

Y

Y

Y

N

LIKE

N

N

Y

N

N

N

STARTS WITH

N

N

Y

N

N

N

ENDS WITH

N

N

Y

N

N

N

CONTAINS

N

N

Y

N

N

N

SOUNDS LIKE

N

N

Y

N

N

N

=TRUE

N

N

N

N

N

Y

=FALSE

N

N

N

N

N

Y

Use a Field as an Operator Value

Depending on the operator chosen for the field, a field may be used as an operator's value allowing a comparison between two fields. Logic only allows fields of the same data type to be used as the Operator's Value. For example, date fields are allowed to use other date fields as an operator value. When the appropriate operator is used, the Value column can act as a dropdown list while remaining static allowing the user to select a field or input a value. Deleting a field also removes it from the Value field, clearing out the operator for the field using it. Additionally, replacing a field with the Element Replacement tool replaces the field and the operator's value if the replaced field was being used as the value.

 screenshot of a value column highlighted

In the above example, a query has been set to report students with fees charged during their enrollment. Using the fields activeEnrollment.startDate (7) and activeEnrollment.endDate (8) as operator values for feeDetail.dueDate (9, 10), the query reports students with fees due on or after the student's active enrollment start date AND on or before the student's active enrollment end date.

Operators Allowed to Use a Field as Values

OperatorAllowed

>

Y

>=

Y

<

Y

<=

Y

< >

Y

=

Y

BETWEEN

Y

IS CURRENT USER

N

IN THE MONTH OF

N

MONTHS BEFORE

N

DAYS BEFORE

N

IS NOT NULL

N

IS NULL

N

IS TODAY

N

IS YESTERDAY

N

IS TOMORROW

N

IN

N

NOT IN

N

LIKE

N

STARTS WITH

N

ENDS WITH

N

CONTAINS

N

SOUNDS LIKE

N

=TRUE

N

=FALSE

N

Logical Expressions

The Logical Expression field allows users to incorporate conditions between fields within a filter. This field provides an effective way to use the OR, AND, and NOT conditions between fields and groups of fields.

  
  • Only fields assigned an Operator are allowed to be included within logical expressions.
  • Logical Expressions are created using the ID number associated with each field.
 screenshot of an example logical expressionLogical Expression with a Filter 

Logical expressions can be grouped using ( ) symbols and the ID number to define the order in which the tool should include or exclude a person. In the example above, the ( ) symbols indicate the tool should determine the student's End Date (5) and the student's grade (6) and include these students depending upon if they are Asian (10) or White (11). This determination and group of students is then applied to the remaining parts of the logical expression.

Using ( ) symbols are especially useful when using the OR condition as users are able to include or exclude people based on whether or not they meet the criteria for the fields included within a group of fields. For example, students who have a State ID less than 1000 (8) or an End Status populated (4) are not included in the remaining calculation for the logical expression.

Functions

Functions can be added to filters which allow logic to be applied to field columns when the filter is generated via the Data Export tool. Add a function to a filter by selecting the Add Function button. The Function Editor appears in a new window.

screenshot of the function editorAdd Function

Click here to expand...

Add Functions to Queries

  1. Enter the Name of the function. This name differentiates the function from other functions within the Selected Fields window and on filters generated via the Data Export tool.
  2. Select the desired Function from the dropdown list. See the  Function Descriptions  section below for descriptions and examples of each function. Once a function is selected, the Filter By Search field (see step 4) becomes active. 
  3. If the Constant Function is selected, enter the Constant Value and click the Add button. The value entered displays in the Parameters window and be reported on every record returned.
  4. Use the Filter By Search field to search for desired fields. Entering a search value and click the Search button resets the list of fields to only return matching fields. Click the Clear button to remove entered search values and see the entire list of fields.
  5. Select which fields to include within the function by clicking on each field within the All Fields window. Selected fields move into the Parameters window, indicating which fields have been added to the function.
  6. Select the Save icon.

screenshot of the add function button highlightedAdding a Function to a Filter

screenshot of the function editorEnter Data into the Function Editor

The Field Selection editor displays after saving the added function. Functions created and added to the filter displays in the Selected Fields window. The Name given to the function always displays to the left of the period (i.e., function.functionName).

  screenshot of a function added to a filterView Functions Added to a Filter  

Edit Functions

Existing functions can be edited by selecting the function within the Selected Fields window and clicking the Edit Function button.

screenshot of the edit function button highlightedEdit Existing Functions

Function Descriptions

The following describes each available function.

FunctionDescriptionExample
Constant

The Constant function outputs the Constant Value entered on each record returned when the filter is exported.


In the examples to the right, a Constant Value of 5 is entered and added to the filter. When the filter is exported, a column is reported displaying the Constant Value entered.

 screenshot of the constant function selected 

Coalesce

The Coalesce function allows users to define multiple fields where logic pulls the first field and if NULL, the second field is pulled and so on down the line of added fields until a value is found. Logic pulls field values in the order fields are selected in the Function Editor.


In the examples to the right, a Coalesce function for Federal Race Ethnicity and Race Ethnicity Determination fields was added. This means logic first pulls and reports student Federal Race Ethnicity field values and for any that are NULL, the student's Race Ethnicity Determination reports.


When the filter is exported, the function reports field data within a specific column. Student Federal Race Ethnicity values are reported. 

 screenshot of the coalesce function selected 

 screenshot of an example of the coalesce function transforming data 

 Concatenate

The Concatenate function allows field values to be appended when the filter is exported.


In the example to the right, a Concatenate function for fields Gender and Race Ethnicity was added. When the filter is exported, field values are appended and reported. Student Gender values (M, F) are reported alongside student Race Ethnicity values. 

 

 

 

 screenshot of the concatenate function selected 

 screenshot of the concatenate function transforming data in the filter 

Add

The Add function allows field values to be added together to output a single result (i.e., field 1 + field 2).


In the example to the right, Fee Debit is being added to Fee Credit  in order to generate a total balance. When the filter is exported, field values are added and reported as single value.

 screenshot of the add function selected 

 screenshot of the add function transforming data within a filter 

Subtract

The Subtract function allows field values to be subtracted from each other to output a single result. 


In the example to the right, total Fees are being subtracted from Total Paid in order to report a student's outstanding balance. When the filter is exported, field values are subtracted and reported as single value. 

 screenshot of the subtract function selected 

 screenshot of a filter where the subtrack function has transformed data 

Multiply

The Multiply function allows field values to be multiplied together to output a single result (i.e., field 1 x field 2).


In the example to the right, employee hours per day are being multiplied by the number of days employed for the year. When the filter is exported, field values are multiplied and reported as single value. 

 screenshot of the multiply function selected  

 screenshot of filter data transformed via the multiple function 

Divide

The Divide function allows a field or more fields to be divided and output into a single result (i.e., field 1 / field 2).


In the example to the right, total number of fees is divided by the total amount of fees paid to get the percentage of total fees paid to date. If applicable, decimal places are included in the output.


When the filter is exported, field values are divided and reported as a single value.

 screenshot of the divide function selected 

 screenshot of the divide function transforming data within a filter 

Record Count

The Record Count function allows users to report a record count for the field selected.

In the example to the right, a record count of behavior events is used as the function to report a count of behavior events per grade level.


When the filter is exported, a record count of the field is calculated and reported.

 screenshot of the record count function selected 

 screenshot of the record count function transforming data within a filter 

Distinct Count

The Distinct function allows users to report a distinct count for the field selected.

In the example to the right, a distinct count of behavior events is used as the function to report the distinct count of behavior events per grade level. 


When the filter is exported, a record count of the field is calculated and reported 

 

 screenshot of the distinct count function selected 

 screenshot of the district count transforming data within a filter 

MIN

The MIN function allows users to report the minimum value for a field.


In the example to the right, the MIN BMI is used as the function to report the minimum BMI (Body Mass Index) per grade level.


When the filter is exported, the MIN the field is calculated and reported.

 screenshot of the min function selected 

 screenshot of the min function transforming data within a filter 

MAX

The MAX function allows users to report the maximum value for a field.


In the example to the right, the MAX student count is used as the function to report the largest class size per course.


When the filter is exported, the MAX the field is calculated and reported.

 screenshot of the max function selected 

 screenshot of the max function transforming data within a filter 

SUM

The SUM function adds the value or field selected over all other aggregated fields.


In the example to the right, the SUM of fee amounts is used as the function to report the SUM of fees per grade.


When the filter is exported, the SUM field is calculated and reported.

 screenshot of the sum function selected 

 screenshot of the sum function transforming data within a filter 

AVG

The AVG function allows users to report the average value for a field.


In the example to the right, the AVG of roster student count is used as the function to report the average class size per department.


When the filter is exported, the AVG field is calculated and reported.

 screenshot of the average function selected 

 screenshot of the average function transforming data within a filter 

Output Formatting

The Output Formatting editor allows users to control how each field is reported and displayed when exported.

screenshot of output formatting options available

Click here to expand...

Output Formatting Descriptions

FieldDescription
Output distinct recordsIf marked, data is outputted in unduplicated records based on field values.

The following is an example of a filter containing student first name, last name, grade, gender and behavior event type:
  • If a student has three behavior events for the same behavior event type and the Output distinct records checkbox is not marked, the student reports three records.
  • If the Output distinct records checkbox is marked, the same student now only reports one record.
Field

Fields selected from the All Fields window in the previous screen.

Output

This checkbox determines whether or not the field is included in outputted data. Deselecting this checkbox means data is still filtered and reported for this field and operators but not included in the output.

Seq

This field determines the sequence of outputted data.

Sort

This field determines the sort order of outputted field data.

Direction

This field determines if data is sorted ascending or descending. This field is only available if a value is entered in the Sort field.

Column HeaderThis field determines what header is displays for the field on exported files. Users are encouraged to enter a logical and easily identifiable column header for each field, as leaving the field blank results in the field name (i.e., student.stateID) being reported.
Alignment

The field determines how field data is aligned on files exported. Available options include: Left, Center and Right.

Formatting

The field determines how values are reported for the field when used in reports and exported files. Formatting options are important for filters used with reports which require specific formatting in order for the file to be correctly submitted to an entity or system.


The following formatting options are available:

  • Zero Pad - numbers are padded with zeros to the left (i.e., 444 zero padded becomes 000444)
  • Space Fill - values are filled with spaces in order to reach required field length
  • Upper Case - values are reported entirely in uppercase (i.e., Course is reported COURSE). This option is only available for text, char and varchar fields.
  • Lower Case - values are reported entirely in lowercase (i.e., Course is reported course). This option is only available for text, char and varchar fields.
  • MM/DD/YYYY
  • MM-DD-YYYY
  • MMDDYYYY
  • YYYY/MM/DD
  • YYYY-MM-DD
  • YYYYMMDD
  • YYYY
  • YYYY/MM
  • YYYY-MM
  • YYYYMM
  • MM/YYYY
  • MM-YYYY
  • MMYYYY
  • MM/DD/YYYY hh:mm AM
  • MM-DD-YYYY hh:mm AM
  • YYYYMMDDHHmm - This is similar to military time (e.g.,1:00PM is 1300) because there is no AM/PM.
  • 1, 234.5; - 1,234.5
  • 1,234.5; (1,234.5)
  • $1,234.00; -$1,234.00
  • $1,234.00; ($1,234.00)
  • Y/N - Used with bit fields. If bit field is checked, Y is reported. If field is unchecked, N is reported.
  • YES/NO - Used with bit fields. If bit field is checked, YES is reported. If field is unchecked, NO is reported.
  • T/F - Used with bit fields. If bit field is checked, T is reported. If field is unchecked, F is reported.
  • TRUE/FALSE - Used with bit fields. If bit field is checked, TRUE is reported. If field is unchecked, FALSE is reported.
  • 1/0 - Used with bit fields. If bit field is checked, 1 is reported. If field is unchecked, 0 is reported.
Length

This field determines the length of the column in the exported data file. This is the maximum amount of characters allowed to be reported in the column. Data which exceeds the defined length is truncated on the right side. Zero padding is added to the left of a value. Space filling is added to the right of a value.


A length must be defined for each field when exporting the filter in Fixed Width format within the Data Export tool.
Save To

Indicates whether the filter saves to the current user, a user group(s) or specific folder.


If a filter is saved to more than one user group, a separate copy is stored for each group. Each group can independently edit the filter without affecting another group's copy. If a filter with the same name already exists within a group, the filter name is appended with a number in parentheses indicating an incremented version number (i.e., HonorStudents already exists for a group so saving a new filter with the same name appends the name to HonorStudents(2)). If the filter was saved across multiple groups, the filter name only displas as appended for groups where a filter with the same name already exists.


Test

This field allows users to test and preview a filter before saving it. Test results display in a separate window. Pop-up windows must be enabled on the web browser in order to view the test filter.

Save

Saves the filter.

Grouping and Aggregation Descriptions

Grouping and aggregation places results into groups and calculations can be performed on the results. Aggregations display at the bottom of each data group when extracting the data. These options are not available for fixed width output formats.

screenshot of grouping options available

Click here to expand...

The following describes the available options.

Field

Description

Grouping

This is the order in which each group is reported. Users are allowed report up to 5 tiers (or groups).

Group By

Determines which field is in the group and reports aggregate/sub-totals. Only fields included within the filter are available for selection.

Group Order

Determines how group aggregate/sub-totals are reported when exported via the Data Export tool.

Aggregate/Sub Total by

Determines which field within the filter is used for the 'Group by' fields. For example, a user creating a behavior Ad hoc filter who chooses to Group By behavior events and Aggregate By personID using an Aggregate Type of Distinct Count produces the number of students per Behavior Event Type.

Aggregate Type

Determines which calculation is applied to the group when calculating and reporting aggregate/sub-totals. For example, a group containing student last names (student.lastName) with an Aggregate/Sub Total of State ID (student.stateID) and an Aggregate Type of Distinct Count reports individual groups based on student last names with a count of how many students within that group have distinct State IDs.
 
Aggregate Types include:

  • Record Count - Indicates the total number of records in the group.
  • Distinct Count - Indicates the total number of distinct records within a group based on the fields selected to be counted from the Aggregate By option.
  • MIN - Indicates the minimum value for the designated Aggregate/Sub Total field within a group (i.e., an Aggregate/Sub Total for State ID (student.stateID) with a MIN Aggregate Type reports the smallest State ID value with each group).
  • MAX - Indicates the maximum value for the designated Aggregate/Sub Total field within a group (i.e., an Aggregate/Sub Total for State ID (student.stateID) with a MAX Aggregate Type  reports the largest State ID value within each group).
  • SUM - Indicates the sum of all values within a group for the Aggregate/Sub Total field selected (i.e., an Aggregate/Sub Total for Present Minutes (attendanceDetail.presentMinutes) with a SUM Aggregate Type reports a sum of all Present Minutes with each group).
  • AVG - Indicates the average of all values within a group for the Aggregate/Sub Total field selected (i.e., an Aggregate/Sub Total for Present Minutes (attendanceDetail.presentMinutes) with AVG Aggregate Type reports the average of  Present Minutes for all students within each group)

See the Rules for Aggregate Calculations by Data Type table below for more information.

Save To

Indicates whether the filter saves to the current user, a user group(s) or specific folder.
   
If a filter is saved to more than one User Group, a separate copy is stored for each group. Each group can independently edit the filter without affecting another group's copy. If a filter with the same name already exists within a group, the filter name is appended with a number in parentheses indicating an incremented version number (i.e., HonorStudents already exists for a group so saving a new filter with the same name appends the name to HonorStudents(2)). If the filter was saved across multiple groups, the filter name only displays appended for groups where a filter with the same name already exists.

Test

This field allows users to test and preview a filter before saving it. Test results display in a separate window. Pop-up windows must be enabled on the web browser in order to view the test filter.

Save

Saves the filter within Infinite Campus. The filter is now available for use in all Ad hoc Filter fields throughout Infinite Campus (if the user is part of the user group the filter was saved to).


Rules for Aggregate Calculations by Data Type

The following table describes all rules for allowing or disallowing aggregate calculations based on data type:

Data Type

Number

Float

String

Date

Text

Bit

MIN

Yes

Yes

Yes

Yes

Yes

Yes

MAX

Yes

Yes

Yes

Yes

Yes

Yes

AVG

Yes

Yes

No

No

No

No

SUM

Yes

Yes

No

No

No

No

Record Count

Yes

Yes

Yes

Yes

Yes

Yes

Distinct Count

Yes

Yes

Yes

Yes

Yes

Yes

Create a Filter

The following is a basic workflow of how to create a filter. See the Query Wizard Features for additional formatting and modification that can be done for more advanced filters.

Step 1. Choose Filter and Data Type

  1. Select the Query Wizard radio button.
  2. Select a Data Type. This determines which type of fields are available for selection: Student, Census/Staff or Course/Section.
  3. Click the Next button. The screen displays a list of fields to select in order to create the filter.

screenshot of filter and data types availableFilter Type, Data Type Selection

Step 2. Select Categories and Fields

Campus fields are organized into specific categories relating to the Filter Data Type selected on the previous screen. Categories are organized in a hierarchy format, where selecting the (+) open available fields and additional subcategories within the category. Users may include Campus and user-created custom fields when building filters.

  1. Enter a Query  Name for the filter.
  2. Enter a Short and or Long Description about the filter (if applicable).
  3. Select the data elements form the All Fields list by clicking on them. The fields move to the Selected Fields list. To remove a field from the Selected Fields list, click on it to highlight and click the left pointing arrow button.
  4. Select the Add Function button to add a function to the filter.
  5. To search for a particular field, enter part of the name of the field in the Filter By section and click the Search button. Select the appropriate options for the query. All fields that contain that name display in the All Fields list. To clear the selection, click the Clear button and all available fields display again.
  6. To save the filter right now without testing it or modifying any results of the selected fields, choose Save or Save and Test.
  7. To continue, click the Next button to continue creating the filter, narrow returned results and sort the filter into the desired order.

screenshot describing how to add and remove filter fieldsAdding/Removing Filter Fields

Step 3. Enter Filter Parameters

Filter parameters allow users to define specific constraints for how each field is filtered within the filter. This tool allows users to filter very specific data within reports and other exported files.

  1.  Enter the Query Name and a Short/Long Description (if applicable).
  2. Select the Operator for each Field. Available fields are based on data elements selected in the previous Field Selection screen.
  3. Enter the Value for each Operator. This is the value being used in conjunction with the Operator selected (i.e., student.age > 5, where 5 is the value entered and the output is all students older than 5 years of age).
  4. If a BETWEEN Operator was selected, fill in all appropriate fields.
  5. To apply multiple operators to the same field(s), click the  Add Filter  button. Selecting this button adds an additional field area where users can select an already existing filter field and apply additional operators.
  6. Enter a  Logical Expression  , if necessary. 
  7. For complicated filters that report data from several calendars and/or have many fields from many different areas, mark the Force Order checkbox. When marked, the database fields in the query are executed in a particular order to increase performance of the filter. When a filter is taking several minutes to generate, try generating it again with this checkbox marked. It is not recommended to mark this on every filter.
  8. To save the filter right now without testing it or modifying any results of the selected fields, choose Save or Save and Test.
  9. If output formatting and/or group data needs to be defined for the filter, select the Next button.

screenshot of available data fields

Step 4. Enter Output Formatting Values

  1. Enter the Query Name and a Short/Long Description (if applicable).
  2. If data should output in unduplicated records based on field values, mark the Output distinct records checkbox.
  3. If the field should appear in the filter output, verify the Output checkbox is marked. If it is not marked, the field does not display in the output but is used to filter data. For example, the field student.activeToday might be chosen to filter out inactive students (student.activeToday = 1), but the Output checkbox could be unselected so that field is not included in the output.
  4. Enter the Sequence. This number places the field in that order on the output.
  5. Enter a number in the Sort field. This determines the order in which fields are sorted.
  6. If a number was entered in the Sort field, determine how the field should be sorted by selecting a Direction. Data can be sorted by ascending or descending direction. If the Sequence and Sort fields are left blank, the fields display in the order selected and sort how the elements appear on the screen.
  7. Enter a Column Header for each field. This is the header that display in the column relating to the field. If no header is entered, the field name is used as the header for the column (i.e., student.otherID displays a column name of student.otherID if no header is entered).
  8. Determine the field's Alignment on files exported via the Data Export tool.
  9. Select the Formatting of outputted field data. These options allow users to specify how data is reported in exported files.
  10. Enter the field Length. This field determines the maximum amount of characters the field reports data before truncation. If data is exported using the Fixed Width format, each field with the Output checkbox checked must have a length value entered.
  11. To save the filter right now without testing it or modifying any results of the selected fields, choose Save or Save and Test.
  12. To continue, click the Next button to continue creating the filter, narrow returned results and sort the filter into the desired order.

screenshot of available output fields

Step 5.  Define Data Filter Grouping, Calculations and Subtotals

 The Grouping and Aggregation editor allows users to group fields into sections and report specific aggregates/sub-totals for each section.

  1. Enter the Query Name and a Short/Long Description (if applicable).
  2. Select each field to Group By for each tier. This field determines which fields are grouped into sections, allowing the field to have separate aggregate/sub-totals reported.
  3. Select each tier Group Order. This determines how aggregate/sub-total data is reported for the tier.
  4. Select the field and determine the Aggregate/Sub Total by Aggregate Type. Data within each group aggregates based on the field and Aggregate Type selected. See the table below for information about each available aggregate type

screenshot of available grouping options

Step 6. Save the Filter

To quickly save the filter, click the Save button. To quickly save and verify the filter returns data, click the Save and Test button. Both of these options save the filter and it can be found in the Saved Filter list. The Save and Test option saves the filter and generates the filter in HTML format for a quick review of the selected fields and format. Users must have pop-ups enabled on the web browser in order to view Test results.

For more advanced save features, follow the procedures below. 

  1. Determine if the filter needs to be saved to a User Account Folder. If yes, choose that radio button and select the appropriate folder.
  2. Determine if the filter needs to be available to particular User Groups. If yes, choose that radio button and select the appropriate user groups. If a filter is saved to more than one User Group, a separate copy is stored for each group. Each group can independently edit the filter without affecting another group's copy.
  3. For complicated filters that report data from several calendars and/or have many fields from many different areas, mark the Force Order checkbox. When marked, the database fields in the query are executed in a particular order to increase performance of the filter. When a filter is taking several minutes to generate, try generating it again with this checkbox marked. It is not recommended to mark this on every filter.
  4. Select the Save icon. The filter is now saved and can be selected from the Saved Filter list on the main page of the Filter Designer.

screenshot of a filter highlighted within the saved filter window

To generate a saved filter:

  1. Select the desired filter from the Saved Filter list.
  2. Choose the appropriate Calendar.
  3. Click the Test button. The filter will appear as a report in a separate window. 

Calendars cannot be selected if the query is for Census/Staff Data Types. Only calendars to which the user is assigned calendar rights are available for selection.

screenshot of a filter, the test button, and a calendar highlighted

Manage Filters

Save Filters to Folders

Ad hoc filters can be saved to specific folders created within the Filter Designer tool. Filters can be saved to User Accounts or User Groups.

For complicated filters that report data from several calendars and/or have many fields from many different areas, mark the Force Order checkbox. When marked, the database fields in the query are executed in a particular order to increase performance of the filter. When a filter is taking several minutes to generate, try generating it again with this checkbox marked. It is not recommended to mark this on every filter.

screenshot of Save To options available

Remove Fields from the Filter Parameters Editor

Fields can be removed from the Filter Parameters editor without being removed from the filter as a whole. This allows users to reduce the Filter Parameters editor to only those fields in which operators are assigned or only those fields in which the user wants to see. 

Fields removed from the Filter Parameters editor are not removed from the filter, only the user's view of the editor.

screenshot of how to remove a field from filter parameters

To remove fields from the Filter Parameters Editor, select the X next to each field. 

Removing a field from the list does not remove it from the filter output.

All fields not assigned an Operator were removed and the field IDs were automatically renumbered. The Logical Expression automatically updates to match new field IDs.

The Filter Designer tool allows users to create folders for organizing and storing Ad hoc filters. Folders can be organized in a hierarchy format, where sub-folders exist within parent folders. By creating folders, users can better manage large volumes of existing Ad hoc filters and group them in a logical order.

If a field in the query has been deactivated (displays in red), use the Element Replacement Tool to update the filter. This removes the deactivated field and adds the equivalent field to the filter.

Create Folders for Filters

Folders allow users to better manage Ad hoc filters within the Filter Designer tool.

screenshot of the create a new folder button highlightedCreate a New Folder

Click here to expand...

To create a new folder, select the Create a new Folder button. The Create a new folder editor displays.

screenshot of the create a new folder editorCreate a New Folder Editor

If the folder should not be tied to a parent folder, leave the Parent Folder field as (No Parent), enter a Folder Name and select the Save button. The folder displays in the Saved Filters field and is now available for storing Ad hoc filters.

If the folder should be assigned to a parent folder, select the parent folder from the Parent Folder field.

screenshot of selecting a folder name within the create a new folder editorSelecting a Parent Folder

Locate the appropriate parent folder. The indentation next to each folder name indicates its relationship to the previous folder (i.e., the Grandchild 1 folder is indented two times because it exists within the Child 1 (Testing) folder which exists within the Parent Folder (Testing) folder). In the example above, the folder being created exists within the Child Test Folder parent folder.

screenshot of a folder name selectedEntering a Folder Name

Once the parent folder has been selected, it displays in the Parent Folder field. Enter the Folder Name of the folder being created and select the Save button. The folder displays in the Saved Filters field and is now available for storing Ad hoc filters.

screenshot of a newly created folderCreated Folder

As seen in the example above, the created folder Test Folder - Tim now exists within its parent folder Child Test Folder.

Add a Saved Query to a Folder

Once folders have been created, Ad hoc filters can now be assigned to those folders.

screenshot of the User Account highlightedSaving an Ad hoc Filter to a Folder

To assign an Ad hoc filter to a folder, click the User Account radio button and select the folder from the Folder field.

screenshot of selecting a folder for saving to a user accountSelecting the Saved Folder

In the example above, the Ad hoc filter is being assigned to the Test Folder - Tim folder.

screenshot of an example of a folder selected and the save button is highlightedSaving the Ad hoc Filter to a Folder

Once the folder is selected, the Folder field displays the folder name. Select the Save button to save the filter to the folder.

screenshot of a filter saved to a specific folderViewing the Saved Filter in the Folder

The Ad hoc filter is now saved and accessible within the assigned folder.

Move Filters between Folders

Ad hoc filters can be easily moved and organized between folders.

screenshot showing how a filter can be moved into a folderMoving an Ad hoc Filter to a Folder

To move an Ad hoc filter into an existing folder, left-click, hold and drag the filter into the designated folder. A pop-up message displays, asking the user to confirm the action. Select the OK button to move the Ad hoc filter.

screenshot of the filter now existing within a new folderViewing a Moved Ad hoc Filter

The moved now displays under the appropriate folder.

This functionality works for moving filters in, out and to another folder.

Existing filters can be easily copied, if desired. This keeps the original version of the filter and lets users change a filter to add new fields and functions.

Copy Filters

Filters can be copied for additional editing. Select a saved filter and click the Copy button. A pop-up message displays indicating the filter has been copied. Copied filters are named Copy of [Original Filter Name].

screenshot of the copy button highlightedCopied Filters

Delete Filters

A saved filter created by a user can also be deleted by that user. Because filters can be shared with other users, only the person who created the filter has the ability to delete it.

District users cannot delete State-Published filters.

screenshot of the delete button highlightedDeleting a Filter

To delete a filter, select the filter from the Saved Filters window and click the Delete button. A pop-up message displays, confirming deletion of the filter.

Users who have updated to Release Pack E.1246 or greater have the ability to delete multiple filters at the same time by holding the Ctrl key, selecting each filter and clicking the Delete button.

Modify a Query Created by Another User

Saved filters can be edited at any time by selecting the filter and clicking the Edit button. This displays the filter so users can modify the selected fields and verify the operations and export options.

Search results on the Search tab can be populated with saved filters. When a saved filter is selected, click the Search button. Results returned in the filter displays in the Search tab.

screenshot of searching an existing filterSearching with a Filter

If a saved filter contains deprecated fields, the filter is highlighted in red within the Saved Filters window.

screenshot of an example of a filter containing deprecated fieldsFilter Containing Deprecated Fields

Test Saved Filters

To test an existing filter, select the filter from the Saved Filter window and click the Test button. A separate window displays, displaying filter results in HTML format.

screenshot of the test button highlightedTesting an Existing Filter

Last Updated, Last Run, and Last Run By Information

Users can view the last time an existing filter was updated, the last time a test of the filter was run, and who ran the last test of the filter. 

If the timestamp or user is unknown, a value of Unknown is reported.

screenshot of the last updated, last run, and last run by fields appearing for a filterExample of a Filter Tracking Information

Manage State Ad hoc Filters

Ad hoc filter publishing allows State Edition users to create and maintain Ad hoc filters which are automatically published to districts connected to the State Edition via DIS. Through this functionality, State Edition users can publish filters to connected districts, unpublish or remove State filters, and revise and republish filters back to districts, as necessary. District users also have the ability to export Ad hoc filters into a file which can be imported into State Edition.

Click here to expand...

Publish State Filters to Districts

Published filters are placed in a State Published folder (or BIE Published folder) where districts can then generate and use the filter.

Users must have Filter Designer, State Owned Filters and Publish Filters tool rights in order to use this functionality.

screenshot of the publish button highlightedPublishing an Ad hoc Filter

To publish a filter, select the filter within the Saved Filter window and click the Publish button. The filter moves to the State Published folder (or BIE Published folder).

screenshot of the Filter history button highlightedPublished Filter Moved to State Published Folder

All published filters are moved and maintained within the State Published folder. This folder indicates what state filters are currently published or unpublished (greyed out) and requires unique tool rights as a means of protecting and controlling the filters from other Filter Designer users.

Once a filter has been published, users can select the filter within the State Published folder and click the Filter History button to view the current status of the filter's publishing process.

screenshot of the filter publish history of a filterStatus of State Published Filter

The Ad Hoc Filter Publish History detail window displays the current status of a filter's publishing activity. Information is broken down by district and the Status indicates where the filter is along the publishing process. A status of DELIVERED means the filter was successfully published to the district. A Status of PENDING means the filter is attempting to publish to the district but has not completed the process. A Status of TIMEOUT means the filter was not published to the district. A status of TIMEOUT is often an indication that the district is not properly synced to the State Edition via DIS.

To refresh the window and view the most up-to-date information, click the Refresh button. If the filter has been previously published, users may view publish history information about all versions of the filter by checking the Show history for all versions checkbox. See the Republishing State Filters section for more information about republishing filters.

Manage State Filters in District Edition

State-published filters can be added or removed from districts at any time. In order to properly manage these filters, notifications are provided in the Process Inbox and filters are stored in a State Published folder. When a filter is published from the state and received by the district, schools within that district receive an indication of this within their Process Inbox.

District users MUST have State Owned Filters tool rights in order to access State Published filters.

screenshot of the notification of a new state published ad hoc filterNotification of a New State Published Filter

Users can click the notification to be directed to the filter or go to the Filter Designer tool and view/generate the filter from within the State Published folder.

screenshot of the state published folder within the saved filters windowMoved Ad hoc Filter

The state-published filter TJF Resident Town problem was indicated in the Process Inbox notification and has been placed within the State Published folder. This filter is now ready for use by district users.

screenshot showing a filter's version that is listedFilter Versions

Because filters can be unpublished, modified and then republished back to districts, the Filter Version indicates what version is currently being used. This indication is important for coordinating with the state or other schools/districts when sharing or discussing information based on the filter. The Last Updated field serves as a good indication of how new or recent a filter version may be. Each time a filter is modified and republished by the state, the Filter Version is updated to reflect this change.

Copy State Filters

Once a filter is unpublished by the State it is no longer available for use by the school or district. For this reason, users may copy state published filters for personal use. Once copied, the filter remains amongst other standard filters within the Saved Filters window and can coexist with newer, more recent versions of the state published filter. 

A copied state filter becomes the user's personal filter and is not updated when the corresponding State Published filter is updated to a newer version.

screenshot of the copy button highlightedCopying State Filters

To copy a filter, select the filter from within the Saved Filters window and click the Copy button. A copy of the state filter is placed amongst other non-state published filters and can now be edited, deleted or exported as necessary.

screenshot of a copied state filterCopied State Filter

Unpublish State Filters

State Edition users can unpublish State Published filters at any time. Once a filter has been unpublished, District Edition users can no longer view or use the filter.

Users must have Filter Designer, State Owned Filters and Publish Filters tool rights in order to use this functionality.

screenshot of the unpublish button highlightedUnpublish a State Published Filter

To unpublish a filter, select the filter from within the State Published folder and click the Unpublish button. The filter becomes a light grey color, indicating it is currently unpublished. Once a filter has been unpublished by the state, users with State Published filter tool rights receive notification of this change within their Process Alerts. This notification means the filter is no longer visible or available for generation.

screenshot of the notification a state filter has been unpublishedMoved Ad hoc Filter

Republish State Filters

Previously published and then unpublished filters can be republished back to districts.

Users must have Filter Designer, State Owned Filters and Publish Filters tool rights in order to use this functionality.

screenshot of a filter showing an updated version as it was republishedRepublish a State Published Filter

To republish a filter, select the filter from the Saved Filters window and click the Publish button. The filter turns from a light grey color to regular black font. This indicates the filter has been published. In the image above, because the filter has been previously published (Version 1) and then modified, the filter is now considered Version 2.

If a published filter has been edited after being published, an editing icon displays to the left of the filter name. This icon indicates the filter has been modified and must be republished in order for district users to receive modifications.

Once the filter has been modified and republished to districts, the Filter Version is now 3 because logic treats every modification to the filter's movement in the publishing workflow as a version.

screenshot of a notification indicating a filter version changeNotification of Republished Filter in District Edition

Once a filter has been republished by the state, schools within the district receives notification of this change within their Process Inbox. If selected, this notification directs users to the newly updated filter as well as indicate the filter's version.

District and State Edition users have the option of exporting Ad hoc filters into a file format that can be imported into the State Edition.

Users must have Filter Designer and Export Filters tool rights in order to use this functionality.

screenshot of the export button highlightedExport an Ad hoc Filter

To export a filter, select the filter from the Saved Filters window and click the Export button. A pop-up message displays, asking where to locally save the file.

screenshot of the prompt which appears when attempting to export a filterSaving an Exported Filter

Select whether to open the file using an installed program or save it locally to a hard drive or storage device. Once saved, the file can then be imported into the State Edition. 

NOTE:

  • When opening the file, click the Browse button to select the desired program in which the filter opens. This selection applies to this and every future time the same filter is selected by the user.
  • It is best to open filters in Notepad, Textpad, Wordpad, etc., unless there is a reason to export it to another format.
  • A program must be chosen for every filter available to the user.

Do not modify the file name or any data within the file. Modification of these items results in the file not being allowed to be imported.

Export and Import Ad hoc Filters

Exported Ad hoc filters can be imported into State Editions of Campus using Import Filter functionality. 

Users must have Filter Designer > Import Filters tool rights in order to use this functionality. This functionality only exists within State Editions of Campus. Filters are published down to districts using Publish functionality and districts are able to send filters to the State for import using Export functionality.

screenshot of the import filter section highlightedImport an Ad hoc Filter

To import an Ad hoc filter, select the Browse button. A pop-up editor displays, asking to select the filter from a locally stored location.

screenshot of the Import button highlightedImport the Exported Filter

Once the file has been selected, the file path populates the field to the left of the Browse button and is now ready to be imported. Select the Import button to import the file.

screenshot of the message a user receives after importing a filterConfirmation of Imported File

Once the Import button is selected, a pop-up message displays, indicating the filter was imported and listing the filter name.

screenshot of the imported filter showing in the saved filters windowView an Imported Filter

The imported filter now displays within the Saved Filters window and is able to edited, copied, deleted or published as desired.