Monday, July 09, 2007

Using Color Columns To Make A Gantt Chart

I love simple solutions. For me, application development is about solving a problem as efficiently and elegantly as possible. Some people like to write code just to write code, but I prefer getting my job done so that my users can do theirs. Today, I wanted to share a quick and simple solution that also looks cool and is very easy to implement.

You've seen several of my applications that make use of color columns. I even blogged about them awhile back. You've all probably also seen an application somewhere along the line that included a view with a "gantt chart". There are a million different ways to go about building this functionality into a view, but I like the solution of using color columns due to their simplicity. Here's an example:

The trick I used here was to create 10 color columns whose values are checked (in increments of 10) against the value on the document that shows percent complete. Here's the formula for the first color column:

@If(PercentComplete >= 10;

and the second:

@If(PercentComplete >= 20;

and so on, incrementing the check by 10 each time. Of course, if you wanted to get more granular, you could create columns in increments of 5 or even 1 (if you are a real glutton for punishment!).

As you can see, if the value of the field is greater than the column being checked, it is colored. The background and text are set to the same color values. If PercentComplete is less than the column we are checking against, the color column resets the background text and color to white.*

If you want to show the actual percentage value in the middle of the chart (as in the picture above), then make column #5 hidden and set column #6 to display the value. In order to make the text of column #6 visible, the preceding column formula is slightly different, setting the text color to black (or whatever color complements your graphic scheme).

@If(PercentComplete >= 50;

That's all there is to it really. Just don't forget to check the "Use value as color" property on your color columns.

Do you have a variation on this or other clever uses for color columns in your apps? If so, please feel free to share with the class. Cheers!

*Notice that this looks less than perfect if you are using alternating row colors. Extra Bonus Points to the person that can figure out how to set the color column to "transparent". If you are using subtle colors for your alternating rows (you should be), then it doesn't look horrible, but it would sure be nice if we could have a little more granular control over this.


Keil Wilson said...

Chris, I'm not sure if you were just joking or not, but you know that (from the Notes help) "Setting colors to -1:-1:-1 reverts to the view and column properties," right? It's not exactly transparent, but should just display whatever default color you have set for your alternating rows.

Bruce said...

Nice job Chris and thanks for helping me the other day with this. You are "the man".

Patrick Kwinten said...

Myself use Adhoc's Gantt Chart for Lotus Notes:


Benoit Dubuc said...

Simply brilliant... I could have used that in a few projects!!!

I'll try to remember the next time I need this.

Nathan T. Freeman said...

Patrick, I think you're missing Chris' point here.

jonvon said...

you sir are a madman. ;-)

Josep Alemany i Fruitós said...

Fantastic Job Chris!

Some times the best things are the most simples.

This will help me a lot.

dogu said...

I use a similar, if not quite so elegant, solution that involves view icons.

We create thise things calle QE Notices and they have a 6 month life from the time they're approved. I wanted to provide a view level graphic that provides the user with a 'count down' flag.

6 columns - each set to display values as icons.

Col 1:
ED := QEExpireDate;
TodayPlusN := @Adjust(@Today;0;1;0;0;0;0);
@If(TodayPlusN > ED; ""; 151)
where 151 is the 'yellow ball with a 1 inside' icon.

Col 2:
ED := QEExpireDate;
TodayPlusN := @Adjust(@Today;0;2;0;0;0;0);
@If(TodayPlusN > ED; ""; 152)
to show the '2' icon

Repeat up to col 6 which is:
ED := QEExpireDate;
TodayPlusN := @Adjust(@Today;0;6;0;0;0;0);
@If(TodayPlusN > ED; ""; 114)
to shows a green ball.

When the QE Notice is new the users sees:
1 2 3 4 5 green ball

Each month one ball drops off the list.

I think I like the color thing better and might adjust it so the color changes as you get closer to 'time zero' - green first, then yellow, then red.

Chris, thanks for posting - as always your stuff really gets me rethinking my design concepts.


DSParillo said...

Nice post - I've used various versions of @Repeat (depending on the application - in excel its REPT) for a long time, but your solution looks better I think.

Theo Heselmans said...

I got inspired by Chris, and posted a blog for creating graphs, using 1 calculated icon-column only.
Have a look:
Nice and easy.

Chris Blatnick said...

@Keil...Thanks for your comment and you're's close but not exactly how I'd like it to work. Unless I am doing something wrong, the problem here is that you'll end up showing the value "-1:-1:-1" in the column. We could fix this by adding 10 more columns. The first column would be hidden and set the color and the second would serve as the "bar" and so on. This would allow us to reset the background color to the standard for the alternating rows. A little more work, but certainly doable.

Chris Blatnick said..., "you are the man". Remember, I am just a lowly underwear consultant! :-D Glad to help, sir!

Chris Blatnick said...

@Doug and Theo: Both great solutions and I agree that sometimes it can be nicer to use graphics. Making your own is great if you have a really custom UI, but for some people this is too much work. In any case, thanks for sharing your ideas. And Doug...ohhh...I love the idea of the changing colors!

Kevin Pettitt said...

@Doug - Neat idea, but @Today has got to go - huge performance killer. Fortunately, Sean Burgess posted a very nice technique a few weeks ago to work around that problem here

And since everyone else is piling on with ideas, I figured I'd throw a couple suggestions in as well.

Christian Petters said...

My most recent approach had been similar to the one Theo described in using shared images as view icons.

Anonymous said...

Can someone tell me how you would setup to track based on % complete and a completion date that the color changes green if on schedule, yellow if getting close to past due, then red when past due? For example, if I say I will complete a task in 4 months I should complete 25% per month in order to be on schedule.

Anders Mygind said...

If you are using alternate rows the empty string "" will provide you with transparency ;)

red := 254:0:0;
yellow := 250:252:0;
white := 255:255:255;
black := 1:1:1;
plain:= 0:0:0;
transparency :=""
@If (colorview="green";green:green ;colorview="yellow";yellow:yellow; colorview="red";red:red;transparency)

Anthony Morano said...

Good information, I also love the simple solution to the problems. I think that only stupid people complicate their lives. I can help someone, I will share these patterns, they make life easier when doing presentations often.