How to expand table based on click to the column

[krusovice]

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.

[niketn · Accepted solution]

@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]

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.

[niketn]

@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?

[krusovice]

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.