Link to home
Start Free TrialLog in
Avatar of cseink
cseink

asked on

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>
ASKER CERTIFIED SOLUTION
Avatar of Scott Bennett
Scott Bennett
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cseink
cseink

ASKER

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
Avatar of cseink

ASKER

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>  
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial