Hello there,
I've generated a table with data as below showing the % of data computed for various type of products.
date_hour country product_A % product_B % product_C %
1 KR 25% 30% 45%
2 KR 50% 20% 30%
My requirement now is when user click on column "product_x %", the table should expand with more data on product_x, example as below:
date_hour country product_A % product_A_volume product_A_cust product_B % product_C %
1 KR 25% 3300 XYZ 30% 45%
2 KR 50% 3400 ABC 20% 30%
This is very much similiar to Excel grouping function whereby user can group/ungroup with what they would like to see.
How can I achieve this requirement?
Thanks.
@krusovice, try the following run anywhere dashboard based on sample data. The %
column that you click will add the volume
and cust
columns for that product.
<dashboard>
<label>Add Detail Columns to Table</label>
<init>
<set token="tokDetailFields">fields - product_A_* product_B_* product_C_*</set>
<set token="fieldNames">date_hour, country, "product_A %", "product_B %", "product_C %"</set>
</init>
<row>
<panel>
<table>
<search>
<query>| makeresults
| fields - _time
| eval data="1 KR 25% 3300 XYZ 30% 3300 XYZ 45% 3300 XYZ;2 KR 50% 3400 ABC 20% 3300 XYZ 30% 3300 XYZ;"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval date_hour=mvindex(data,0), country=mvindex(data,1), "product_A %"=mvindex(data,2), product_A_volume=mvindex(data,3), product_A_cust=mvindex(data,4), "product_B %"=mvindex(data,5), product_B_volume=mvindex(data,6), product_B_cust=mvindex(data,7), "product_C %"=mvindex(data,8),product_C_volume=mvindex(data,9), product_C_cust=mvindex(data,10)
| fields - data
| $tokDetailFields$
| table date_hour country "*"</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">20</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">cell</option>
<option name="percentagesRow">false</option>
<option name="refresh.display">progressbar</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
<drilldown>
<condition field="product_A %">
<set token="tokDetailFields">fields - product_B_* product_C_*</set>
</condition>
<condition field="product_B %">
<set token="tokDetailFields">fields - product_A_* product_C_*</set>
</condition>
<condition field="product_C %">
<set token="tokDetailFields">fields - product_A_* product_B_*</set>
</condition>
<condition>
<!-- DO not respond to click on other fields-->
</condition>
</drilldown>
</table>
</panel>
</row>
</dashboard>
@krusovice, try the following run anywhere dashboard based on sample data. The %
column that you click will add the volume
and cust
columns for that product.
<dashboard>
<label>Add Detail Columns to Table</label>
<init>
<set token="tokDetailFields">fields - product_A_* product_B_* product_C_*</set>
<set token="fieldNames">date_hour, country, "product_A %", "product_B %", "product_C %"</set>
</init>
<row>
<panel>
<table>
<search>
<query>| makeresults
| fields - _time
| eval data="1 KR 25% 3300 XYZ 30% 3300 XYZ 45% 3300 XYZ;2 KR 50% 3400 ABC 20% 3300 XYZ 30% 3300 XYZ;"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval date_hour=mvindex(data,0), country=mvindex(data,1), "product_A %"=mvindex(data,2), product_A_volume=mvindex(data,3), product_A_cust=mvindex(data,4), "product_B %"=mvindex(data,5), product_B_volume=mvindex(data,6), product_B_cust=mvindex(data,7), "product_C %"=mvindex(data,8),product_C_volume=mvindex(data,9), product_C_cust=mvindex(data,10)
| fields - data
| $tokDetailFields$
| table date_hour country "*"</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">20</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">cell</option>
<option name="percentagesRow">false</option>
<option name="refresh.display">progressbar</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
<drilldown>
<condition field="product_A %">
<set token="tokDetailFields">fields - product_B_* product_C_*</set>
</condition>
<condition field="product_B %">
<set token="tokDetailFields">fields - product_A_* product_C_*</set>
</condition>
<condition field="product_C %">
<set token="tokDetailFields">fields - product_A_* product_B_*</set>
</condition>
<condition>
<!-- DO not respond to click on other fields-->
</condition>
</drilldown>
</table>
</panel>
</row>
</dashboard>
Hi @niketnilay,
Thanks for the great help, the drilldown works perfectly for me. However, by using "fields" (I even tried changing to "table"), the column seem to be sorted as alphabetical order. Is that a way to "un-sort" it?
Thanks.
@krusovice are the details for drilldown already available from your existing query/existing data or is a new lookup/join is required to pull it from somewhere else?
hi @niketnilay,
It is already available from the existing query. At first I have shown everything in one table, but due to too many column, user would like to see whether can hide/unhide the column based on what they want to view.
Thanks.