Back to Top

Report queries

In this topic

  1. Add a summary section
  2. Aggregate functions
  3. Include select values
  4. Additional syntax

The Survey123 website's report functionality allows you to create detailed reports of individual survey responses, summarize your data across a number of responses, and aggregate results. This topic describes how to use queries to summarize and aggregate results and present these in a summary section.

Summary functions do not incur a credit charge beyond the cost of any other reports printed at the same time.

Add a summary section

To include a summary section in a report, include summary tags in your report template. A summary section will only be produced once if multiple records are selected for the report, while the rest of the report template will be produced for every record. This is done using the following syntax:

$<$summary>
...
$</>

Summary sections can be placed at the start and end of reports, but they cannot be placed within the contents of a report. For example, if your template includes a title, this title should be either within a summary section at the start of the report or after the summary section has been closed.

If a map is included in a summary section, it will print all geometries of all input records on a single map. You can also use additional syntax to further specify the geometry displayed. See the following example:

  • ${inspectionLocation | size:400:300}—Displays all input records.
  • ${inspectionLocation | orderByFields:"creationDate DESC" | resultRecordCount:3 | size:400:300}—Displays the three most recent records among all input records.
  • ${$shape | where:"1=1 !important" | drawingInfo:"currentLayer" | size:400:300}—Prints all geometries on the main layer using the main layer's drawing info.
Note:

In a summary section, printing a map or image, including retrieving properties from an attachment file except for attachment name and size, is only supported outside of repeats. Otherwise, an error message will appear.

The following table contains supported methods that are useful for either presenting statistics or shaping the presentation of your report's data:

MethodDescriptionExample

where

Returns only records that fit a certain criteria.

