Running Searches in Statistics Mode

When you run a search in statistics mode, the results of your search are displayed as statistical data in a table. This search type enables you to return data groupings based on the search results of the query. You can also display this data in a chart, which can provide you with a more concise visual presentation of the information.

Statistical Report Functions

Create statistical reports by grouping specific field values in the search results, and by applying aggregate functions, such as Group by, Count of, Average, Median, Mode, and Sum, Minimum of, or Maximum of, to other field values.

To render a chart from a statistical report, the results of a statistical query must properly form a series. For example, Group by and Count of options define the series results.

The following table describes the types of statistical report functions.

Function	Description
Group by	A function that returns a record for each distinct value stored in the specified field. Use this function with the Aggregate function. For example, if you are performing a statistics search in the Facilities application, and you apply the Group by function to the Region field and apply the Count of function to the Facility Name field, you can produce a report listing the number of facilities by region. Additionally, if you apply the Group by function to two or more fields in the Selected list, the statistical report is grouped by the first Group by field in the list, and all other fields with the Group by function are nested in the previous Group by field in the list. Therefore, building on your statistical search in the Facilities application, you can nest the Type field with the Group by function under the Region field, which produces a report that lists both the number and type of facilities by region. Note: For a cross-reference field, the internal segment count result may be different if the Group by is on a parent application as opposed to a sub-form. The Statistics Search is limited in this case because if the last Group by field is in the parent application, it only counts the relationships to the parent application.
Group by (Date Range)	Groups data of date-based fields (for example, Date, First Published, and Last Updated) into specific date ranges, such as "by Week" or "by Day." Returns a record for each distinct value stored in the specified field. Use this function with one of the aggregate functions. For example, you are performing a statistics search in the Incidents application. You would apply the Group by Month function to the Date Reported field and apply the Count of function to the same field. The search produces a report listing the number of incidents reported by month.
Count of	Returns a count of each distinct value returned for a specified field (aggregate function).
Average	Returns the average of all numeric values for a specified field. When you drill down on the value in the Average of [Selected field] column, you see the records that were calculated to the average value (aggregate function).
Median	Returns the middle value of all numeric fields in a list of records. When there is an even set of records, a calculated value shows as the average of the two middle values. For example, a list of values is 1, 2, 3, 4, 5, 6, The value shown is 3.5 ([3 + 4] / 2). When you drill down on the value in the Median of [Selected field] column, you see the records that were calculated to the median value (aggregate function).
Mode	Returns the value that occurs most often of all numeric fields in a list of records. When you drill down on a value shown in the Mode of [Selected field] column, you see the records that were calculated to the mode value or multi-modal values. If no value occurs multiple times, (no value) is shown in this column. If the list of records have multiple values that occur more than once, the values that occur most often are listed. For example, the multi-modal results are 6, 15, and 25. When you drill down on 6, you see only the records where 6 appears most often. The same is true for 15 and 25. Charting is not available for a multi-modal result (aggregate function). A Featured Metric chart is only available when a search using one aggregate generates a single output. Otherwise, the system displays the standard search results. When making a Featured Metric chart, in order for the defined numeric or percentage record limit to match, the sorting criteria must be identical between the Sorting section and Sorting sub-section of the Statistical Drill Down Options section on the Advanced Search page.
Sum	Returns the total of all numeric values for a specified field (aggregate function).
Minimum of	Returns the minimum numeric value for a specified field (aggregate function).
Maximum of	Returns the greatest numeric value for a specified field (aggregate function).

In the search results, a Totals row can be listed for each unique group. For example, you are running a statistics search in the Facilities application using the Display Totals function combined with the Group by and Count of functions. You would set the search results to list the number of facilities in each of your regions and the total number of facilities in all regions.

Run a Search in Statistics Mode

1. Go to the Search Records page.

   1. From the menu bar, click the Solution menu.
   2. Click the solution.
   3. From the Applications list, click the application or questionnaire.
   4. Click the Search button.
