Color Coding and PRD Charts

Maintaining consistent color coding for metrics on PRD charts can be a bit challenging, as chart colors are defined statically with no reference to a specific metric they belong to. This post shows how to maintain consistent relationships between colors and metrics in PRD charts using two different approaches. Get the sample reports from the downloads section if you’d like to follow along.

The Problem

Consider a side by side comparison of metrics, that have colors assigned by convention. The color coding sometimes stem from company conventions saying that sales are always green, cancellations are always red, etc. The color codes may also be inherent to the metric more directly. @JosVanDongen brought up the triage process as a real world example, while working on a BI solution for St. Antonius hospital. Triage is a classification process for patients used by paramedics and emergency hospital staff. Minor injuries are always depicted in green, urgent cases in red, etc. The colors are inherent to the metric, and deviations from the color scheme should be avoided.

When setting up a chart in PRD, colors are defined independent of metric, and they are used by the chart engine in the order specified. If the data fed into the chart always contains the same set of metrics in the same order, the same metrics will reliably be assigned the same colors. If on the other hand, the set of reported metrics or their order within the data vary, different colors may be assigned. The following samples illustrate the situation using the triage example.

When Static Color Assignment works

The following data table contains the same set of metrics for each day, and the first day in particular contains them in the order the colors are specified in the report. The static color assignment works perfectly.

 

When Static Color Assignment breaks

Now, remember that the colors for the chart are defined as black, red, yellow, green for morgue, immediate, delayed, minor. The following data table does not have any immediate attention patient on the first day. The desired behavior is to simply skip the red color, and continue with the yellow for delayed cases. However, the red color is next in line, so it is applied to the delayed metric on the first day, and kept throughout the chart. Yellow is next in line, so it is assigned to the upcoming minor injuries. The next day has some immediate attention patients and the last color, green, is used for this category. The result is very confusing, even though the legend shows the meaning of the colors in the chart.


Solution 1: Enforcing first Day’s Metrics to fit the Colors

Depending on the data source, there may be ways of ensuring the presence of each metric in the expected order of defined colors. Creative use of SQL may yield a dataset that always gives a complete set of metrics in a defined order for the first day, for example. The following SQL samples (developed on postgres) illustrate the approach:

The first stab at the report query may look something like this:

SELECT "date", "triage", count(patient_id) as "patients" 
FROM emergency_incidents 
WHERE "date" BETWEEN '2011-05-18' AND '2011-05-20'
GROUP BY "date", "triage" 
ORDER BY "date", "triage"

The result would be:

Fixing the Order of Triage Types

Ordering by “triage” gives the following triage order per day: delayed, immediate, minor, morgue. If the events should appear in the order morgue, immediate, delayed, minor the severity may be calculated separately and the results ordered by it as in the following example:

SELECT "date", "triage", 
CASE 
    WHEN "triage" = 'Morgue' THEN 0
    WHEN "triage" = 'Immediate' THEN 1
    WHEN "triage" = 'Delayed' THEN 2
    WHEN "triage" = 'Minor' THEN 3
END AS severity,
count(patient_id) as "patients" 
FROM emergency_incidents 
WHERE "date" BETWEEN '2011-05-18' AND '2011-05-20'
GROUP BY "date", "triage" 
ORDER BY 1, 3

Now the triage types appear in the correct order.

Now the events come in the correct order, but the first day still has the immediate triage type missing, which leads to incorrect color assignments. Another fix addresses that.

Fixing Missing Triage Types

To ensure that every triage type is present on the first day, all triage types are included with a zero incident count. They are subsequently summed up with the results of the DB query.


SELECT 
"date",
"triage",
CASE 
    WHEN "triage" = 'Morgue' THEN 0
    WHEN "triage" = 'Immediate' THEN 1
    WHEN "triage" = 'Delayed' THEN 2
    WHEN "triage" = 'Minor' THEN 3
END AS severity,
sum("patients") as "patients" FROM
(
    SELECT "date", "triage", "patients" FROM
    (VALUES 
      ('2011-05-18'::DATE, 'Morgue', 0),
      ('2011-05-18'::DATE, 'Immediate', 0),
      ('2011-05-18'::DATE, 'Delayed', 0),
      ('2011-05-18'::DATE, 'Morgue', 0)
    ) as zero_events ("date", "triage", "patients")

UNION ALL

    SELECT "date", "triage", count(patient_id) as "patients" 
    FROM emergency_incidents 
    WHERE "date" BETWEEN '2011-05-18' AND '2011-05-20'
    GROUP BY "date", "triage" 
) AS complete_metrics
GROUP BY "date", "triage" 
ORDER BY 1,3

Now the first day has all triage states in proper order, so the chart colors will be assigned correctly in all circumstances.


Solution 2: Assigning Colors to Series dynamically

Pentaho Report Designer allows to execute a post-processing script on charts. After the chart has been set up and is about to be rendered you get the opportunity to change it in any way you like. The limits are your imagination and the capabilities of the jFreeChart library.

The following Beanshell script looks at all series defined on the chart, and sets their colors, depending on the triage type associated with them.

// define colors
Color morgueColor = Color.black;
Color immediateColor = new Color(Integer.valueOf("ff2929", 16));
Color delayedColor = new Color(Integer.valueOf("fbfb4f", 16));
Color minorColor = new Color(Integer.valueOf("37df37", 16));


// grab the interesting objects from jFreeChart
renderer = chart.getPlot().getRenderer();
data = chart.getPlot().getDataset();
keys = data.getRowKeys();

// loop through each bar and give it a color based on its contents
for (int i=0;i<keys.size();i++){

	Color c = Color.darkGray; 

	switch(keys.get(i)){
		
		case "Morgue": 
			c = morgueColor;
			break;
	
		case "Immediate":
			c = immediateColor;
			break;
		
		case "Delayed":
			c = delayedColor;
			break;
		
		case "Minor":
			c = minorColor;
			break;
		
	}
	
	renderer.setSeriesPaint(i, c);
}

The scripting technique is particularly useful for charts that do not list categories in a fixed order, but rather have the order depend on the data. The following chart shows triage cases ordered by frequency. There’s no telling which the most common type will be, and assigning the color dynamically by script helps to achieve a consistent color code.


Downloads

Get the sample report files here. There’s a file for each case and sample discussed in the post.

Keep the woods green and the oceans blue!
Slawo

1 comment to Color Coding and PRD Charts

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>