${#mainLayer | where:"status='broken'"}...${/}

stats

An aggregate function derived from certain records in the layer.

${mainLayer | where:"status='broken'" | stats:"count,objectId"}

orderByFields

Orders printed records in a report by one or more fields in ascending or descending order.

${#mainLayer | orderByFields:"state_name ASC,POP2000 DESC"}...${/}

resultRecordCount

Sets a specific number of records to print.

${#mainLayer | resultRecordCount:3}...${/}

returnDistinctValues

If set to true, returns unique values if used with outFields. If used with the count statistic type, this method returns the count of unique values in the field.

${#mainLayer | outFields:"status" | returnDistinctValues:true}...${/} or ${mainLayer | stats:"count,stateName" returnDistinctValues:true}

outFields

Use together with returnDistinctValues to get unique values of a field or combination of fields. This method accepts a comma-separated list of fields.

${#mainLayer | outFields:"status" | returnDistinctValues:true}...${/}

Aggregate functions

Summary sections, in addition to accepting all expressions for report templates, are ideal for the use of a number of aggregate functions. These functions can be used to return a result derived from every response printed for the report. They do not return results from the entire feature layer, unless a report is being generated for all features or the !important qualifier described in Additional syntax is used. Aggregate functions can also be used in individual reports, as well as for repeats.

Tip:

When previewing a report, aggregate functions produce results from every response selected to print, even though preview sample reports only print a maximum of 20 results at a time.

To extract and aggregate values in the summary section, you must define the layer or repeat name with the field name from which you intend to extract values. Be aware that for surveys created in the Survey123 web designer, the default layer name used for surveys is survey. The layer name used for your survey responses can be found in the Quick reference available on the Manage templates dialog box.

Aggregate functions are controlled through the use of a filter called stats. This filter behaves identically to feature service queries, and uses the following format:

${layerName | stats:"outStatistics":"groupByFieldsForStatistics":"havingClause"}

The outStatistics parameter defines the statistical operation and the field being used, and is streamlined for summary reports, accepting three parameters as a comma-separated list. In order, define the following:

  • A statistics type.
  • The question from which you want to pull the results.
  • Optionally, include a field name for the resulting calculation; if this is done, the field name can be referred to elsewhere in the same way as any survey question.

The groupByFieldsForStatistics and havingClause parameters accept the same values as with feature service queries, and are optional. They can be omitted if not needed.

The following example would create a sum of all results provided to the question named pop2000:

${mainLayer | stats:"sum,pop2000"}

The supported statistics types are as follows:

ValueDescription

avg

Mean of all responses

count

Amount of responses

sum

Sum total of all responses

stddev

Standard deviation from the mean

min

Lowest response to the question

max

Highest response to the question

var

Variance from the mean

range

Range of values in all responses (maximum minus minimum)

first

First response to the question

last

Last response to the question

median

Median of all responses

Multiple outStatistics parameters can be set by separating them with a semicolon. If multiple parameters are used, the query will output an array of results. In the following example, both the population total and average income are calculated and displayed from records in the main layer:

${#mainLayer | stats:"sum,pop2000,populationTotal;avg,income,averageIncome"}
Population total: ${populationTotal} 
Average income: ${averageIncome}
${/}

Include select values

Individual values from questions can be printed by using the same functionality used for repeats in individual reports. By constructing a filter to pull only select values, you can augment your summary with specific information pulled from your survey.

Note:

If you're using a filter in an individual report, the individual record is used as the default context for your filter. You may need to use additional syntax to produce the result you intend.

An example of a filter using only select information is below, followed by explanations of its individual elements.

Average populationOrganization name

${#All_Orgs | stats:"avg,POP,AvgPOP":"Org_NAME":"AVG(POP)>200" | orderByFields:"AvgPOP DESC" | resultRecordCount:5}${AvgPOP | round:2}

${Org_NAME}${/}

  • #All_Orgs—The specific layer being referred to for the required data. This begins the declaration of an area to pull response data in, as well as for the stats filter.
  • "avg,POP,AvgPOP"—The outStatistics parameter for the stats filter. In this case, it returns the average numbers of responses to the question named POP, and saves them in a field named AvgPOP.
  • "Org_NAME"—The groupByFieldsForStatistics parameter for the stats filter. This will group the average responses according to the responses to the question named Org_NAME.
  • "AVG(POP)>200"—The havingClause parameter for the stats filter. This ensures that only responses with an average population above 200 are included.
  • orderByFields:"AvgPOP DESC"—The orderByFields filter lists the responses based on the contents of a specific field. In this case, responses are listed according to the AvgPOP field created by the outStatistic parameter, in descending order.
  • resultRecordCount:5—The resultRecordCount filter limits responses only to a specific number; in this case, 5.
  • ${AvgPOP | round:2}—The contents of the AvgPOP field, rounded to two decimal places. This is the only element in the first cell that will display in the summary; the stats filter itself will not display.
  • ${Org_NAME}—The contents of the Org_NAME field from the survey responses.
  • ${/}—Ends the declaration of an area to pull response data in. Because all of this information is only in a table, both the beginning and ending of this area must be declared inside of the same table.

The result of this filter will display five rows of responses in the table, adding the extra rows it needs. The resulting summary report will look similar to this:

Average populationOrganization name

2360.23

A

1639.75

C

1179.7

B

915.93

D

795.57

E

Additional syntax

In addition to the above parameters used to construct a filter for your survey, there are additional parameters and qualifiers that can be used to enhance your query.

  • where—A legal SQL WHERE clause that operates on the fields in the layer.
    • An explicit value—For example, where:"weather='sunny'" will cause the filter to only use responses in which the weather field is exactly the value sunny.
    • The contents of a field—For example, where:"weather='"+cloudCover+"'" will use the contents of the cloudCover field as the requirement for the weather field.
    • Additional parameters—For example, where:"weather='"+cloudCover+"' AND temperature='high'" will use responses in which the weather matches the cloudCover field, and the temperature field is exactly the value high.
  • !important—This qualifier can be added to any filter but resultRecordCount to override any constraints on the values that the filter returns. For example, if you were printing reports from only the last seven days, where:"weather='sunny' !important" would cause the filter to only use responses in which the weather field is exactly the value sunny, but pulls from all applicable responses in the feature layer, rather than just the responses selected to be printed.
  • $inputParams—This placeholder can be used within any filter but resultRecordCount to refer to the parameters you set on the Survey123 website when printing a report. These parameters can then be further qualified to narrow down your response. For example, orderByFields:"$inputParams, objectid DESC" will order the fields using the ordering set in the Survey123 website, then descending by the object ID value for all fields with matching parameters. You can also use $inputParams to apply these parameters to repeats.
Note:

If where is used with other filters, they should be implemented in the following order: where, stats, orderByFields, resultRecordCount.

Feedback on this topic?

In this topic
  1. Add a summary section
  2. Aggregate functions
  3. Include select values
  4. Additional syntax