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}

${/}

View solution in original post

TylerGraham2

Try removing the spaces between the elements of your where clause and see if that works.

Instead of:

where: “actual_waterbody = ‘Maple River’”

try:

where:"actual_waterbody='Maple River'"

ZenMasterZeke

Thanks @TylerGraham2 , interesting idea, but still evaluates to 'Invalid expression' in the syntax checker. What I'm trying to do, in case my code isn't clear: subtract the sum of anglers observed (tot_anglers_obs) from the sum of all observations (tot_obs). This value should always be >= 0, although I've noticed that the sum function doesn't return anything (visible, at least) if the result is 0.

Error: An error occurred when checking the report template. Failed to parse
 ${(River_Survey |where:“actual_waterbody=‘Maple River’”| stats: “sum,
 tot_obs”) – (River_Survey|where:“actual_waterbody=‘Maple River’” | stats:
 “sum, tot_anglers_obs”)} since it is an invalid expression.

TylerGraham2