# Canvas - Error in populate dynamic variable/parameter

**URL:** <https://discuss.elastic.co/t/canvas-error-in-populate-dynamic-variable-parameter/307131>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [June 14, 2022, 10:31am UTC](https://discuss.elastic.co/t/canvas-error-in-populate-dynamic-variable-parameter/307131 "2022-06-14T10:31:27Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Chiaaa](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Chiaaa](https://discuss.elastic.co/u/Chiaaa)\
**Post date:** [June 14, 2022, 10:31am UTC](https://discuss.elastic.co/t/canvas-error-in-populate-dynamic-variable-parameter/307131/1 "2022-06-14T10:31:27Z")

</div>

Hi !  
I'm trying to create a Data Table in canvas.  
I would like to add 3 columns in my table:

- Asset
- Volumes (as a count of record)
- % On Total (as Asset Volumes/Total Volumes)

I create the follow script in espression editor, but my espression failed with the message "[essql] \> Can not cast 'datatable' to any of string,number, boolean".

How can I change my espression ? There is a way to populate a variable or a parameter with dynamic value? (In my case with the count of total row ? )

```auto
filters
| var_set name="Total" value={
    essql query="SELECT count(*) FROM \"index-pattern*\" where Date > dateadd('MM',-1,curdate()) and ReportDate < dateadd('DD',-2,curdate()) "
  }
| essql 
  query="select Asset,
count(*) as Volumi,
count(*)/? *100 as PercOnTotal
from \"index-pattern*\"
where 
Date > dateadd('MM',-1,curdate()) and Date < dateadd('DD',-2,curdate()) 
group by Asset" parameter={var "Total"}
| table
| render

```

Thanks,  
Chiara

---

<div class="post-metadata">

**Author:** ![preetish\_P](https://avatars.discourse-cdn.com/v4/letter/p/77aa72/32.png) [@preetish\_P](https://discuss.elastic.co/u/preetish_P)\
**Post date:** [June 17, 2022, 8:31pm UTC](https://discuss.elastic.co/t/canvas-error-in-populate-dynamic-variable-parameter/307131/2 "2022-06-17T20:31:04Z")

</div>

Hi @Chiaaa ,

How about a different approach like below ?

```auto
filters
| essql 
  query="select Asset,
count(*) as Volumi
from \"index-pattern*\"
where 
Date > dateadd('MM',-1,curdate()) and Date < dateadd('DD',-2,curdate()) 
group by Asset"
|staticColumn name=total_records value={math "sum(Volumi)"}
|mapColumn name=PercOnTotal expression={math "(Volumi/total_records)*100"}
|columns include=Asset,Volumi,PercOnTotal
| table
| render

```

---

<div class="post-metadata">

**Author:** ![Chiaaa](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Chiaaa](https://discuss.elastic.co/u/Chiaaa)\
**Post date:** [June 24, 2022, 10:49am UTC](https://discuss.elastic.co/t/canvas-error-in-populate-dynamic-variable-parameter/307131/3 "2022-06-24T10:49:12Z")

</div>

Thank you very much @preetish_P !

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 22, 2022, 10:49am UTC](https://discuss.elastic.co/t/canvas-error-in-populate-dynamic-variable-parameter/307131/4 "2022-07-22T10:49:52Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.