?
Solved

coldfusion graphing help

Posted on 2007-07-24
4
Medium Priority
?
253 Views
Last Modified: 2013-12-16
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>
0
Comment
Question by:cseink
  • 2
  • 2
4 Comments
 
LVL 14

Accepted Solution

by:
Scott Bennett earned 2000 total points
ID: 19560437
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
 

Author Comment

by:cseink
ID: 19561353
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
 

Author Comment

by:cseink
ID: 19565417
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
 
LVL 14

Assisted Solution

by:Scott Bennett
Scott Bennett earned 2000 total points
ID: 19566524
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question