Colour Rows Based on Greater than or Less than Ranges

Tips submitted by ASP.NET Maker users

Colour Rows Based on Greater than or Less than Ranges

Postby xgis » Sun Aug 11, 2013 4:17 pm

In some cases you may need to colour fields based on a range of selections.
The example below uses colours to differentiate levels of risk using decimal / float values.
For discrete values (ie integers) you could use "=" and change Convert.ToDouble to Convert.ToInt32
The control example in this case is a drop down with stored values ranging from 1-5.
The colour range is amber to red as any decision indicates risk, whereas no selection (null) indicates no risk.

The code; (replace "EnvERRSoilContVisAssID" with your field name)

if (EnvERRSoilContVisAssID.CurrentValue is System.DBNull) {
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #FF8C69"; }
else if (Convert.ToDouble(EnvERRSoilContVisAssID.CurrentValue) <= 1.5 ){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #FFD966"; }
else if (Convert.ToDouble(EnvERRSoilContVisAssID.CurrentValue) <= 2.5 ){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F9C475"; }
else if (Convert.ToDouble(EnvERRSoilContVisAssID.CurrentValue) <= 3.5 ){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F4B084"; }
else if (Convert.ToDouble(EnvERRSoilContVisAssID.CurrentValue) <= 4.5 ){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F68C77"; }
else if (Convert.ToDouble(EnvERRSoilContVisAssID.CurrentValue) > 4.5 ){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F8696B"; }
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm

Re: Colour Rows Based on Greater than or Less than Ranges

Postby xgis » Sun Aug 11, 2013 4:54 pm

Coding for Discrete values (Integers)

Two changes to the first sample are given below;
1. Usually I use an Amber colour to represent a field where a decision needs to be made, In this example Amber is already used.
I have simply changed NULL values to SlateGray to make them standout in this example. eg #C6E2FF
2. Ignore the line "For discrete values (ie integers) you could use "=" and change Convert.ToDouble to Convert.ToInt32"
in the previous post. The following code for integers is based on the example in ANM Custom Scripts.
The following code works for integers;

The code; (replace "EnvERRSoilContVisAssID" with your field name)

//1. EnvERRSoilContVisAssID
if (EnvERRSoilContVisAssID.CurrentValue is System.DBNull) {
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #C6E2FF"; }
else if (ew_SameInt(EnvERRSoilContVisAssID.CurrentValue, 1)){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #FFD966"; }
else if (ew_SameInt(EnvERRSoilContVisAssID.CurrentValue, 2)){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F9C475"; }
else if (ew_SameInt(EnvERRSoilContVisAssID.CurrentValue, 3)){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F4B084"; }
else if (ew_SameInt(EnvERRSoilContVisAssID.CurrentValue, 4)){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F68C77"; }
else if (ew_SameInt(EnvERRSoilContVisAssID.CurrentValue, 5)){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F8696B"; }
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm

Re: Heat Map Cells Based on Greater than or Less than Values

Postby xgis » Mon Dec 16, 2013 12:05 pm

This is an alternate implementation of the code that is designed for data within one year of an event occurring.
C# often has issues with the way the code is presented when operators are used e.g. (> or <).
The use of the int variable "Days", also reduces verbose and repetitive coding.

The code itself changes the 'CellCssStyle ' to display more intense colours (e.g. Darker RED) as the event draws nearer.
The codes below are used on an MS SQL View (aka query) but are suitable for tables also.
This method is designed to be applicable to multiple views or tables with minimal name changes.
To use, simply replace "DaysTillWedding" with your field name representing your integer field type.

The codes used in the 'Row_Rendered' event;

int Days = Convert.ToInt32(DaysTillWedding.ViewValue); //Render Decision Variable as an Integer

if (DaysTillWedding.CurrentValue is System.DBNull) {
DaysTillWedding.CellCssStyle = "background-color: #FFE680"; } //Pastel Heat Map Colours Below
else if ((int)Days < 3) {
DaysTillWedding.CellCssStyle = "background-color: #C96A67"; } //DkRed (3 Days)
else if ((int)Days < 7) {
DaysTillWedding.CellCssStyle = "background-color: #CE7876"; } //Red (1 Week)
else if ((int)Days < 14) {
DaysTillWedding.CellCssStyle = "background-color: #D99795"; } //LightRed (2 Weeks)
else if ((int)Days < 21) {
DaysTillWedding.CellCssStyle = "background-color: #EAC6C5"; } //LighterRed (3 Weeks)
else if ((int)Days < 30) {
DaysTillWedding.CellCssStyle = "background-color: #75923C"; } //DkGreen (1 Months)
else if ((int)Days < 60) {
DaysTillWedding.CellCssStyle = "background-color: #75923C"; } //Green (2 Months)
else if ((int)Days < 180) {
DaysTillWedding.CellCssStyle = "background-color: #C1CEA7"; } //LightGreen (6 Months)
else if ((int)Days < 365) {
DaysTillWedding.CellCssStyle = "background-color: #ECF0E4"; } //LighYellowGreen (12 Months)
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm

Re: Colour Rows Based on Greater than or Less than Ranges

Postby xgis » Sun Feb 26, 2017 8:01 pm

The sample below is an ANM 12/2017 Row Rendered sample that uses hex gradients.
The method used subtracts dates then converts it to integers.

//DATE DIFFERENCE CELL BACKGROUND GRADIENT COLOURS (MOST RECENT EDITS)
//Blue Colour Scheme Gradient hex 172459 14 steps perbang.dk/rgbgradient

DateTime DateLastEdited = Convert.ToDateTime(SiteEditedDateLast.ViewValue);
DateTime DateToday = Convert.ToDateTime(DateTime.Today);
var DateDifferenceDays = Convert.ToInt32((DateToday - DateLastEdited).TotalDays);

if (DateDifferenceDays <1){
SiteEditedDateLast.CellCssStyle = "background-color: #172459; color: #FFFFFF";} //contrasts cell text with white on dark blue background
else if ((DateDifferenceDays >= 1) & (DateDifferenceDays <= 3)){
SiteEditedDateLast.CellCssStyle = "background-color: #172459; color: #E5E5E5";}
else if ((DateDifferenceDays > 3) & (DateDifferenceDays <= 5)){
SiteEditedDateLast.CellCssStyle = "background-color: #283465";}
else if ((DateDifferenceDays > 5) & (DateDifferenceDays <= 7)){
SiteEditedDateLast.CellCssStyle = "background-color: #3a4572";}
else if ((DateDifferenceDays > 7) & (DateDifferenceDays <= 14)){
SiteEditedDateLast.CellCssStyle = "background-color:#4c567f";}
else if ((DateDifferenceDays > 14) & (DateDifferenceDays <= 30)){
SiteEditedDateLast.CellCssStyle = "background-color: #5e678c";}
else if ((DateDifferenceDays > 30) & (DateDifferenceDays <= 60)){
SiteEditedDateLast.CellCssStyle = "background-color: #707898";}
else if ((DateDifferenceDays > 60) & (DateDifferenceDays <= 90)){
SiteEditedDateLast.CellCssStyle = "background-color: #8289a5";}
else if ((DateDifferenceDays > 90) & (DateDifferenceDays <= 180)){
SiteEditedDateLast.CellCssStyle = "background-color: #9399b2";}
else if ((DateDifferenceDays > 180) & (DateDifferenceDays <= 365)){
SiteEditedDateLast.CellCssStyle = "background-color: #a5aabf";}
else if ((DateDifferenceDays > 365) & (DateDifferenceDays <= 730)){
SiteEditedDateLast.CellCssStyle = "background-color: #b7bbcb";}
else if ((DateDifferenceDays > 730) & (DateDifferenceDays <= 1825)){
SiteEditedDateLast.CellCssStyle = "background-color: #c9ccd8";}
else if ((DateDifferenceDays > 1825) & (DateDifferenceDays <= 3650)){
SiteEditedDateLast.CellCssStyle = "background-color: #dbdde5";}
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm

Re: Colour Rows Based on Greater than or Less than Ranges

Postby xgis » Sun Mar 19, 2017 5:35 pm

// How to Deal with the Date fields that have NULL values
//START InspectionDate (Some of the date fields have NULL Values)

if (InspectionDate.CurrentValue is System.DBNull) {
InspectionDate.CellCssStyle = "background-color: #FFE680"; }

//If DATE NOT NULL then do this
else if (InspectionDate.CurrentValue != null){

DateTime DateInspectionDate = Convert.ToDateTime(InspectionDate.CurrentValue);
DateTime DateToday2 = DateTime.Today;
var DateDifferenceDays2 = Convert.ToInt32((DateToday2 - DateInspectionDate).TotalDays);

if (DateDifferenceDays2 <1){
InspectionDate.CellCssStyle = "background-color: #172459; color: #FFDF00";}
else if ((DateDifferenceDays2 >= 1) & (DateDifferenceDays2 <= 3)){
InspectionDate.CellCssStyle = "background-color: #172459; color: #FFFFFF";}
else if ((DateDifferenceDays2 > 3) & (DateDifferenceDays2 <= 5)){
InspectionDate.CellCssStyle = "background-color: #283465; color: #FFFFFF";}
else if ((DateDifferenceDays2 > 5) & (DateDifferenceDays2 <= 7)){
InspectionDate.CellCssStyle = "background-color: #3a4572; color: #FFFFFF";}
else if ((DateDifferenceDays2 > 7) & (DateDifferenceDays2 <= 14)){
InspectionDate.CellCssStyle = "background-color:#4c567f; color: #FFFFFF";}
else if ((DateDifferenceDays2 > 14) & (DateDifferenceDays2 <= 30)){
InspectionDate.CellCssStyle = "background-color: #5e678c; color: #FFFFFF";}
else if ((DateDifferenceDays2 > 30) & (DateDifferenceDays2 <= 60)){
InspectionDate.CellCssStyle = "background-color: #707898";}
else if ((DateDifferenceDays2 > 60) & (DateDifferenceDays2 <= 90)){
InspectionDate.CellCssStyle = "background-color: #8289a5";}
else if ((DateDifferenceDays2 > 90) & (DateDifferenceDays2 <= 180)){
InspectionDate.CellCssStyle = "background-color: #9399b2";}
else if ((DateDifferenceDays2 > 180) & (DateDifferenceDays2 <= 365)){
InspectionDate.CellCssStyle = "background-color: #a5aabf";}
else if ((DateDifferenceDays2 > 365) & (DateDifferenceDays2 <= 730)){
InspectionDate.CellCssStyle = "background-color: #b7bbcb";}
else if ((DateDifferenceDays2 > 730) & (DateDifferenceDays2 <= 1825)){
InspectionDate.CellCssStyle = "background-color: #c9ccd8";}
else if ((DateDifferenceDays2 > 1825) & (DateDifferenceDays2 <= 3650)){
InspectionDate.CellCssStyle = "background-color: #dbdde5";}
else if ((DateDifferenceDays2 > 3650) & (DateDifferenceDays2 <= 36500)){
InspectionDate.CellCssStyle = "background-color: #dee0e7";}
else if ((DateDifferenceDays2 > 36500) & (DateDifferenceDays2 <= 3650000)){
InspectionDate.CellCssStyle = "background-color: #CD2626";}
//the last check here is for dates that may have been added that are clearly wrong e.g year 1754
}
//FINISH InspectionDate
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm

Re: Colour Rows Based on Greater than or Less than Ranges

Postby xgis » Mon Mar 20, 2017 9:51 am

The success of the last 2 code samples above is dependent on the datetime format, for the relevant date field in ANM.

The Date/Time named format in the case of DateTime that includes Time is set to "DateTime" Format
Alternatively it may be left as "Default", but NULL values may cause issues.

In the case the field is only displaying short date eg "31/12/2017" then you can use format "dmY"

Troubleshooting
In the event you receive a "String was not recognised as a valid DateTime" changing the ANM date format should resolve the issue.
This code can be touchy and was only tested against MSSQL.

Duplicating the code to other tables requires you to pay close attention to the renamed variables.
If at all possible implement a naming convention that allows you to do a single word copy paste in notepad or Visual Studio Code.
eg InspectionEditedDateLast vs WorksEditedDateLast
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm


Return to User Submited Tips (ASP.NET Maker)