{"id":11511,"date":"2022-10-13T09:45:38","date_gmt":"2022-10-13T08:45:38","guid":{"rendered":"https:\/\/help.instantatlas.com\/report-builder\/?page_id=11511"},"modified":"2023-07-20T15:48:55","modified_gmt":"2023-07-20T14:48:55","slug":"table-cell-color-rules-rb-2-5","status":"publish","type":"page","link":"https:\/\/help.reports.esriuk.com\/report-builder\/table-cell-color-rules-rb-2-5\/","title":{"rendered":"Table Cell Color Rules"},"content":{"rendered":"<p>To improve the visualization of data within table widgets, different cell shading can be applied based on the data value. For example, cells with values above 50 could be shaded red and cells with values below 50 in green. This is achieved using settings located in the <strong>Settings<\/strong> tab under the <strong>Colors &amp; Highlights<\/strong> heading.<\/p>\n<h4>Cell color\u00a0rules<\/h4>\n<p>A set of space-delimited rules where each rule has the form <code><strong>&lt;test&gt;(&lt;value&gt;, &lt;style-if-match&gt;, &lt;style-if-not-match&gt;?,&lt;indicator-key&gt;?)<\/strong><\/code>, for example <code>lessthan(100, #ccffcc, #ffaaaa)<\/code>.<\/p>\n<p><strong><em>&lt;test&gt;<\/em><\/strong> can be simple and apply to either text or numeric values &#8211; one of:<\/p>\n<ul style=\"list-style-type: none;\">\n<li><code>equals<\/code><\/li>\n<li><code>notequal<\/code><\/li>\n<\/ul>\n<p>or <strong><em>&lt;test&gt;<\/em><\/strong> can be mathematical:<\/p>\n<ul style=\"list-style-type: none;\">\n<li><code>greaterthan<\/code><\/li>\n<li><code>greaterthanorequal<\/code><\/li>\n<li><code>lessthan<\/code><\/li>\n<li><code>lessthanorequal<\/code><\/li>\n<li><\/li>\n<\/ul>\n<p>(Advanced users can use the shorter mathematical symbols (<code>==<\/code>, <code>!=<\/code>, <code>&gt;<\/code>, <code>&gt;=<\/code>, <code>&lt;<\/code>, <code>&lt;=<\/code>) instead of the keywords above but this does make your rules harder to read!)<\/p>\n<p>Or <strong><em>&lt;test&gt;<\/em><\/strong> can be a list or text-based comparator:<\/p>\n<ul style=\"list-style-type: none;\">\n<li><code>contains<\/code><\/li>\n<li><code>doesnotcontain<\/code><\/li>\n<li><code>in<\/code><\/li>\n<li><code>notin<\/code><\/li>\n<li><\/li>\n<\/ul>\n<p><em><strong>&lt;value&gt;<\/strong><\/em> is the value to test against &#8211; either a simple value (number or text), for example <code>99<\/code>, or a dynamic value from the same dataset using the syntax <code>#VALUE{&lt;feature-id&gt;}<\/code> (see examples below).<\/p>\n<p><strong><em>&lt;style-&gt;<\/em> <\/strong>is either of form:<\/p>\n<p><code><em>&lt;color&gt;;&lt;font-weight&gt;;&lt;font-style&gt;;&lt;font-size&gt;<\/em><\/code><\/p>\n<p>or<\/p>\n<p><code><em>class:&lt;css-class-name&gt;<\/em><\/code><\/p>\n<p>The <code>class:&lt;css-class-name&gt;<\/code> syntax gives more control over the final output but is more complex and requires the use of <a href=\"https:\/\/help.instantatlas.com\/report-builder\/styling-menu\/\">advanced styling<\/a>.<\/p>\n<p><em><strong>&lt;style-if-match&gt;<\/strong><\/em> is required and is the style that will be applied when the value in a given table cell matches or &#8220;passes&#8221; the test.<\/p>\n<p><em><strong>&lt;style-if-not-match&gt;<\/strong><\/em> is optional and is the style that will be applied when the value in a given table cell does not match or &#8220;fails&#8221; the test. If it is not specified no extra styling will be applied to the cell.<\/p>\n<p><em><strong>&lt;indicator-key&gt;<\/strong><\/em> is optional and restricts the rule to cells that contain values for the given indicator ID (you can get this ID from the metadata in the <a href=\"https:\/\/help.instantatlas.com\/report-builder\/widgets\/widget-data\/\">data tab<\/a>). If it is not specified the rule will be evaluated for every table cell\/value. Note &#8211; to use <em>&lt;indicator-key&gt;<\/em> <span style=\"text-decoration: underline;\">without<\/span> a <em>&lt;style-if-not-match&gt;<\/em> use two commas in the rule, for example <code>greaterthan(90, #ffaaaa,, I2776)<\/code>.<\/p>\n<h4><strong>Cell rules color style<\/strong><\/h4>\n<p>This can be <code>background<\/code> or <code>text.<\/code> If <code>text,<\/code> then the above color rules will apply to text color. \u00a0If <code>background,<\/code> the background of the cell will be shaded instead.<\/p>\n<h3>Examples<\/h3>\n<p>To shade cells red where the percentage of benefit claimants is above 50 can be achieved by giving <b>Cell color rules<\/b> a value of <code>greaterthan(50, #ff0000)<\/code>.\u00a0 Note that there are many web pages that list color codes, such as <a href=\"http:\/\/www.w3schools.com\/colors\/colors_picker.asp\">http:\/\/www.w3schools.com\/colors\/colors_picker.asp<\/a>.<\/p>\n<p><a href=\"https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2023\/07\/rb-2-6-cell-color-rules.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11785\" src=\"https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2023\/07\/rb-2-6-cell-color-rules.png\" alt=\"Cell colour rules setting\" width=\"350\" height=\"204\" srcset=\"https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2023\/07\/rb-2-6-cell-color-rules.png 350w, https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2023\/07\/rb-2-6-cell-color-rules-300x175.png 300w\" sizes=\"auto, (max-width: 350px) 100vw, 350px\" \/><\/a><\/p>\n<p>This is the resulting table.<\/p>\n<p><a href=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11518\" src=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic2.png\" alt=\"The output table from the cell colour rules.\" width=\"601\" height=\"181\" srcset=\"https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic2.png 601w, https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic2-300x90.png 300w\" sizes=\"auto, (max-width: 601px) 100vw, 601px\" \/><\/a><\/p>\n<p>To display cells that have values above 50 shaded red and those with values equal to or less than 50 shaded green, the setting rule would be <code>greaterthan(50 #ff0000,#00ff00)<\/code>.<\/p>\n<p><a href=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic3.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11519\" src=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic3.png\" alt=\"The table with values greater than 50 shaded red, and below shaded green\" width=\"601\" height=\"179\" srcset=\"https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic3.png 601w, https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic3-300x89.png 300w\" sizes=\"auto, (max-width: 601px) 100vw, 601px\" \/><\/a><\/p>\n<p>To assign more than two colours e.g. cells with values above 50 shaded red, cells with values between 50 and 20 amber and those with values equal to or less than 20 shaded green, the setting rule would be <code>greaterthan(50, #ff0000) lessthanorequal(20, #00ff00) lessthan(50, #ffbf00)<\/code>.<\/p>\n<p><a href=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic4.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11520\" src=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic4.png\" alt=\"Third table with three colours: red, amber and green\" width=\"602\" height=\"179\" srcset=\"https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic4.png 602w, https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic4-300x89.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/a><\/p>\n<p>Please note that the order of the rules is important. The string is read from left to right and as soon as the table widget finds a rule that is true, it applies the respective style and then ignores all further rules. So giving the Rule a value of <code>greaterthan(50, #ff0000) lessthanorequal(50, #ffbf00) lessthanorequal(20, #00ff00)<\/code> will not shade any cells green.<\/p>\n<p>To shade the values rather than the cells, change the <strong>Cell rules color style<\/strong> to <code>text<\/code>. The table widget below is using a <strong>Cell color rules<\/strong> value of <code>greaterthan(50, #ff0000) lessthanorequal(50, #00ff00)<\/code>.<\/p>\n<p><a href=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic5.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11521\" src=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic5.png\" alt=\"Table with the text colour highlighted rather than the background\" width=\"602\" height=\"179\" srcset=\"https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic5.png 602w, https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic5-300x89.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/a><\/p>\n<p>To display values above 50 as red, bold, italic and 1.2 times the base font size and those equal to or less than 50 to be green and bold give the <b>Cell color rules<\/b> a value of <code>greaterthan(50, #ff0000;bold;italic;1.2em) lessthanorequal(50, #00ff00;bold)<\/code>.<\/p>\n<p><a href=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic6.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11522\" src=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic6.png\" alt=\"The text made bold and italic\" width=\"602\" height=\"181\" srcset=\"https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic6.png 602w, https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic6-300x90.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/a><\/p>\n<p>Note that 1em is equal to the current font size. \u00a02em means 2 times the size of the current font.\u00a0 The &#8217;em&#8217; is a very useful unit in CSS, since it can adapt automatically to the font that the reader uses.<\/p>\n<p><strong>Comparison to other feature values<\/strong><\/p>\n<p>Rules can use dynamic values in tests &#8211; an obvious example is to compare cell values with a comparison feature&#8217;s value e.g. comparing values with that of another region:<\/p>\n<p><code>greaterthan(#VALUE{C:E0200001}, #ffcccc, #ccffcc)<\/code><\/p>\n<p>Note: the ID (E0200001) is obtained from the data table in ArcGIS Online.<\/p>\n<p>The above rule will shade cells red if the cell value is greater than the value for the comparison feature with ID E0200001 and green if not. The syntax is similar to <a href=\"https:\/\/help.instantatlas.com\/report-builder\/variables\/\">text widget variables<\/a> but only acts on the <em>current indicator<\/em>.<\/p>\n<p><a href=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic7.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-11524\" src=\"https:\/\/help.instantatlas.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic7-1024x306.png\" alt=\"Comparison table of the cell to another feature value\" width=\"660\" height=\"197\" srcset=\"https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic7-1024x306.png 1024w, https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic7-300x90.png 300w, https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic7-768x229.png 768w, https:\/\/help.reports.esriuk.com\/report-builder\/wp-content\/uploads\/sites\/51\/2022\/10\/pic7.png 1379w\" sizes=\"auto, (max-width: 660px) 100vw, 660px\" \/><\/a><\/p>\n<p>As well as comparing cells to other feature values, cells can be compared to the currently selected feature:<\/p>\n<p><code>greaterthan(#FVALUE, #ffcccc)<\/code><\/p>\n<p>This will turn cells red whose value is greater than the value for the currently selected feature.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>To improve the visualization of data within table widgets, different cell shading can be applied based on the data value. For example, cells with values above 50 could be shaded red and cells with values below 50 in green. This is achieved using settings located in the Settings tab under the Colors &amp; Highlights heading. [&hellip;]<\/p>\n","protected":false},"author":61,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-11511","page","type-page","status-publish","hentry","entry"],"_links":{"self":[{"href":"https:\/\/help.reports.esriuk.com\/report-builder\/wp-json\/wp\/v2\/pages\/11511","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/help.reports.esriuk.com\/report-builder\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/help.reports.esriuk.com\/report-builder\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/help.reports.esriuk.com\/report-builder\/wp-json\/wp\/v2\/users\/61"}],"replies":[{"embeddable":true,"href":"https:\/\/help.reports.esriuk.com\/report-builder\/wp-json\/wp\/v2\/comments?post=11511"}],"version-history":[{"count":0,"href":"https:\/\/help.reports.esriuk.com\/report-builder\/wp-json\/wp\/v2\/pages\/11511\/revisions"}],"wp:attachment":[{"href":"https:\/\/help.reports.esriuk.com\/report-builder\/wp-json\/wp\/v2\/media?parent=11511"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}