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

[cat206]

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?

[abureaux]

Outside of a repeat, each select_one is its own question, and so needs to be counted individually. Also, depending on how you have your form set up, you could use slightly different methods. Going off of your first example, where you simply want to know how many things are "Compliant" and "Not Compliant":

If you have 5 select_one questions, you will need 2 calculates for the totals plus one calculate for every select_one (7 total).

[cat206]

That you so much for responding! Crikey, this doesn't sound great though, i have over +70 questions in the audit all using the same drop down but all in different groups / pages. It's such a simple thing to do in excel so i just assumed it could be easily done in the survey. Below are my choice list for the select_one so there are more options than Compliant or Non-compliant, apologies for not specifying that earlier so i'm not sure that code will work, i honestly thought it might be quite simple. I'm wondering if i could potential count each group instead, i'm not sure if they work the same as repeats? I have 16 different groups so that would be a lo easier than having to calculate each question. The end result is I'm looking to create a summary table which counts each "select_one category" of the whole survey

category	Compliance (C)	Compliance (C)
category	Good Practice (GP)	Good Practice (GP)
category	Recommendation for Improvement (RI)	Recommendation for Improvement (RI)
category	Observation (O)	Observation (O)
category	Non-conformance (X)	Non-conformance (X)
category	Not Applicable (NA)	Not Applicable (NA)

[abureaux]

Well, do you need to provide a score on the spot? If not, another thing to think about is that you could do the processing outside of S123.

It really depends on what your deliverable is, but you could either dump everything into Excel on One Drive, or a SharePoint (SP) list.

For our audits, we used to provide a score on the spot, but stopped that as we are transitioning to ISO. Now, all scoring is behind the scenes and our deliverable is a PowerBI (PBI) report. All audits are dumped into SP, and PBI does the processing/presenting.

[TylerGraham2]

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.  

[cat206]

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]

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.