Julia Ridgely

Date: Jun. 15, 2010

We all know by now that visualizing data is easier for many people than reading columns of numbers, especially for people who aren't "data heads." Many of us have gotten pretty good at making impressive charts in Excel and PowerPoint. Web charts have also gotten easy; as usual, Google has anticipated our needs and offers Chart Tools for the web.

But what these charts have in common besides the power to persuade is that they're static. Unless you connect them to a data source, you'll be looking at a snapshot in time, not the latest data. That's fine if you're presenting a quarterly report, but what if you want to include an up-to-the-minute chart? Such charts are considered high-value enough that they've become the centerpiece of a whole software category, the business intelligence dashboard. An important part of any dashboard package--besides the fancy chart wizards--is a tight connection to the database, without which you can't create a dynamic chart.

Or can you? We know by now that HTSQL greatly simplifies the data connection layer of any web application. To demonstrate this, we recently integrated a web-based charting package. The new charting features of HTSQL are basic (for now), but they show how easy it is not only to integrate client-side web tools, but how simple the resulting commands are to use.

Here's an example, revisiting our favorite high-paid executives from the EDGAR database. This HTSQL request asks for executives whose 2008 compensation was more than $40 million:

http://edgar.htsql.com/compensation{officer.officer_name,amount}?amount>40000000&compensation_type='TOTAL'&year=2008

To see the same information as a chart, just add the chart command:

http://edgar.htsql.com/compensation{officer.officer_name,amount}?amount>40000000&compensation_type='TOTAL'&year=2008/chart(width='600',title='Top Executive Compensation')

Removing 'compensation_type='TOTAL' will automatically create a series, since the executives receive more than one type of compensation:

http://edgar.htsql.com/compensation{officer.officer_name,compensation_type,amount}?amount>40000000&year=2008/pivot()/chart(width='600',title='Compensation',legend='nw')

By default, HTSQL will display a bar chart. Height and width are optional, as is a title for chart. Aggregating data allows us to take the focus off individuals and look at compensation trends by industry. Here is the maximum (highest) compensation for the different sectors of the computer industry:

http://edgar.htsql.com/company_extended{industry^,max(company.compensation.amount)}?industry~'computer'/chart(width='700',title='Max Compensation by Industry')

Here's the same information in a pie chart, with a legend positioned in the upper right (or "northwest" corner):

http://edgar.htsql.com/company_extended{industry^,max(company.compensation.amount)}?industry~'computer'/chart(type='pie',legend='nw',width='500',title='Max Compensation by Industry')

The best thing about these charts is that they're dynamic--when the data changes in the database, so will the chart. Like any HTSQL URL, they can be easily shared or included in web pages or social networking applications. They're also easy to use as dashboard "widgets" for popular platforms like SharePoint or Google. To find out how, you can view our recent dashboard webinar by my colleague Andrzej, who'll show you step-by-step examples.

• Add new comment