topic Re: Color a row based on a column value in Dashboards & Visualizations

Color a row based on a column value

sweety1309 — Tue, 09 Feb 2021 12:20:21 GMT

Hey I have the query-

index="classroom-students" profile.emailAddress="something" earliest=0 latest=now
| join type=outer courseId
[search index="courses" courseState="ACTIVE" ]
| join type=outer courseId max=0
[search index="course-work" | rename id as courseWorkId ]
| join type=outer userId, courseId, courseWorkId
[search index="students-submission" | dedup userId, courseId, courseWorkId sortby -updateTime]
| rename profile.name.fullName as StudentName
| rename name as Classroom
| rename submissionHistory{}.gradeHistory.pointsEarned
as pointsEarned
| table StudentName, courseId, courseWorkId, userId, Classroom, descriptionHeading, title, workType, maxPoints, pointsEarned, state, late.

In late column,i get "true" in some rows.I want to color those rows where I am getting "true" in late column.Like if u look at the imagfe,I want to color the whole second row.

Any help would be highlt appreciated.Thanks in advance.

Re: Color a row based on a column value

ITWhisperer — Tue, 09 Feb 2021 13:16:51 GMT

Try something like this

<format type="color"> <colorPalette type="expression">if(match(value,"true"),"#53A051",null)</colorPalette> </format>

Re: Color a row based on a column value

sweety1309 — Tue, 09 Feb 2021 15:48:42 GMT

Thanks for ur response.I want that whole row to get coloured which contains true value.

Re: Color a row based on a column value

ITWhisperer — Tue, 09 Feb 2021 17:00:50 GMT

Sorry I missed a couple of steps

You need a hidden panel with some style

<panel depends="$stayhidden$"> <title>Only SimpleXML no JS required</title> <html> <style> #tableRowColor table tbody td div.multivalue-subcell[data-mv-index="1"]{ display: none; } </style> </html> </panel>

You need to name your table with a matching id - if it already has an id, adjust the above style

Then you need to add this to the end of your query

| foreach * [| eval <<FIELD>>=mvappend('<<FIELD>>',late)]

This assumes you don't already have multi-value fields in your table. If you do, you could try changing the order of the mvappend to stick a copy of the late field at the front of mv field and change the index in the style to 0

Re: Color a row based on a column value

sweety1309 — Wed, 10 Feb 2021 02:48:40 GMT

Thanks a lot for ur quick response @ITWhisperer .

If u look at the image,true value is coming in all the columns now which I dont want.Can u please help me with this.

Thanks in advance

Re: Color a row based on a column value

ITWhisperer — Wed, 10 Feb 2021 07:18:29 GMT

The style hides the second element of the multivalue fields - the reason you have them showing is that you either haven't included that part of the solution or the table id does not match the name used in the style.

Re: Color a row based on a column value

sweety1309 — Wed, 10 Feb 2021 07:49:45 GMT

This is how I have written its query.Can u plz point out my mistake @ITWhisperer

<row>
<panel depends="$stayhidden$">
<title>Only SimpleXML no JS required</title>
<html>
<style>
#tableRowColor table tbody td div.multivalue-subcell[data-mv-index="1"]{
display: none;
}
</style>
</html>
</panel>
<panel>
<table id="tableRowColor">
<search>
<query>index="dpsn-classroom-students" profile.emailAddress="$email$" earliest=0 latest=now
| join type=outer courseId
[search index="dpsn-courses" courseState="ACTIVE" ]
| join type=outer courseId max=0
[search index="dpsn-course-work" | rename id as courseWorkId ]
| join type=outer userId, courseId, courseWorkId
[search index="dpsn-students-submission" | dedup userId, courseId, courseWorkId sortby -updateTime]
| rename profile.name.fullName as StudentName
| rename name as Classroom
| rename submissionHistory{}.gradeHistory.pointsEarned
as pointsEarned
| foreach * [| eval <<FIELD>>=mvappend('<<FIELD>>',late)]
| table StudentName, courseId, courseWorkId, userId, Classroom, descriptionHeading, title, workType, maxPoints, pointsEarned, state
</query>
<earliest>$time_tok.earliest$</earliest>
<latest>$time_tok.latest$</latest>
</search>
<option name="count">20</option>
<option name="drilldown">cell</option>
<option name="percentagesRow">false</option>
<option name="rowNumbers">true</option>
<option name="totalsRow">false</option>
<option name="wrap">false</option>
<format type="color">
<colorPalette type="expression">if(match(value,"true"),"#53A051",null)</colorPalette>
</format>
</table>
</panel>
</row>

Re: Color a row based on a column value

ITWhisperer — Wed, 10 Feb 2021 08:24:11 GMT

I can't see anything wrong with what you have posted except that it does not match the image, for example, late is not listed as a field in the table command. Having said that, this should not affect the solution as you have already copied it to the multi value fields. On that, I also noticed in the image that pointsEarned only has the value true in, so you might want to consider using fillnull to ensure all columns have a value before the foreach which is adding the true. Neither of these should prevent the solution from working. Can you check that the html tags in use match the values used in the style. You can do this by inspecting the page in the browser.

Re: Color a row based on a column value

sweety1309 — Wed, 10 Feb 2021 10:15:17 GMT

I have attached the screenshot on the inspect element.Please have a look @ITWhisperer

Re: Color a row based on a column value

ITWhisperer — Wed, 10 Feb 2021 13:57:40 GMT

Can you capture a bit further up to include the div id="tableRowColor" down to the td?

Re: Color a row based on a column value

ITWhisperer — Wed, 10 Feb 2021 14:04:34 GMT

You could also try giving the style a bit of priority - possibly needed if you have other styles applied?

Re: Color a row based on a column value

sweety1309 — Wed, 10 Feb 2021 16:20:38 GMT

Here is the screenshot @ITWhisperer

Re: Color a row based on a column value

ITWhisperer — Wed, 10 Feb 2021 17:26:26 GMT

Can you select the second multivalue element so we can see what style are being applied?

Re: Color a row based on a column value

sweety1309 — Wed, 10 Feb 2021 17:29:41 GMT

As you can see in the picture..Some td tags have two div containing true.If I remove the second true div,the work is done. @ITWhisperer

Re: Color a row based on a column value

ITWhisperer — Wed, 10 Feb 2021 17:43:41 GMT

I didn't see anything in your Simple XML that would add an additional element to the multi-value fields. Has the issue now been fixed?

Re: Color a row based on a column value

sweety1309 — Thu, 11 Feb 2021 05:49:03 GMT

No,I m facing the same issue @ITWhisperer

Re: Color a row based on a column value

ITWhisperer — Thu, 11 Feb 2021 07:33:55 GMT

Can you share a screenshot of the inspection with the second element (the one with data-mv-index="1") selected to show what styles are in operation, so we can track down why the element is still being shown?

Re: Color a row based on a column value

sweety1309 — Thu, 11 Feb 2021 08:34:29 GMT

Ya sure.Here is the screenshot @ITWhisperer

Re: Color a row based on a column value

ITWhisperer — Thu, 11 Feb 2021 08:42:29 GMT

You need to select the td two lines down

Re: Color a row based on a column value

sweety1309 — Thu, 11 Feb 2021 08:46:55 GMT

@ITWhisperer