How to use advanced xml to color a “bar” in a bar chart with diff colors based upon a field value

[akhil4mdev]

Hello
I have a reportcreatedate for a report

Imagine I have 5 report instances of a report named A in one day

| timechart dc(reportcreateddate)

_time. Dc(reportcreateddate)

05-10. 5
05-11. 10
05-12 3

I am getting a time chart

So each report instance has a size

It means on 5-10 we have 5 reports with diff sizes
5.6kb,7kb,4kb,10kb,15kb

So I decided a threshold reports under 6kb are empty

So I want a time chart in a way that
It shows count of reports over time

And I want the chart bars to be covered by percentage of good reports that r present on that day for example

On 5-11 we have 10 reports if 5 of them r below 6kb and remaining five or above 6kb

So in the time chart

The bar of that day should be fifty percent green and fifty percent red

Is this possible ? How ? What will be the query

Reportcreateddate
Reportsize
_time are the required fields

Thank you

[niketn · Accepted solution]

@akhil4mdev, please try the following run aywhere dashboard based on details and sample data provided. While I dont have TIme field and its value based on the sample data following is the query based on Time field between 1-24

Following is the Simple XML code as per the samepl data provided. With Tabular Details and Chart representation of Good% and Bad %:

<dashboard>
  <label>Report with Good and Bad size</label>
  <row>
    <panel>
      <title>Tabular details</title>
      <table>
        <search>
          <query>|  makeresults
|  eval Time=5, Reportsize=5.4,Sizeunit="Kb"
|  append 
    [|  makeresults
|  eval Time=6, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=7, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=8, Reportsize=6.5,Sizeunit="Kb"]
|  append 
    [|  makeresults
|  eval Time=11, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=13, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=15, Reportsize=6.5,Sizeunit="Kb"]
|  eval Reportsize=case(Sizeunit="B",round(Reportsize/1024,1),true(),Reportsize)
|  eval TimeWindow=case(Time>=5 AND Time<=10,"05-10",Time>=11 AND Time<=15,"11-15",true(),"Other")
|  dedup Reportsize TimeWindow
|  stats count(Reportsize) as uniqueReportSize count(eval(Reportsize>6)) as "Good" count(eval(Reportsize<6)) as "Bad" values(Reportsize) as "Reportsizes (in KB)" by TimeWindow
|  eval Good=round((Good/uniqueReportSize)*100,1)." %",Bad=round((Bad/uniqueReportSize)*100,1)." %"</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">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="Bad">
          <colorPalette type="expression">case(true(), "#DC4E41")</colorPalette>
        </format>
        <format type="color" field="Good">
          <colorPalette type="expression">case(true(), "#53A051")</colorPalette>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Chart</title>
      <chart>
        <search>
          <query>|  makeresults
|  eval Time=5, Reportsize=5.4,Sizeunit="Kb"
|  append 
    [|  makeresults
|  eval Time=6, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=7, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=8, Reportsize=6.5,Sizeunit="Kb"]
|  append 
    [|  makeresults
|  eval Time=11, Reportsize=4412,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=13, Reportsize=7321,Sizeunit="B"]
|  append 
    [|  makeresults
|  eval Time=15, Reportsize=6.5,Sizeunit="Kb"]
|  eval Reportsize=case(Sizeunit="B",round(Reportsize/1024,1),true(),Reportsize)
|  eval TimeWindow=case(Time>=5 AND Time<=10,"05-10",Time>=11 AND Time<=15,"11-15",true(),"Other")
|  dedup Reportsize TimeWindow
|  chart count(Reportsize) as uniqueReportSize count(eval(Reportsize>6)) as "Good" count(eval(Reportsize<6)) as "Bad" values(Reportsize) as "Reportsizes (in KB)" by TimeWindow
|  eval Good=round((Good/uniqueReportSize)*100,1),Bad=round((Bad/uniqueReportSize)*100,1)
| table TimeWindow Good Bad</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">column</option>
        <option name="charting.chart.showDataLabels">all</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.fieldColors">{"Bad":"0xDC4E41","Good":"0x53A051"}</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
  </row>
</dashboard>

[niketn]

@akhil4mdev what is the name of size field? Also is its value just 4.5,5.4 or does it have kb in it like in your example 4.5kb and 5.4kb?

[ramarcsight]

It have kb , bytes also but I think first I need to convert them all to KB
Could you please help me in that also

Reportsize=5.6
Sizeunit=Kib

Reportsize=118
Sizeunit=B which means bytes

First I need to convert all of them to Kib then do the coloring

[ramarcsight]

Both r separate field names

Reportsize
Sizeunit