A few years ago then Google CEO Eric Schmidt was quoted as saying, “There was 5 exabytes of information created between the dawn of civilization through 2003, but that much information is now created every 2 days, and the pace is increasing”. The great volumes of data available for analysis have necessitated the development of tools and methodologies to help users derive information from the vast volumes of data. Conditional formatting is one such simple but effective tool. Typically, data indicating possible problem areas is highlighted in red and good areas in green. This post is a continuation on the series showing how Drupal can be used as a Business Intelligence or data analysis platform.
Implementation
You only need have a Drupal view that has fields to implement conditional formatting. In this post I used views I built for two previous posts. In the first post I created a Drupal view from a table in an external database. Then I created a view with drill down functionality. Typically, business users only want to drill down on problem areas. I am going to apply different coditional formats to different ranges of Total Quantity sold values:
- Green more than 100 units sold
- Amber between 50 and 100 units sold
- Red under 50 units sold
The business users can quickly zero in on the problem areas and analyse the sales details by drilling down to the detail.
To implement conditional formatting you apply a condition to a field in your view. You use the condition’s return value is to format the data in the view. As is usually the case with Drupal, you have a choice of contributed modules to write conditions. Views PHP, Views Conditional and Views Raw SQL to name a few.
Views Raw SQL
After none exhaustive evaluations of several modules I decided to use the Views Raw SQL module. I settled on this module of two main reasons:
- Enables you to use SQL expressions to define the conditions. SQL is a powerful language with functions for string, numeric and conditional programming.
- Condition statement is sent to the database for execution. This is a good thing because your database should always be more powerful than your web server.
I used the CASE statement to create a conditional formatting field.
CASE WHEN vw_genre_total.Total_Quantity
Conditional Formatting
To format the data I created CSS class names that corresponded to values returned in my condition. You should put this CSS in your theme style sheet class in your Drupal theme.
.red{color:red;border-right: thick solid #ff0000;text-align:right;}
.orange{color:orange;text-align:right;}
.green{color:green;text-align:right;}
The next step was to rewrite output of the Total Quantity sold field. I assigned the return value from the conditional field as the CSS class to be added to the field. Below is the screen shot showing use of condition to assign class to formatted field.
I kept my conditional formatting CSS classes very simple. You don’t have to limit yourself to merely changing font colours. Off the top of my head other properties that would be useful are the background-image and font properties. Below is the output of my formatted data.
Conclusion
You really are spoilt for choice when it comes to implementing conditional formatting in Drupal Views. To define the conditions you can use PHP via the Views PHP module or SQL as I did in this post. Using CSS you can format your data using the power of CSS. You can change layout, fonts and backgrounds to draw your user’s attention specific data. Having worked with a number of commercial BI applications, I can confidently say that functionality to implement conditional formatting in Drupal views is on par commercial BI software.