Advanced Charting Issue

[sockerdad]

My latest assignment is to create a horizontal bar chart from a DB2 table to indicate the length of a project.

The table contains the Environment Name, the Start Date, the End Date and the Project Name:

"ENVA"  2010-01-01  2015-06-30  "REAL LONG PROJECT"

What is wanted is a chart with the environments on the vertical axis and the months (January through December of each year) across the horizontal axis:

Envrn.  Jan 2010  Feb 2010  Mar 2010...Dec 2010

ENVA     **********************************************

ENVA     ********************

Perhaps this isn't a cfchart issue but an SQL one, but I can't figure out how to configure the query in such a way that will give me the information required to create the chart.

Any suggestions?

[Dan_Bracuk]

Does your db have a table where the date is the primary key?

[sockerdad]

No, the environment is the primary key; the dates are just field in the row.

I found that I can include extra fields in my query and then manipulate them to be what I want them to be before executing the cfchart.  That solves some of the problems.

I think I need to step back and rethink the design.  I used cfchartdata to test the display and found that it wasn't giving me the results they wanted.

After I figure something out, I'll post it here for everyone's amusement.  Film at 11:00.          

[sockerdad]

I finally found a way to do this.

The display is basically a 13-column table:  the first column is the name and the subsequent columns are the months for the given year.  Using the project dates and cfloop, I merely set the cell's background color if its project dates included the month and year of the cell.  And to be really fancy, I assigned each project its own color.

And after all that, they decided that they really didn't need to see the display after all!

Management - what are you going to do with them?