Building jQuery DataTables with expandable accordion rows

Note: We just pushed out an update to the data table that includes a third tab for assistant coach salaries. I have updated my blog post to include links to two additional data files but otherwise have not discussed this new tab. If you are reviewing my JavaScript code, however, you may find it more helpful to look at the fnFormatAsstDetails function used by this new tab rather than the fnFormatDetails function discussed below. The code in this function is less complex because the assistant coach data lacked some of the challenges found in the head coach data.

At USA Today, the newsroom’s data team has had a recent fascination with DataTables, a jQuery plugin that enables you to embed a richly interactive and highly customizable HTML table which users can sort and filter. Allan Jardine has developed a first-rate piece of open source software that is extremely complex yet so simple to use, you can throw up a basic data table in 20 minutes.

The most complex data table we’ve built to date was launched today: an interactive table providing salary data for NCAA football coaches at 120 schools. I won’t go into a lot of detail describing it, since you can see it here, but the interactive includes expandable “accordion” rows that let you see much more detail when clicked, including photos of each coach, five years of salary data and a Google chart that graphs salary information by coach and conference. One of our designers, Kristin DeRamus, developed the CSS.

There are plenty of tutorials on the DataTables website, and the forums include a wealth of information, so I won’t talk about the basics here. Instead, I’m going to cover how to build a data table with accordion rows. In another post, I’ll talk about how to embed a Google chart.

I wanted to blog about this interactive to share our code with an ever-growing community of journalists and interested citizens who use data and a myriad of technologies to promote better storytelling. Matt Thompson over at NPR recently posted an article on Poynter about the merits of journalists sharing their data and code with the open-source community. He makes some compelling arguments for why this is a good thing: It increases the transparency of our work, which is rightfully and routinely subject to intense scrutiny. And given the reality of ever-shrinking newsrooms, anything we can do to make data more accessible to others furthers the watchdog role of journalism.

And, admittedly, it’s just fun and cool to show off your bling.

Back to the data table. If you want to see the source code for the data table independent of all the content wrapped around it, you can go here. In the code for this page, you’ll see full-path links to all the CSS and jQuery plugins you need, including our standard CSS for all our data tables. Custom CSS for this data table is embedded in the .htm file.

You’ll also need the five files that house all the data in JSON format. You can get those files here, here, here, here and here.

To build this table, I started with Jardine’s tutorial for building tables with drill-down rows. I’m going to ignore the fact this page has tabs and just focus on the Coaches table, which I created like this:

I’m posting this code above for reference but don’t want to go into any detail, since it’s just bread-and-butter DataTables. There’s plenty of documentation on the DataTables site about how to build one if you’re not sure and how each property is used.

Let’s go to the code that handles the Click event for the table:

This function captures the row that has been clicked. If it’s already open, the “else” clause fires and closes the row. Otherwise, the data for the row is passed to the fnFormatDetails function (which dynamically builds the HTML for the expanding row). Once that code is returned, a couple lines of jQuery code provide animation to expand the clicked row and slide it to the top of the table.

The fnFormatDetails function is just simple JavaScript. While it is a long function, you can see it is just building a long string of HTML to create <div class=”innerDetails”>. That HTML string is housed in the variable sOut.

Tip: To see what HTML is being generated by your function during development, just insert the line alert(sOut); immediately before return sOut;

If you look at this function and your jaw drops because it’s almost 300 lines of code, keep in mind that the vast majority of the code is needed to produce the Google chart. Aside from that chart, only the first dozen or so lines of the function and the last 25 lines or so produce the rest of the HTML. The Google chart, which I’ll cover in another blog post, was difficult to create because each coach includes additional contract data for zero to four years (housed in nested arrays). Were it not for the chart, this function would have been less than 50 lines of code!

If you want to see simpler examples of data tables that can be posted quickly, check out: 58 members of Congress among wealthy 1%, Megadonors pump money into SuperPACs and Hospital death and readmission rates.

Two side notes: First, you certainly can wire a data table to a database, if you like, but I have yet to do this. We’ve used DataTables exclusively with smaller datasets where it’s not impractical to pass the entire dataset to the browser. This speeds up the page by eliminating database calls completely and allows for rapid client-side processing of the data.

Second, if you’ve got your data in Excel and want to convert it to JSON, Mr. Data Converter is your friend. I should note that I did not use Mr. Data Converter here only because it wouldn’t work with the nested Contracts data. (Instead, I wrote a SQL Server stored procedure that exported the data in XML format, then wrote a quick Python script to convert that XML data to JSON format.) But most of the time, I use the Mr. Data Converter site to build well-formed JSON data for my DataTables.

Have fun, good luck and Happy Coding!