Report queries

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.

Note:

Summary sections cannot contain map images or anything related to attachment binary files. This includes attempting to extract EXIF data. However, attachment name and size are available.

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 gender:

${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

Multiple outStatistics parameters can be set by separating them with a semicolon. If multiple parameters are used, the query will output as an array, which will require specifically calling their results, as in the following example:

${#mainLayer | stats:"sum,pop2000,populationTotal;avg,income,averageIncome"}Population total: ${populationTotal}, Avg. 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 used to display 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 formulate a filter for your survey, there are additional parameters and qualifiers that can be used to enhance your query.

  • where—A where clause for the query. Any legal SQL where clause operating on the fields in the layer is allowed. For example, where:"weather='sunny'" will cause the filter to only use responses where the weather field is exactly the value sunny. if where is used with other filters, they should be implemented in the order where, stats, orderByFields, resultRecordCount.
  • !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 where 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.