You don’t have to load in bitmap / SVG / javascript based graphing libraries to add visual queues to your reports. You can get a long way using CSS.

I’ve recently been delving into some data analysis for a customer to get trends of error codes from a field device. The customer was concerned that the error rate is increasing. These things are sometimes hard to visualise because there is plenty of variation from day to day. For the sake of keeping my blog active I thought I’d note a few things and if anyone out there comes across this, maybe you could give some thoughts in the comments.

I decided to make a quick and dirty web app to visualise the data. I chose CodeIgniter because it seems to be pretty popular and I’ve worked with it before. CodeIgniter uses an MVC approach but in this case I haven’t much use for the ‘M’ straight up so I start with a controller that will give me the error percentages for each of the error codes over the last 24 hours. Here’s how I do the query:

In the view, I print a table of the error codes and the counts for the last 24 hour period. The last column of the table is a basic bar graph which helps to immediately pick out the red flags.

I write the text for the table using the rows returned by my query which is simple enough, open a table tag, the do a foreach loop over the result rows generating and tags as approriate and close the table after the loop. The percentages are being calculated against the total throughput of items which I obtained in a separate query.

To draw the last column, I did something a little special. First for the sake of scale, I iterated through the array earlier to obtain the maximum percent value (I also incidentally used this loop to get the value for the total row):

Inside the main foreach loop, I make use of a little helper I wrote:

The helper looks like this:

(Don’t forget you have to load helpers with a $this->load->helper(‘bar_helper’);)

The ‘bar’ function creates a couple of nested divs and uses the style element to set the width of the bar. In the CSS you just need to style the outer bar to have the width you want and the inner bar to be the colour you want. Like this:

Some readers may notice that my table also links to trends and that the bar_helper file also had a vbar function. The trends were slightly more difficult. They look like this:

I had to do a bit more work to get the trends right. First the query:

This query gives me an array with a column of 14 days and a column of error counts. I did a second query to get the total throughputs for the day. Things got a bit weird because on weekends we had a little bit of throughput but no errors so the two arrays had different numbers of rows. I got around this in a fairly expensive way by turning the second query result into a keyed array. This seems pretty expensive and if I knew a way to do all this in one SQL query or simpler PHP it would be worth doing:

You can see I did two array_maps to split out the columns and then combined the arrays back into a keyed array. Now in the view, to access the throughput total for a day I need to do $totals[$errors[$ii][‘tdldate’]].

Another complexity of the view is that with the data in columns but the page view in rows, I had to iterate through the array once for each row. I came up with a dodgy way to save myself writing the loop four times by using an array lambdas, once for each row:

I know newer versions of PHP support closures instead of create_function but this is an oldish server.

The final trick to this little application was that when I did the vertical bar graph, the bars wanted to grow from the top of the cells down rather than growing up from the bottom. If you look at the vbar function above, you’ll see I use two inner divs to make the bars so that I fill the whole outer bar. The first bar is actually the space above the top of the bar and then the second bar is the actual coloured part of the bar.