Advanced Query Options

Overview

Axiom offers a number of advanced query options. Data can be summed, averaged, and the maximum/minimum determined using totals and subtotals, and total of records for a specific field value can be determined with summary queries.

Query Totals and Subtotals

The grid allows for various types of totaling and subtotaling on numeric fields in a query. If no numeric field already exists, the generic “record count” field can be added to a query to allow (sub)totaling. The following list details the types of (sub)total options available:

Totals vs. Subtotals

Totals apply to all records in a numeric field, whereas subtotals only apply to subgroups of all the records.

To establish totaling in a query, click on the numeric field to be totaled and select the total type from the drop-down at the bottom of the field properties section.

The process to establish subtotaling in a query is slightly different from the process for totaling. Users must first select which field(s) to subtotal by, and then set the subtotal type on the associated numeric field.

For example: to find the average posted grades for a class using a posted grades query, the field to subtotal by would be the “Class” field, and the associated numeric field to subtotal would be the “Grade: Posted” field with a subtotal type of “average.”

To select a field to subtotal by, click on the field and then toggle the “use this field for subtotal headers” option, located in the field properties section, to yes. Once the subtotal group header has been turned on, set the subtotal type on the numeric field by clicking on the field to be subtotaled and then selecting the subtotal type from the appropriate drop-down in the field properties.

It is important to note that subtotaling only becomes available on numeric fields once another field in the query has subtotal group headers turned on.

It is possible to both subtotal and total simultaneously.

For example: to find the average age of students in 4th and in 5th grade, and then find the average age of all of the 4th and 5th grade students combined, the user would subtotal by the “current grade” field, and the associated numeric field to subtotal would be the “age” field, with both a subtotal type of “average” and a total type of “average.”

Fields with (sub)totals applied are distinguished by a sigma symbol ( ∑ ) to the right of the field in the query.

Ignore Zeros for Averages

When using the average (sub)total type, the user can opt to exclude values of zero from the average. This means that a numeric field with a value of zero will not count against the average. Take the example of averaging posted grades in class: if a student does not yet have a posted grade, the numeric value is zero. Turning the “ignore zeros for (sub)totals” option on will ensure that the lack of a grade does not bring the class average down.

To ignore zeros when averaging, simply toggle the “ignore zeros for (sub)totals” option, located at the bottom of the field properties section, to yes.

Weighted Averages

Queries can display weighted averages for subtotals and totals in the query results grid. A weighted average computes according to sum(a)/sum(b), rather than avg(a/b).

Note that in summarization grouping queries, described next, picking “average” as a summarization method for a weighted average field also requires the sum(a)/sum(b) transformation, even without (sub)totals. To see an example of a weighted average in a query, review any of the Admissions Pipeline reports on the Admissions homepage in Axiom.

Summary Queries

A summary query displays the total of records for a specific field value. Users can select which fields are summarized, and the query results will show totals based on distinct values within each summarized field. When summarizing fields within a query, only the summarized field values along with record count are shown in the results.

Selecting Summary Fields

Most fields are eligible to be summarized in a query. The fields that cannot be summarized are numeric fields (i.e. a “Grade: Posted” field) due to their lack of distinct values. To set a summary field, click on the field and then toggle the “summarize by this field” option – located at the bottom of the field properties section – to yes. If multiple fields are summarized, the system prioritizes summaries by the sorting order.

Record Count Field

The initial results of a summary query show only those summarized field values with associated records and their record count. The record count field in a summary query shows the number of records associated with that field value and appears as hyperlinked text.

To view more detailed data from the associated records, drill into the summary query by clicking on the hyperlinked record count number. All records for that field value will appear in a new results grid. The information displayed in the new results grid is determined by the remaining fields from the original query.

Schools may also further summarize by the number of records that are associated with the specified field. Summarize the query by the desired field then pull in the related Record Count field into the query design. Apply criteria to the record count field, e.g. is great than 5. Under ‘Summarization’ section of Record Count field, set the summarization method to “sum” and set Apply Criteria to Sum Value to be “yes”.

Summary Totals

When a field is summarized in Axiom, the query results will automatically add a summary totals row in order to display totals for any field summarized.

Exclude Zero Values From Drill Down

This option enables filtering out 0 values for the clicked grid cell’s dynamic drill down. It is useful in a summary query that is summing a value for an aggregate total count, but by default, even records that don’t contribute to the count (ie, 0) are in the drill down.

For example in Grade Review queries, the “Ready” column is adding up that columns values, which is conventionally either 0 or 1. For the Drill Down, the expectation is that only records that contributed to the sum would be shown. Selecting “Exclude zero values in Drill Down” will only show enrollments where Ready is 1.

For example: if a user summarizes the Grade Applying For field in a “Find Candidates” query, the results will show not only the sum of candidates in each grade, but the total number of candidates across grades. That total shows up below the list of grade totals.

Managing Query Results

For more information about actions and procedures regarding query results including saving, exporting, and emailing, click here.