topic Drilldown and hidden column in line chart in Dashboards & Visualizations

Drilldown and hidden column in line chart

pduflot — Tue, 29 Sep 2020 07:58:36 GMT

Hello,

I am trying to make a drilldown from a hidden column in a line chart.
If I hide the columns (with <option name="charting.data.fieldShowList">["MaxError"]</option>), the tokens are not replaced by its value in the target URL.
If I don't hide the columns, the drilldown works fine, but the chart is unreadable because the Y scale is adapted to the START_TIME/END_TIME columns.

Is there a way to pass value of hidden columns into drilldown links?

Here is my code so far...

  <chart depends="$favg_tok$">
    <title>Evolution of average normalized error of $measurement_label$ by field.</title>
    <search>
      <query>index="sca_rs_index2" sourcetype=recordspecif | stats avg(ERR_DOSE_PRIM) AS FAVG_ERR_DOSE_PRIM,  earliest(_time) AS START_TIME, latest(_time) AS END_TIME by FIELD_ID | sort START_TIME | streamstats COUNT(FIELD_ID) AS INDEX | chart max(FAVG_N$favg_tok$) AS MaxError, first(FIELD_ID) AS FIELD_ID, min(START_TIME) AS START_TIME, max(END_TIME) AS END_TIME over INDEX</query>
    </search>
    <option name="charting.chart">line</option>
    <option name="charting.axisY2.enabled">false</option>
    <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
    <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
    <option name="charting.axisTitleX.visibility">visible</option>
    <option name="charting.axisTitleY.visibility">visible</option>
    <option name="charting.axisTitleY2.visibility">collapsed</option>
    <option name="charting.axisLabelsX.minorTickVisibility">show</option>
    <option name="charting.axisX.scale">linear</option>
    <option name="charting.axisY.scale">linear</option>
    <option name="charting.axisY2.scale">inherit</option>
    <option name="charting.chart.bubbleMaximumSize">50</option>
    <option name="charting.chart.bubbleMinimumSize">2</option>
    <option name="charting.chart.bubbleSizeBy">area</option>
    <option name="charting.chart.nullValueMode">gaps</option>
    <option name="charting.chart.sliceCollapsingThreshold">0.001</option>
    <option name="charting.chart.stackMode">default</option>
    <option name="charting.chart.style">shiny</option>
    <option name="charting.drilldown">all</option>
    <option name="charting.layout.splitSeries">0</option>
    <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
    <option name="charting.legend.placement">right</option>
    <option name="charting.data.count">1000</option>
    <option name="charting.data.fieldShowList">["MaxError"]</option>
    <option name="charting.axisTitleX.text">Field index</option>
    <option name="charting.axisTitleY.text">$measurement_label$</option>
    <drilldown target="New Window">
      <link><![CDATA[/app/new_sc_pdu/sc_field_analysis?form.field_id=$row.FIELD_ID$&form.lavg_tok=$favg_tok$&earliest=$row.START_TIME$&latest=$row.END_TIME$]]></link>
    </drilldown>
  </chart>

Thanks!

Re: Drilldown and hidden column in line chart

jeffland — Mon, 30 Nov 2015 15:07:29 GMT

Use the <fields> option to determine which fields are displayed; they should preserve your data for drilldown.

Re: Drilldown and hidden column in line chart

pduflot — Mon, 11 Jan 2016 14:39:38 GMT

I worked around this problem by sending all 'hidden data' to the y2 axis and by setting a range of [0-0] to the y2 axis.

"charting.axisY2.enabled": "true",
"charting.axisLabelsY2.axisVisibility": "hide",
"charting.axisY2.fields": "FIELD_ID,FRACTION_ID,INDEX,START_TIME,END_TIME",
"charting.axisY2.minimumNumber": "0",
"charting.axisY2.maximumNumber": "0",
"charting.axisTitleY2.visibility": "collapsed",

Re: Drilldown and hidden column in line chart

pduflot — Mon, 11 Jan 2016 14:40:16 GMT

According to the docs the fields options only applies to table not to chart. Anyway, I could not make it work.

Re: Drilldown and hidden column in line chart

jeffland — Tue, 12 Jan 2016 08:45:22 GMT

Yes, you're right - sorry about that. I'm moving my answer to a comment so others see this question unanswered.
PS: just noticed you already have a solution. You should accept it!

Re: Drilldown and hidden column in line chart

altink — Mon, 21 Aug 2017 19:23:40 GMT

thank you very much for posting this.

I do have the same problem, tried your settings but didn't work.

