Solved

cfchart question

Posted on 2009-05-18
14
226 Views
Last Modified: 2012-05-07
I have the following query
<CFQUERY NAME="QoQWeeklyHours" DBTYPE="Query">
  SELECT WEnded, Executive, Sum(Qty) AS TotQty FROM WklyHoursQuery
  GROUP BY WEnded, Executive
  ORDER BY WEnded, Executive
</CFQUERY>
and I want to create a line graph with lines for each executive showing TotQty for each WEnded (week ended date).  I have spent hours trying to get something to work but I just end up in a mess.  Any help greatly appreciated.
0
Comment
Question by:jdthedj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 63

Expert Comment

by:Zvonko
ID: 24422870
Show your query result data dump (samples) and your cfchart statements that you have so far.
0
 
LVL 3

Author Comment

by:jdthedj
ID: 24426642
Thanks for the reply Zvonko - here is what the data looks like
05/04/2009      Ed      1754.25
05/04/2009      James      2019.00
05/04/2009      Jeanette       871.75
12/04/2009      Ed                 1212.50
12/04/2009      James      2263.25
12/04/2009      Jeanette      924.50
19/04/2009      Ed      1654.25
19/04/2009      James      2259.00
19/04/2009      Jeanette      919.75

and here is the cfchart code

<cfchart format = "flash" chartHeight = "200" chartWidth = "800" showXGridlines = "yes" showYGridlines = "yes"
   xAxisTitle = "Week Ended" yAxisTitle = "Hours">
<cfchartseries
  type="line"
  query="QoQWeeklyHours"
  itemColumn="Executive"
  valueColumn="TotQty"
  seriesLabel="Executive">
</cfchartseries>
</cfchart>

What I want is separate lines on the graph for each executive with the week ended dates along the bottom, and the number of hours on the Y axis.  With the code above I get several graphs showing meaningless data.  I also tried using labelFormat="date" but that either crashes the charting engine or goes off into the wide blue forever.  I really don't know what I am doing, so I really need the help.
0
 
LVL 63

Accepted Solution

by:
Zvonko earned 500 total points
ID: 24434075
Here my proposal:

<CFQUERY NAME="QoQExecutives" DBTYPE="Query">
  SELECT Executive FROM WklyHoursQuery
  GROUP BY Executive
  ORDER BY Executive
</CFQUERY>

<cfchart format = "flash" chartHeight = "200" chartWidth = "800" showXGridlines = "yes" showYGridlines = "yes"
   xAxisTitle = "Week Ended" yAxisTitle = "Hours">
  <cfloop query="QoQExecutives" >
    <CFQUERY NAME="QoQ_Qty" DBTYPE="Query">
      SELECT WEnded, Qty FROM WklyHoursQuery
      WHERE Executive='#QoQExecutives.Executive#'
      ORDER BY WEnded
    </CFQUERY>    
   <cfchartseries query="QoQ_Qty"
      type="line" itemColumn="WEnded" valueColumn="Qty"
      seriesLabel="#Executive#">
   </cfchartseries>
  </cfloop>
</cfchart>
0
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 
LVL 3

Author Comment

by:jdthedj
ID: 24447064
Thanks Zvonko - sorry for the delay in replying.  A couple of issues.  
1.  The dates, although the query is sorted by WEnded appear in random order in the graph
2.  How do I tell which graph line refers to which Executive so I can have a legend.
0
 
LVL 3

Author Comment

by:jdthedj
ID: 24447224
OK - I have figured out the legend, so that is working, but the dates are still random.
0
 
LVL 3

Author Comment

by:jdthedj
ID: 24462788
Any ideas on the sorting problem?
0
 
LVL 3

Author Closing Comment

by:jdthedj
ID: 31582842
Cannot sort dates into correct order despite query sorted on date
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 24575724
Did you ever find a solution to the dates appearing in random order?  I am having the same problem..
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 24576163
I do not see what you mean by "random order".
Can you dump your Query and tell me what rows are not in sequence in that chart?

You can open a new question and put here the reference to it.

0
 
LVL 3

Author Comment

by:jdthedj
ID: 24578108
No, I still have the problem.  Here is the code, and I am attaching a screenshot of the graph.

<CFQUERY NAME="QoQExecutives" DBTYPE="Query">
  SELECT Executive FROM WklyHoursQuery
  GROUP BY Executive
  ORDER BY Executive
</CFQUERY>

<cfchart format = "flash" chartHeight = "600" chartWidth = "800" showXGridlines = "yes" showYGridlines = "yes" showLegend="yes"
   xAxisTitle = "Week Ended" yAxisTitle = "Hours">
  <cfloop query="QoQExecutives" >
    <CFQUERY NAME="QoQ_Qty" DBTYPE="Query">
      SELECT WEnded, Executive, Sum(Qty) AS TotQty FROM WklyHoursQuery
      GROUP BY WEnded, Executive
      HAVING Executive='#QoQExecutives.Executive#'      
      ORDER BY WEnded, Executive
    </CFQUERY>    
   <cfchartseries query="QoQ_Qty"
      type="line" itemColumn="WEnded" valueColumn="TotQty"
      seriesLabel="#QoQExecutives.Executive#">
   </cfchartseries>
  </cfloop>
</cfchart>
Graph.jpg
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 24578535
You closed this question with a grading B and want now additional support???
Open a new question and perhaps somebody will look into it ;-)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 24580512
It was driving me crazy.   I found this parameter on cfchart...

                   sortXAxis="Yes"

That should put the x axis in order.

To format the date correctly, I was using the dateFormat() function but I was using cfchartitem as in...

 <cfchartdata item="#dateFormat(getValues.theDate,'yyyy/mm')#" value="#theValue#">


Since you are not using cfchartItem, you can probably format the date using your database's date functions such as convert( ) if SQL Server or TO_CHAR() if oracle.   So the query will return a date well formatted such as mm/dd/yyyy.

Zonko, jd was just answering my question, I was seeking a solution.  But since I found it, I thought it best to post here for future seekers.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 24583269
No problem ;-)
0
 
LVL 3

Author Comment

by:jdthedj
ID: 24585089
sortXAxis has fixed the sorting problem - thanks gdemaria!
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

624 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