Feature Report summary statistics / aggregate functions for orderBy with multiple filters

Question

Good Morning,

I have a Survey123 form for a Tree Inventory survey which contains a repeat (${#trees_repeat}).  Within the repeat, I have a field that lists the species of the tree ( ${tree} ) and the assessed condition of the tree ( ${condition} ), populated from a list with values like "poor", "fair", etc.

My feature report template includes a table listing every individual tree by ${tree_ID} that is working well:

but I also need to create a summarized section with a count of condition ratings, grouped by species. Here is an example from the reports that are arborist are currently making manually, which is the goal I'm aiming for:

And this is as close as I've been able to get thus far (orderBy and the TreeCount for each species working as I want it to, but I'm not sure how to summarize the condition counts for each species in the table):

The code I'm using in the feature report table to print out the repeat records sorted by species is:

${#trees_repeat | stats:"count,objectid,TreeCount":"tree": "" | where:"1=1" | orderByFields:"tree ASC" }${tree}

And the whole table (including the counts by conditions for all the records in the repeats, not just the species)

Species

Count

Excellent

Poor

${#trees_repeat | stats:"count,objectid,TreeCount":"tree": "" | where:"1=1" | orderByFields:"tree ASC" }${tree}

${TreeCount}

${trees_repeat | where:"condition = 'excellent' " | stats:"count,objectid"}

${trees_repeat | where:"condition = 'fair' " | stats:"count,objectid"}${/}

If what I'm after is possible, I assume that I need to add in these other summaries to the code in the first column using multiple outStatistics parameters, but I haven't been able to figure out how to include a filter or where clause for just those specific outputs/results.   I was thinking something like this (which doesn't work):

${#trees_repeat | stats:"count,objectid,TreeCount;count,condition='poor',PoorCount":"tree": "" | where:"1=1" | orderByFields:"tree ASC" }

Any help would be greatly appreciated.  I've been using the help doc here as a reference, but haven't been able to make it work for my purposes - Report queries—ArcGIS Survey123 | Documentation

Thanks

DerrickWestoby · Answer

@JamesTedrick , would you mind helping to clarify this answer?  I've tried everything I can think of to get it to work, but no luck so far (and this is something we'd like to do on other reports, so it would be widely used).

The doc I attached is the one I've been using as a test to get that table displayed as needed.  The first table in there works great to summarize by tree condition, but still no luck with the By-Species table at the very end.

TESTING - Tree Inventory export template.docx

DerrickWestoby · Answer

Hey James, thanks for the response.

Do you mean I should have something like this in the "Poor" column? 
${#trees_repeat | stats:"count,objectid,PoorCount":"tree": "" | where:"condition='poor'" | orderByFields:"tree ASC" }${PoorCount}
I thought I tried that last week, but maybe it was just a syntax error I was getting if so.

If I'm way off, would you mind giving an example?

JamesTedrick · Answer

Hi @DerrickWestoby ,

Given your description, you should also be using a groupByFieldsForStatisitcs parameter with the tree field so that the counts are grouped by tree type.