3 Replies Latest reply on Mar 16, 2011 9:02 AM by sockerdad

    Advanced Charting Issue

    sockerdad Level 1

      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?

        • 1. Re: Advanced Charting Issue
          Dan Bracuk Level 5

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

          • 2. Re: Advanced Charting Issue
            sockerdad Level 1

            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.          

            • 3. Re: Advanced Charting Issue
              sockerdad Level 1

              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?