APS NZ
asked on
cfchart question
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.
<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.
Show your query result data dump (samples) and your cfchart statements that you have so far.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
OK - I have figured out the legend, so that is working, but the dates are still random.
ASKER
Any ideas on the sorting problem?
ASKER
Cannot sort dates into correct order despite query sorted on date
Did you ever find a solution to the dates appearing in random order? I am having the same problem..
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.
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.
ASKER
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="#QoQExecutive s.Executiv e#">
</cfchartseries>
</cfloop>
</cfchart>
Graph.jpg
<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.
ORDER BY WEnded, Executive
</CFQUERY>
<cfchartseries query="QoQ_Qty"
type="line" itemColumn="WEnded" valueColumn="TotQty"
seriesLabel="#QoQExecutive
</cfchartseries>
</cfloop>
</cfchart>
Graph.jpg
You closed this question with a grading B and want now additional support???
Open a new question and perhaps somebody will look into it ;-)
Open a new question and perhaps somebody will look into it ;-)
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(getValue s.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.
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(getValue
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.
No problem ;-)
ASKER
sortXAxis has fixed the sorting problem - thanks gdemaria!