Can you please paste the whole Chart code here ?
or at least all the necessary

<option name="......

because I guess you did at least remove the

 <option name="charting.data.fieldShowList">["MaxError"]</option>

option because of drill-down invalidity; and may be others too

thank you very much
best regards
Altin

Re: Drilldown and hidden column in line chart

pduflot — Tue, 29 Sep 2020 15:24:39 GMT

Here is the full list of option:

"charting.layout.splitSeries": "0",
"charting.axisLabelsY2.axisVisibility": "hide",
"charting.chart": "line",
"charting.axisY2.fields": "FRACTION_ID,FIELD_ID,START_TIME,END_TIME",
"charting.axisLabelsX.minorTickVisibility": "show",
"charting.chart.style": "shiny",
"charting.chart.bubbleSizeBy": "area",
"charting.axisY2.minimumNumber": "0",
"charting.axisTitleX.text": "Range",
"resizable": true,
"charting.axisTitleY2.visibility": "collapsed",
"charting.chart.nullValueMode": "gaps",
"charting.chart.bubbleMinimumSize": "2",
"charting.chart.sliceCollapsingThreshold": "0.001",
"charting.axisY2.enabled": "true",
"charting.axisTitleX.visibility": "visible",
"charting.legend.labelStyle.overflowMode": "ellipsisMiddle",
"charting.chart.stackMode": "default",
"charting.axisY2.scale": "inherit",
"charting.axisLabelsX.majorLabelStyle.overflowMode": "ellipsisNone",
"charting.chart.bubbleMaximumSize": "50",
"charting.axisY2.maximumNumber": "0",
"charting.drilldown": "all",
"charting.axisTitleY.text": "$measurement_label$",
"charting.axisX.scale": "linear",
"charting.data.count": "1000",
"charting.axisLabelsX.majorLabelStyle.rotation": "90",
"charting.axisTitleY.visibility": "visible",
"charting.axisY.scale": "linear",
"charting.legend.placement": "right",

The above code uses HTML syntax.

Re: Drilldown and hidden column in line chart

altink — Tue, 22 Aug 2017 12:07:00 GMT

Thank you very much for your reply Pduflot

I see above you say that code above uses HTML syntax.

I have tried your way in Simple XML (my project is limited there - no Javascript/CSS). And furthermore it is a Column chart.

I tried to set the same of what you suggested in XML, I added the y2Axis. SCAN_ID is the sole field/column I want to hide in legend (it produces nothing)

 <chart>
        <search>
          <query>`mc_vln` 
| search DB_NAME=$field_db_name$          
| stats 
count(eval(CTRL_FIND_ID=-1)) as Broken,
count(eval(CTRL_FIND_ID=0)) as Correct,
count(eval(CTRL_FIND_ID=1)) as Finding,
earliest(_time) as SCAN_DT_START 
by DB_NAME SCAN_ID
| sort - SCAN_DT_START 
| table DB_NAME SCAN_ID SCAN_DT_START Broken Correct Finding 
| dedup $field_last_x_scan$ DB_NAME 
| head 15
| sort SCAN_DT_START
| fields - SCAN_DT_START
| rename DB_NAME as Database</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>

        <option name="charting.chart">column</option>

        <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
        <option name="charting.axisTitleX.visibility">collapsed</option>

        <option name="charting.axisY2.enabled">false</option>
        <option name="charting.axisLabelsY2.axisVisibility">hide</option>
        <option name="charting.axisY2.fields">"SCAN_ID"</option>
        <option name="charting.axisY2.minimumNumber">0</option>
        <option name="charting.axisY2.maximumNumber">0</option>
        <option name="charting.axisTitleY2.visibility">collapsed</option>
        <option name="charting.axisY2.scale">inherit</option>

        <option name="charting.chart.stackMode">default</option>
        <option name="charting.legend.labels">["Finding","Correct","Broken"]</option>
        <option name="charting.legend.placement">top</option>
        <option name="charting.seriesColors">[0xFA4343,0x00579F,0x646464]</option>
        <option name="charting.legend.masterLegend"></option>
        <drilldown target="blank">
          <link>scan_data?form.field_db_name=$click.value$&amp;form.field_scan_id=$row.SCAN_ID$&amp;form.field_ctrl_find_name=$click.name2$</link>
        </drilldown>
      </chart>

but I does not work, I still see the SCAN_ID field (the only needed to hide) appear in legend menu

Is your solution valid for simple XML, or am I missing something in my code?
Can you please advise at this point?

thanks and best regards
Altin