S123 Summary Report returnDistinctValues

Dawn_Bondy

Hi All,

I'm trying to use a report query to show a count of unique values using the method "returnDistinctValues", but I get an error that it's an invalid expression. 

Error: An error occurred when checking the report template. Failed to parse ${anuran_observations | stats:"count,anuran_species" returnDistinctValues:true} since it is an invalid expression.

The idea is that if I have the following species recorded: Wood Frog, Mink Frog, Wood Frog, Spring Peeper; the count will be 3 rather than 4 (Wood Frog being counted once instead of twice).

If I remove "returnDistinctValues:true", the count works, but counts all values rather than those that are unique. Any idea what I'm missing? Am I using this method wrong? 

 

CharlottePeters95540

I had a similar issue and "returnDistinctValues" parameter didn't work for me either. I was able to get it to work by using the count statistic and group parameter.

Try something like this:

${#RecordingRepeat | where:"RecordedSpecies='frog'" | stats:"count,objectid,FrogCnt":"FrogSp"}${FrogSp}: ${FrogCnt}

${/}

Explanation of expression parts:

• #RecordingRepeat = name of repeat section
• where: = optional where clause if you need to filter records
• stats: = count the number of objectid (1 object ID represents 1 record) for recorded species in the repeat and store the count in the "FrogCnt" variable, but group the counts by frog species in the "FrogSp" attribute field
• for the list, return the frog species LABEL value (${FrogSp}) and frog species count (${FrogCnt})
• the closing or end ${/} tag is put on its own separate line to create a carriage return and put each species on its own line in the list; otherwise, the frog species count will run together on the same line

You should end up with:

Wood Frog: 2

Mink Frog: 1

Spring Peeper: 1