Copy link to clipboard
Copied
I need to graph on y-axsis (Day 1-Day31 of month) on the x-axsis the code of metrics...and their progresses...
Lets say, a metric called "AHT" has the max data till 14 th of the month so the graph bar will be till 14th of the month...
Any help appreciated....
<cfquery name="GetChartData" datasource="#application.DSN_ORC#">
SELECT MAX( s.end_date ) AS enddate, m.sc_metric_code as metriccode
FROM ops$rms.resource_score s, ops$rms.ref_metric m
WHERE s.metric_code = m.metric_code
AND EXTRACT( MONTH FROM s.end_date ) = '#Form.Month#'
AND EXTRACT( YEAR FROM s.end_date ) = '#Form.Year#'
GROUP BY s.end_date, m.sc_metric_code
</cfquery>
<cfchart
format="png"
xaxistitle="Progress"
yaxistitle="Date"
chartheight="400"
chartwidth="700"
foregroundcolor="FFFAFA"
backgroundcolor="BC8F8F"
fontbold="yes"
show3d="yes"
databackgroundcolor="DDA0DD"
font="Verdana"
fontsize="13"
showxgridlines="no"
showygridlines="yes"
labelformat="date"
>
<cfchartseries
type="bar"
seriescolor="8B4513"
paintstyle="light" query="GetChartData" itemcolumn="metriccode" valuecolumn="EndDate"
>
</cfchartseries>
</cfchart>
Copy link to clipboard
Copied
I need to graph on y-axsis (Day 1-Day31 of month) on the x-axsis the code of metrics...and their progresses...
Lets say, a metric called "AHT" has the max data till 14 th of the month so the graph bar will be till 14th of the month...
Any help appreciated...
What is the actual question here? You've got the "I am trying to do this..." part down. But there's not "and [something has happened which I did not expect]" part seems to be missing...
--
Adam
Copy link to clipboard
Copied
Copy link to clipboard
Copied
OK. One thing to consider here is that <cfchart> can only handle numbers for values, not dates. Now, while a date will be cast to a number, it's not probably going to be the sort of number you want reflected here.
I suspect you'd get the sort of results you are after if you just chart the value of the day of the month (which will be your 1-31), rather than the actual date.
So basically you want your record set to have a row for each metric and a day-of-month value, and chart that.
Nice colour scheme on that chart, btw 😉
--
Adam
Copy link to clipboard
Copied
Now the problem is y axis shows from 0-35 even though...I need to show from 1-31 and data of each bar must show the date not number...Please see the attached doc.
my query:
<cfquery name="GetChartData" datasource="#application.ORC#">
SELECT b.enddate,
a.metriccode
from
(
SELECT m.sc_metric_code as metriccode
FROM ops$rms.ref_metric m
WHERE m.sc_metric_code is not null
) a,
(
SELECT EXTRACT( DAY FROM MAX( s.end_date)) AS enddate,
m.sc_metric_code as metriccode
FROM ops$rms.resource_score s, ops$rms.ref_metric m
WHERE s.metric_code = m.metric_code
AND EXTRACT( MONTH FROM s.end_date ) = '#Form.Month#'
AND EXTRACT( YEAR FROM s.end_date ) = '#Form.Year#'
GROUP BY s.end_date, m.sc_metric_code
) b
where a.metriccode = b.metriccode (+)
Order by b.metriccode
</cfquery>
--------
<cfchart
format="jpg"
xaxistitle="Progress"
yaxistitle="Date"
chartheight="400"
chartwidth="700"
foregroundcolor="FFFAFA"
backgroundcolor="BC8F8F"
fontbold="yes"
show3d="yes"
databackgroundcolor="DDA0DD"
font="Verdana"
fontsize="9"
showxgridlines="no"
showygridlines="yes"
labelformat="number"
sortXAxis="yes"
>
<cfchartseries
type="bar"
seriescolor="8B4513"
paintstyle="light"
query="GetChartData" itemcolumn="metriccode" valuecolumn="enddate">
</cfchartseries>
</cfchart>
Thank you all.
Copy link to clipboard
Copied
You might want to try to design your chart with the WebCharts3D app that ships with CF. It has a lot more options than <cfchart>
It's in the [ColdFusion]/charting dir.
I've never used it (I didn't even know it existed until someone else pointed me at it), so I can't offer any advice there... someone else might though. It looks pretty straight forward though.
If you get it cracked, pls report back and let us know how you did it.
--
Adam
Copy link to clipboard
Copied
The webcharts3D utility is a great tool. But before you go that route, did you try simply setting the scale via cfchart's scaleFrom/scaleTo attributes? I do not know if those are the exact attribute names. But cfchart does have settings for scale.
Copy link to clipboard
Copied
i tried scale from to , and it doesnt do scale of 1-31...
Copy link to clipboard
Copied
I seem to remember something about having to supply a "gridlines" value as well to see the effect. However, since your max scale is an uneven number (ie 31) you will probably end up with partial values on the Y-axis, like 4.832 days. You will need to use the webcharts utility to format the Y-Axis values as integers. In particular the Design Tab -> YAxis section in the webcharts utility.
Here is a simple example that shows the YAxis values formatted as integers. The two lines that control the Y-Axis formatting are in blue.
<cfsavecontent variable="style"><?xml version="1.0" encoding="UTF-8"?>
<?xml version="1.0" encoding="UTF-8"?>
<frameChart is3D="false">
<frame xDepth="12" yDepth="11"/>
<yAxis scaleMin="1" scaleMax="31" labelCount="10">
<labelFormat style="Integer" pattern="#,##0"/>
<parseFormat pattern="#,##0.###"/>
<groupStyle>
<format pattern="#,##0.###"/>
</groupStyle>
</yAxis>
</frameChart>
</cfsavecontent>
<cfchart format="jpg" style="#style#">
<cfchartseries type="bar" query="GetChartData" itemcolumn="metriccode" valuecolumn="enddate">
</cfchartseries>
</cfchart>
Copy link to clipboard
Copied
With the help from an entry from your own blog - http://cfsearching.blogspot.com/2008/10/silly-cfchart-tweaks.html - I was able to tweak your example to put "nicer" values on the Y-axis labels:
<yAxis scaleMin="0" scaleMax="35" labelCount="8">
<labelFormat style="choice" pattern="0#0|5#5|10#10|15#15|20#20|25#25|30#30|35# "/>
This goes back to scaling the axis all the way up to 35, but dispenses with the label for "35". So the chart still has room for a 31, but doesn't suggest it's possible to have dates within the month of 32-35 as well.
--
Adam
Copy link to clipboard
Copied
Interesting approach 😉 But I think choice may skew the tooltip values. There is probably a way around that. But it seems like accepting slightly uneven numbered y-axis values may be easier. At least they are integers, though not as uniform as 0,5,10, etc...