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

1707
3
06-11-2021 09:57 AM
DerrickWestoby
Occasional Contributor III

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:

DerrickWestoby_1-1623429454344.png

 

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:

DerrickWestoby_0-1623429327035.png

 

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): 

DerrickWestoby_2-1623429622197.png

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

 

0 Kudos
3 Replies
JamesTedrick
Esri Esteemed Contributor

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.

0 Kudos
DerrickWestoby
Occasional Contributor III

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? 

DerrickWestoby
Occasional Contributor III

@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.