Generate Report with summary count and list per area

[katemcc918]

I am using Survey123 to allow field technicians to record data on 1000+ test points. The project is divided into survey areas. I have developed a report template that summarizes the work done each day, including a count of the excavated tests and a list of their IDs. I'd like to be able to separate that summary by survey area. I've figured out how to get the count for each survey area, but I can't figure out how to filter the list of tests.

Here is what I have so far:

${#Section_7_STP_Log | where: “1=1 and dug=’yes’” | stats:”count,stp_num,areacount”:”projarea”}${areacount} STPs excavated in Area ${projarea} including: ${#Section_7_STP_Log | where:”1=1 and dug=’yes’” | orderByFields:”projarea”}${stp_label}, ${/}

${/}

And here is what it returns:

6 STPs excavated in Area A including: A052, A053, A054, A055, B052, B053, B002, B003, B004, B005, B006, B007, B008,

7 STPs excavated in Area B including: A052, A053, A054, A055, B052, B053, B002, B003, B004, B005, B006, B007, B008,

I'd like for it to just list the 6 STPs in Area A ("A052, A053, A054, A055, B052, B053"), and then the 7 STPs in Area B ("B002, B003, B004, B005, B006, B007, B008"), but I can't figure out how to apply the "projarea" variable from the groupByFieldsForStatistics parameter in the first query to the query for the list.

[Neal_t_k]

I think this is what you are after. Wrap your current code in an if statement to filter the results.  https://doc.arcgis.com/en/survey123/browser/analyze-results/featurereport-conditionalelements.htm

[katemcc918]

I'm not sure how to implement an if statement here. I tried this but it doesn't filter the list:

${#Section_7_STP_Log | where: “1=1 and dug=’yes’” | stats:”count,stp_num,areacount”:”projarea”}${areacount} STPs excavated in Area ${projarea} including: ${if projarea}${#Section_7_STP_Log | where:”1=1 and dug=’yes’” | orderByFields:”projarea”}${stp_label}, ${/}${/}${/}

I don't have a discrete value to use in the if statement because they're being dynamically listed by the stats block in the first query. We've had at least 20 lettered areas in some sections so I don't want to have to type out an if statement for each of them if I can avoid it. How do I dynamically reference the value that query is returning in the if statement I'd use to filter the second query?

[Neal_t_k]

Ah okay, yes that would be a pain.  Its been a while for report templates...have you tried "returnDistinctValues"?

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

[katemcc918]

The closest I've come is getting it to list the project area ahead of each test using this:

${#Section_7_STP_Log | where: “1=1 and dug=’yes’” | stats:”count,stp_num,areacount”:”projarea”}${areacount} STPs excavated in Area ${projarea} including: ${#Section_7_STP_Log | where:“1=1 and stp_type <> ’radial’ and dug=’yes’” | outFields:”projarea,stp_label” | returnDistinctValues:true}${projarea}: ${stp_label}, ${/}${/}

I now realize that the projarea as referenced in groupByFieldsForStatistics can't also be referenced outside that clause. What I need is a way to includes stp_label or stp_num in the first query in a way that lets it be referenced in the output, but the only way appears to be using aggregate functions, which by definition don't return lists. In short, I think I'm asking for the impossible, at least under the current Survey123/XLSForms syntax.

[Neal_t_k]

I have no idea if this will work but you could at least try combining stats and outfields, it couldn't hurt.

${#Section_7_STP_Log | where: “1=1 and dug=’yes’” | stats:”count,stp_num,areacount”:”projarea”|outFields:"stp_label"|returnDistinctValues:true}${areacount} STPs excavated in Area ${projarea} including:${stp_label}${/}

[Neal_t_k]

@katemcc918 I can't get it to work, your original code is the closest I was able to produce as well,  if they could add "concatenate" to the stats options that would be ideal.  Maybe post that as an idea.

[katemcc918]

Good idea! https://community.esri.com/t5/arcgis-survey123-ideas/add-ability-to-list-dynamically-filtered-record...