Survey123 Report Template to count the total select_one options in the whole survey, not in a repeat or group

Question

Hi all,

I am wanting to create a report template for one survey response of an audit, and i could like to use a syntax to count the total number of questions that have a select_one category, such as 'Compliant' or 'Not Compliant'. These aren't repeated questions, or in a group but needed for the whole survey.

I have tried the below Syntax but i am getting this error as i think it's looking for one field but i need it to count over all fields.

${Audit_Form | where:"select_one='Compliance (C)' " | stats:"count"}

Error: An error occurred when checking the report template. Failed to parse ${Audit_Form | where:"select_one='Compliance (C)' " | stats:"count"} since undefined field cannot be found in the parsing scope.

I think this is because i'm trying to calculate the number of compliances in one survey and not multiple, so i'm using the wrong syntax. The only other formular I've come across is count-selected(${question_one}) but that seems to require a specific question/field. Does anyone know of the correct Syntax to use please?

TylerGraham2 · Answer

You're welcome! Copy/paste is going to be your best friend for streamlining this work. I like to setup my template formulas in Word (I find it harder to catch formula errors in the little Excel text box) and then use find/replace to make the changes to the relevant parts before pasting it into the form spreadsheet.

cat206 · Answer

Thank you both so much for your help on this! I obviously thought it would be a lot easier than it is, wishful thinking on my part. Unfortunately I do think it needs to be integrated as part of the reports so i will give it ago, although it may take me a while to implement, but your section idea is really good, I will try that for each of the 16 groups

TylerGraham2 · Answer

Knowing you have those multiple options, I took what abureaux did as a starting point and created a calculate for each response option. This follows best practices for parsing out select_multiple entries into separate fields, you could change the select_one to a select_multiple type and it would still work.

Survey123 often does not have an elegant solution to a problem and you have to do a lot of repetitive work for the setup.  To help manage that, you want to think about names for the calculations and choices.  For the calculation fields, I used a shortened version of the primary question ("example1" becomes "ex1") followed by the code for the choice (c, gp, ri, o, x, na) and then "calc." That will let you quickly reference and know what the field's purpose is. Standardizing it as much as you can will also make setup faster; you can copy/paste entire rows and will only need to change "ex1" to "ex2" and "c" to "gp" in a few places.

Since you have 70+ fields, you should think about breaking it up into manageable sections so your calculation lengths don't get too crazy. Maybe after something like 10 compliance response questions in the form you would have do a sum of that section. That way when it gets to the final sum field, you only need to enter 7 fields into the calculation (e.g. ${sect1_c_sum}+${sect2_c_sum}+${sect3_c_sum}+${sect4_c_sum}+${sect5_c_sum}+${sect6_c_sum}+${sect7_c_sum}) instead of 70 into the calculation (e.g. ${ex1_c_calc} +${ex2_c_calc} +${ex3_c_calc} +... ${ex70_c_calc} ).

Alternately if you know Javascript, the survey isn't public, and you'll have an internet connection when collecting the data, you could likely set up a more elegant solution that way.

Counting Compliance.xlsx