2. (Optional) In the Keyword Search section, enter the keyword or phrase.
3. In the Fields to Display section, select which fields appear in the search results.
   1. Select the Statistics Mode checkbox.

      Note: Statistics mode searches always use SQL queries to obtain results.

   2. From the Available list, select the fields to which you want to apply grouping and aggregate functions. You can add the same field to the Selected list multiple times to apply different functions to the same field.

      Note: If the application in which you are searching is related to other applications, you can include fields from the related applications.

   3. (Optional) Do one or more of the following:
      • To remove fields from your search results, click to the right of the field in the Selected list.
      • To reorder the fields in the Selected list, click a field and use the to move it up or down in the list.

      Note: If you select to view fields from related applications or data levels, or fields from within a history log or sub-form field, you can reorder how these fields are displayed in their respective grouping. However, you cannot mix fields contained in these items with fields from your primary application. All contained sub-form fields must be displayed together.

   4. From the Selected list, select the grouping, count of, or aggregate function that you want to apply.

      Note: The order of the fields determines the relationship among multiple groups. For example, if you apply the Group by function to two or more fields, the second Group by field in the list becomes nested beneath the first field.

4. (Optional) In the Filters section, filter what records are returned in the search results.
1. In the Field to Evaluate field, select the field to evaluate for one or more specific values.
2. In the Operator column, select the filter operator.
3. In the Value(s) column, click the ellipses and select the values for the condition.

   Note: Values lists with 2000 items or more must be manually expanded by clicking the + icon.

4. (Optional) To create additional conditions, click Add New and repeat steps a - c.
5. (Optional) If you create more than on condition, apply logic to your search criteria in the Advanced Operator Logic section.
5. (Optional) In the Sorting section, sort records in the search.
1. In the Field column, select the field that is the primary sort for the search results.
2. (Optional) Click Add New to add additional fields for sorting.
3. In the Order column, select to sort records in ascending or descending order.

   Note: When items in a value list are custom ordered, the order is used for the ascending or descending in the sort order, not an alphabetic sort. Null (blank) values are listed first in ascending sorts. Prefixes and suffixes are not evaluated when determining a sort order.

4. In the Grouping column, select whether to enable grouping for the search results displayed in the column-hierarchical format.

   If you select to enable grouping, you can expand and collapse sections of the search results based on the values in the sorting field.

5. (Optional) In the second row of the Field column, select a field by which results are sorted after the initial sort.
6. (Optional) Select the order and grouping preferences for this field.

If you select to enable grouping, you can expand and collapse sections of the data point search results based on the values in the sorting field. This option is available for only the Column-Hierarchical display format.

Note: When making a Featured Metric chart, in order for the defined numeric or percentage record limit to match, the sorting criteria defined here must be identical to that of the Sorting sub-section of the Statistics Drill Down Options section.

6. (Optional) In the Display Options section, configure the display options for your search results.
   1. From the Display Format list, select one of the available display format options.
   2. From the Results Per Page list, select the number of records you want displayed on each page.
   3. In the Headings field, select the content to display as the header.
   4. From the Record Count list, set the limit of records to display in the search results.
      1. Select one of the following:
         • Return All
         • Limit To
      2. If you selected Limit To, select one of the following:
         • Records. In the Records field, set an integer 1 through 1000 to limit the number of total results shown.
         • Percent. In the Percent field, set a percentage 1 through 100 to limit the number of total results shown. If selected, the number of results returned is always rounded up to the nearest integer.

         Note: Due to tie resolution inclusion—based on the primary aggregate sort, the number of records returned may exceed the defined limit.

   5. (Optional) Choose one or more of the following display options:
      • Display Totals
      • Display Zero Values
      • Fix Headers
7. In Statistics Drill Down Options section, configure the settings that you want applied when a user drills into the search results.
   1. In the Fields to Display section, configure the fields that you want displayed.
   2. In the Sorting section, configure how you want the records to be sorted.
   3. In the Display Options section, configure the drill down display options.

   Note: When making a Featured Metric chart, in order for the defined numeric or percentage record limit to match, the sorting criteria defined here must be identical to that of the Sorting section.

8. Click Search.