Report template field not working with where clause

ZenMasterZeke

I'm having a problem with an expression in my Survey123 report template. If I take an expression without a where clause, it works fine, but if I add a where clause, the syntax checker returns "Invalid expression". Is there something with the expression, or is this a known limitation? Thanks!

 

// Invalid expression
${(MySurvey | where: “actual_waterbody = ‘Maple River’”| stats: “sum, tot_obs”) – (MySurvey | where: “actual_waterbody = ‘Maple River’” | stats: “sum, tot_a_obs”)}

//Valid expression
${(MySurvey | stats: “sum, tot_obs”) – (MySurvey  stats: “sum, tot_a_obs”)}

 

TylerGraham2

Get ready to be really angry about this. The problem is Word is auto extending the minus sign to a hyphen. Essentially - to -- and that makes it an invalid expression because you are not subtracting, you trying to hyphenate the two values. 

I created a dummy survey and data set and got the same results you did, I was trying multiple outStatistics as a solution (which works and is more elegant since you only query the data once) and I caught Word making the change.  I tried it and it failed too.  I went back into the template, changed the hyphen back to a minus sign and made it didn't autocorrect back to a hypen and then the expressions were valid.  

Here's my setup for the outStatistics if you're interested:

${#Fish_Form_1|where: “actual_waterbody = ‘Maple River’” | stats:"sum ,tot_obs,totalobs;sum,tot_a_obs,total_a_obs"}

${totalobs - total_a_obs}

${/}