Feature reports: counting individual entries for a select_multiple question

[SFM_TravisBott]

Howdy,

@Anonymous User , or anybody else that can help. 

I am producing a feature report and would like to summarize the individual entries in a select_multiple question.

Users have the options to select any combination of A, B, C, D. I would like to be able to count across all records how many times each individual answer (A, B, C, or D) appears. The documentation only notes legal WHERE clause, so that if I broke them each out (e.g. where: "field = 'A'") it would only count options where 'A' was the only option chosen (ignoring A,B, or A,B,C, etc).

Is there a technique to count every time 'A' is chosen? E.g. in stead of WHERE field = 'A', an equivalent of field LIKE '%A%'?

This is possible using feature sets in dashboards to break them out and summarize them individually but it would be really helpful to count them up in a report. 

[DougBrowning]

Does where except contains?  One way around multiple.  You could also use selected and count them in the form.

Also look the examples here Include select values it may work I but not sure.

https://doc.arcgis.com/en/survey123/browser/analyze-results/featurereportqueries.htm 

Hope that helps

[SFM_TravisBott]

@DougBrowning You'll have to be more explicit about what techniques you're referring to. You're just tossing stuff into the wind. 

The documentation you passed along is the same documentation I link in my post. I've read it and can't find what I need as this scenario isn't mentioned (or at least I think it's hard to tell if they're trying to mention it, but doing a poor job of it). Do you have a specific part you're referring to?

The highlighted section may be trying to speak to it but I don't think so. They don't really provide a clear example of how this works since they don't share the data structure they have in mind or the results of the example queries. I am guessing here that 'cloudCover' might have a value like 'overcast' and it's asking if the field 'weather' might have the same value, (where weather = 'overcast')? I don't really know. 

Anybody else out there? @Ruth_JiatengXu  ?

[DougBrowning]

I thought I gave you all the info you need to test this.  

With contains your where clause can be

where: "CONTAINS(field, 'A')"

Not sure all SQL is supported or not.

using LIKE could also work LIKE '%A%'

Then 123 has stats like count so I think something like

${mainLayer | where: "CONTAINS(field, 'A')" | stats:"count,field"} may work.

Hope that helps

[ZhifangWang]

Hi @SFM_TravisBott ,

@DougBrowning  is correct. The general idea is to combine the "where" and "stats" capabilities mentioned in the doc together to get the statistics result.

For example, if a select_multiple question "q1" has 3 choices named as "A", "B", "C", I can confirm this syntax in a report template ${survey | where:"q1 LIKE '%A%'" | stats:"count,objected"} will output 2 if you current got 3 answers: "A", "B,C", "B,A,C".

You can find more information about the idea using statistics in the report https://community.esri.com/t5/arcgis-survey123-blog/survey123-tricks-of-the-trade-summary-sections-i... . Hope this helps.