Using workpad variables in esql queries

[IGB042 (Ian)]

I am developing a report in Canvas currently and several elements use the same field within their esql queries. As it is intended that this workpad would be cloned it would be really useful to use a variable in the query

Currently I have

essql 
  query="SELECT HISTOGRAM(isotimestamp, INTERVAL 1 DAY) AS day, COUNT(result) AS tot, result AS res  FROM \"MyIndex*\" WHERE account_id='DADCCUT3D44UOUCE9VDU' AND result='success' GROUP BY day, res"

I would prefer

account_id='{var \"AcctID\"}'

But I cannot get this to work. As the report queries multiple indexes for multiple elements it would be beneficial to be able to update this in one spot, rather than risk missing the change on one element.

[corey.robertson]

Hi @IGB042

You'll have to use string concatenation to get the variable into the query.

So it would look something like

essql query={
  string "select * from MyIndex where account_id='" {var name="AcctID"} "'"
}

[IGB042 (Ian)]

Awesome Corey, did find I can drop the name= from the expression.

This is going to simplify the report creation process so much.