cfchart help

[emmim44]

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>

[Adam Cameron.]

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

[emmim44]

Please see the attached image... It doesnt do what I need... I need some guidance..

[Adam Cameron.]

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

[emmim44]

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.

[Adam Cameron.]

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

[-__cfSearching__-]

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.

[emmim44]

i tried scale from to , and it doesnt do scale of 1-31...

[-__cfSearching__-]

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>

[Adam Cameron.]

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

[-__cfSearching__-]

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...