How to do enhanced project charts

My idea about enhanced project charting raised some discussion in the web forum provided by Perceptual Edge, Stephen Few's site.

Just to convince you it wasn't Excel (!), here's the dialog box to specify the color scale in Voyant:



Actually the process of creating such project charts in Voyant needs only three steps:

1. Create a projdata table and populate it with sample data (or use a corresponding table in your database).

CREATE TABLE projdata (
project_name VARCHAR(20),
day DATE,
hours DECIMAL(20,2));

After that it takes only 5 minutes:
2. By using a Sheet chart, specify four dimensions:

Measure: Hours = SUM(hours)
Rows: Project = project_name
Columns, outer dimension: Year = {fn YEAR({fn TIMESTAMPADD(SQL_TSI_DAY,-{fn MOD({fn DAYOFWEEK(day)}+5,7)}+3,day)})}
Columns, inner dimension: Week = {fn FLOOR(1+({fn DAYOFYEAR({fn TIMESTAMPADD(SQL_TSI_DAY,-{fn MOD({fn DAYOFWEEK(day)}+5,7)}+3,day)})}-1)/7)}

NOTE: The expressions above apply in SQL Server, MySQL, and other ODBC compatible databases. For other suitable expressions and how to limit the time frame, see my posting about monitoring changes over time.

3. Use the Sheet Cell Format dialog box (presented in the beginning): first for the whole chart, and second for the total row.

For the whole sheet, select Min & max determined automatically, so the color scale adapts to the greatest and least number of hours found from the data (excluding the total row).



For the total row, it's necessary to deselect Exclude totals, formulas, and cumulative numbers, so that the color scale adapts to the numbers presented in the total row.

It's also possible to use a tool tip to show the exact work hours by moving a mouse cursor over a cell of interest, as in the following image:



I hope we can soon see the color scale feature available in other OLAP tools too.

Viewing project data--new options available

In this posting, I will explore different ways to view project data. Some options have become available just recently.

Let's start with the well-known gantt chart. It's a great tool for project scheduling.

The gantt chart displays the start and finish date for each project. For example, the chart above shows that Project P is the longest project, starting on November 2006 finishing on August 2007.

However, a gantt chart doesn't show us how much resources each project requires.

Let's try a line chart with the same data, work hours displayed on a weekly basis.

Now we can see that project P (the longest project) doesn't require too much resources. Project K requires much more and so does A and G. You can also see the timing of these projects, that is, when the most work is done. This is important information that the gantt chart didn't tell us!

OK, let's try the area chart.

Now you can see the total resources required for all projects.

However, the line and area charts are hard to read when you have many projects. The colors of the 15 projects above are so-so distinguishable. If you have hundreds of projects, the line and area charts become useless.

Fortunately we have some new options available. Microsoft Excel calls this option "color scale" and so does Voyant's new version. Here's the same project data with Voyant.

It looks like the original gantt chart!

But instead of having just solid blue bars, now each blue bar contains much more information. First, there are weekly hours available. Second (and what is most important), the intensity of color tells us directly which project is hot and when.

If there are hundreds of projects, this kind of display still works (you just scroll down).

There's also an alternative to hide the numbers.

In the image above, the total row displays the overall resources required (red color scale).