coldfusion graphing help

I want to graph this data but am unfamiliar with cfml graphing. X-AXIS would be [ED DOOR DT] AND y-axis would be a integer. The line data is CT30,CT60,CT90,CT120,CT120 PLUS FOR EACH DAY.  


code so far..
<!-- Get the raw data from the database. -->
<!-- cfset getdate1 = FORM.one  -->
<!-- cfset getdate2 = FORM.two > -->


<cfquery name="GetSpace" datasource="EDSTATS">
SELECT     (SELECT     COUNT(*)
                       FROM          edstats
                       WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                              [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) < 30) AS ct30,
                          (SELECT   COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 30 AND 60) AS ct60,
                          (SELECT    COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 60 AND 90) AS ct90,
                          (SELECT    COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 90 AND 120) AS ct120,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) > 120) AS ct120plus

</cfquery>






<html>
<head>
   <title>charts</title>
</head>

<body>
<h1>ed gRAPHS </h1>

<!--- Line chart--->

<cfset variables.colorList = "red,green,blue,yellow,black,##ff9900,##ccff00,##999999,##FF00FF,##CC99FF,##666633,##99ccff,##F9D031,##800040,##336600">  <!---- list your colors ---->

<cfchart
      xAxisTitle="Date"
      yAxisTitle="#"
       chartHeight = "500"    
       chartWidth = "500"
     scaleto="100"
      font="Arial"
      gridlines="10"
      showXGridlines="yes"
      showYGridlines="yes"
      showborder="no"
      show3d="no"
     showlegend="yes"
     sortxaxis="yes"
     showmarkers="yes"
format="jpg"




<cfoutput query="GETspace" group="[ed door dt]">

     <cfset variables.thisColor = listFirst(variables.colorList)>
     <cfset variables.colorList = listRest(variables.colorList)>

  <cfchartseries


    type="LINE"
    seriesColor="#variables.thisColor#"
    paintStyle="plain"
    seriesLabel="#getspace.CT120#">

    <cfoutput>
     
 <cfchartdata item="" value="">
    </cfoutput>


  </cfchartseries>

</cfoutput>
 
</cfchart>



<br>
</body>
</html>
cseinkAsked:
Who is Participating?
 
Scott BennettConnect With a Mentor Manager TechnologyCommented:
try it like this:

<cfquery name="GetSpace" datasource="EDSTATS">
SELECT     (SELECT     COUNT(*)
                       FROM          edstats
                       WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                              [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) < 30) AS ct30,
                          (SELECT   COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 30 AND 60) AS ct60,
                          (SELECT    COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 60 AND 90) AS ct90,
                          (SELECT    COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 90 AND 120) AS ct120,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) > 120) AS ct120plus

</cfquery>

<cfchart
      xAxisTitle="Date"
      yAxisTitle=""
       chartHeight = "500"    
       chartWidth = "500"
     scaleto="100"
      font="Arial"
      gridlines="10"
      showXGridlines="yes"
      showYGridlines="yes"
      showborder="no"
      show3d="no"
     sortxaxis="no"
     showmarkers="yes"
format="jpg"
      >

      <cfchartseries
            seriesLabel="Status"
            type="line"
            paintStyle="plain">
            <cfchartdata item="CT30" value="#EDSTATS.CT30#">
            <cfchartdata item="CT60" value="#EDSTATS.CT60#">
            <cfchartdata item="CT90" value="#EDSTATS.CT90#">
            <cfchartdata item="CT120" value="#EDSTATS.CT120#">
            <cfchartdata item="CT120plus" value="#EDSTATS.CT120plus#">
            </cfchartseries>
</cfchart>  
0
 
cseinkAuthor Commented:
Ok. we are getting there. This shows the the items ct30->ct120 on the y-axis but I need the [ed door dt] date to be listed on the y-axis with multiple  line graph representing ct30,ct60,ct90,ct120,ct120plus .

Thanks so much for your help
0
 
cseinkAuthor Commented:
How can I get the date to show up on the bottom?? Right now it showing  a number from 0 -60 for each day..

<cfquery name="GetSpace" datasource="EDSTATS">
SELECT     [ED DOOR DT],
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
                                                   [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]), [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) < 30)
                      AS ct30,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
                                                   [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]), [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN
                                                   30 AND 60) AS ct60,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
                                                   [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]), [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN
                                                   60 AND 90) AS ct90,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
                                                   [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]), [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN
                                                   90 AND 120) AS ct120,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
                                                   [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]), [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) > 120)
                      AS ct120plus
FROM         EDSTATS e
WHERE     ([ED DOOR DT] BETWEEN '05/01/2007' AND ' 06 / 30 / 2007 ')
GROUP BY [ED DOOR DT]
ORDER BY [ED DOOR DT]

</cfquery>
<cfset variables.colorList = "red,green,blue,yellow,black,##ff9900,##ccff00,##999999,##FF00FF,##CC99FF,##666633,##99ccff,##F9D031,##800040,##336600">  <!---- list your colors ---->
<cfchart
      xAxisTitle="Date"
      yAxisTitle="Counts"
       chartHeight = "800"    
       chartWidth = "800"
     scaleto="100"
      font="Arial"
      gridlines="10"
      showXGridlines="yes"
      showYGridlines="yes"
      showborder="no"
      show3d="no"
     sortxaxis="no"
     showmarkers="yes"
format="jpg"
      > 

   <cfchartseries
      type="line"
       seriesLabel=">120 "
      query="getspace"
paintstyle="plain"
      valueColumn="ct120plus"
      itemColumn="[ed door dt]"
      seriesColor="red"
            
   />
   
      <cfchartseries
      type="line"
       seriesLabel="90-120"
      query="getspace"
paintstyle="plain"
      valueColumn="ct120"
      itemColumn="[ed door dt]"
      seriesColor="blue"
            
   />
   
        <cfchartseries
      type="line"
       seriesLabel="60-90"
      query="getspace"
paintstyle="plain"
      valueColumn="ct90"
      itemColumn="[ed door dt]"
      seriesColor="green"
            
   />

        <cfchartseries
      type="line"
       seriesLabel="30-60"
      query="getspace"
paintstyle="plain"
      valueColumn="ct60"
      itemColumn="[ed door dt]"
      seriesColor="black"
            
   />
   
         <cfchartseries
      type="line"
       seriesLabel="0-30"
      query="getspace"
paintstyle="plain"
      valueColumn="ct30"
      itemColumn="[ed door dt]"
      seriesColor="yellow"
            
   />



                    
</cfchart>  
0
 
Scott BennettConnect With a Mentor Manager TechnologyCommented:
you will want to rename [ed door dt] as a variable name that will be compatible with CF syntax like:

SELECT     [ED DOOR DT] as ed_door_dt

In fact I would recomend never using spaces when naming database columns in the first place.

and then use itemColumn="e_ door_dt" in your chartseries tags.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.