Survey123 Report Template: Sum Total Values

[Jahluwalia12]

Hello, 

I want to put in some lines of code in a template (word doc) to get the total amount of fields when generating a report from a survey that I published. I created a survey in which users can assess the conditions of households and properties after a natural disaster. These conditions are reported as waypoints. One group of waypoints refers to the level of damage of a property if any (i.e. affected - minor damage, completely destroyed, unaffected, etc.). Another group of waypoints refers to human activity in the area of interest, so in addition to the total amount of each type of waypoint, I also want to calculate the total amount of people reported under each kind of waypoint. 

The template (word doc) that I created when generating reports has columns for the total amount of each type of waypoint. In each of these cells, I want to put a line of code that will give me the sum of each type of waypoint. For example, if there are 5 inputs that reported a "Shelter in place" waypoint and each house has 3 people associated with it I want to be able to generate a report that shows 5 for total waypoint count and 15 for total person count in the "Shelter in place" waypoint category. 

Attached is a picture of the survey word doc showing where the totals would need to be for each waypoint category. I circled examples of the locations where the code to sum the # of waypoints and the # people would be.

Any help would be appreciated! Thanks!

[JamesTedrick]

Hi @Jahluwalia12 ,

There is some limited ability to conduct calculations within a report, however, the report syntax does not support functions such as sum() (see the 'Numbers' section of the Report documentation).  In the screenshot, it appears that you also attempt to use a statistical query (the 'stats' portion of the placeholder); this is used when aggregating data from multiple records in a summary section, not adding the values from within one record (which is in the individual section of a report).  

Aside from creating an expression in the report that adds the values together (which would achieve what it seems you want), you may also want to consider adding a calculate question to the form to calculate the value when the data is submitted.

[Jahluwalia12]

Thanks for the reply @JamesTedrick!

So I do indeed want to aggregate data from multiple records of a survey, not adding values within a singular record. I was trying to figure out how to add the values from multiple records by editing the template so as to display a number for total waypoints by type and total person count across all responses of the survey. 

This new image shows the three responses from my survey, I would like to take the data from all of these and aggregate the types of waypoints which are circled on the left side of the image (in this case it would be 2 "Shelter in Place" and 1 "Affected" ), and the Person count circled on the right side of the image (we would want 7 total for "shelter in place). I was hoping to add the code to my word document template (image in the original post) so as to automatically aggregate how many of each waypoint there are (and person count when applicable) across all responses.

Do you think this is a possibility? If not, would editing the survey or the XLS document be a better route?

Thanks, 

John

[JamesTedrick]

Hi @Jahluwalia12 ,

Based on your description, you want to use the aggregation functions in the report template. Again, refer to this section of the Survey123 documentation; if you need to break out information by category (which seems likely from the format in your first screenshot), you would also need to use the where parameter to select those records that should be summed.

[Jahluwalia12]

Great! I will attempt working it that way and I will post back once I have my results. Thanks again!

[Jahluwalia12]

Hi again @JamesTedrick,

After looking at the documentation you were referring to in your response I am still a bit stumped (I am still a beginner when it comes to coding/querying). Would you mind giving me an example of what a line of code would look like that utilizes both the "sum" aggregation and the"where" parameter?

Thanks!