How to pass Values from Drop-down Filter in to ESSQL Query in Kibana Canvas Workpad

[Sankalan_Banerjee]

Hi,

We have created a Drop-down Filter in Kibana Canvas Workpad which shows the List of Defect Fix Versions :-

 `essql query="SELECT defect_fix_version FROM \"project_defect_list*\""`

| dropdownControl valueColumn="defect_fix_version" filterColumn="defect_fix_version"
| render

Now we created a Markdown Content which shows the Test Effectiveness %.

essql
"SELECT defect_component, COUNT(defect_jira_id) AS count FROM \"project_defect_list\" GROUP BY defect_component"
| mapColumn "defect_component"
fn={if {getCell "defect_component" | eq "Exists in Prod"} then={getCell "defect_component"} else="Other"}
| ply by="defect_component" fn={math "sum(count)" | as "count"}
| staticColumn "total" value={math "sum(count)"}
| mapColumn "percentage" fn={math "floor(count/total * 100, 0)"}
| filterrows {getCell "defect_component" | any {eq "Other"}}
| markdown {getCell "percentage"} "%"
font={font family="'Open Sans', Helvetica, Arial, sans-serif" size=30 align="center" color="#FFFFFF" weight="bold" underline=false italic=false}

We want to filter the Test Effectiveness % based on the Fix Version selected from the drop down filter. It will be very helpful if you can let us know how to do that?

Currently the Test Effectiveness % is showing across all Fix Versions.

Regards,
Sankalan Banerjee

[tims (Tim Schnell)]

Hi @Sankalan_Banerjee,

What happens when you select a specific defect_fix_version in the dropdown? Does the Test Effectiveness % continue to show across all versions? Or does it go to 0? Can you also provide a few examples of what a defect_fix_version looks like?

[Sankalan_Banerjee]

@tims

at a defect_fix_version looks like?

When I select any specific value i.e. defect_fix_version from the drop down filter, it shows an Alert Sign with Error as : "Whoops! Expression failed
Expression failed with the message:
[mapColumn] > Cannot read property 'defect_component' of undefined"

Expression used:

filters

| essql
"SELECT defect_component, COUNT(defect_jira_id) AS count FROM \"project_defect_list\" GROUP BY defect_component"
| mapColumn "defect_component"
fn={if {getCell "defect_component" | eq "Exists in Prod"} then={getCell "defect_component"} else="Other"}
| ply by="defect_component" fn={math "sum(count)" | as "count"}
| staticColumn "total" value={math "sum(count)"}
| mapColumn "percentage" fn={math "floor(count/total * 100, 0)"}
| filterrows {getCell "defect_component" | any {eq "Other"}}
| markdown {getCell "percentage"} "%"
font={font family="'Open Sans', Helvetica, Arial, sans-serif" size=30 align="center" color="#FFFFFF" weight="bold" underline=false italic=false}

Let me know if you need any more details.

Regards,
Sankalan Banerjee

[tims (Tim Schnell)]

@Sankalan_Banerjee It looks like you are re-mapping the values of defect_component to be either "Exists in Prod" or "Other" so I would imagine when the filter tries to apply a value that isn't one of those 2 things it would break. Is that what is happening?