I don't know what I'm doing

---ERROR---
Attribute validation error for the CFCHARTDATA tag.
The value of the VALUE attribute is invalid. The value cannot be converted to a numeric because it is not a simple value.Simple values are booleans, numbers, strings, and date-time values.

The error occurred in C:\Websites\182973ef2\adminRates.cfm: line 430
Called from C:\Websites\182973ef2\adminRates.cfm: line 404
Called from C:\Websites\182973ef2\adminRates.cfm: line 396
Called from C:\Websites\182973ef2\adminRates.cfm: line 394

428 : <cfset Dec=#monthlyRate[12].monthlyAmount#>
429 :
430 : <cfchartdata item="Jan" value=#Jan#>
431 : <cfchartdata item="Feb" value=#Feb#>
432 : <cfchartdata item="Mar" value=#Mar#>



---CODE---

<!--- SET LIST OF YEARS THAT EXIST IN DB --->
<cfquery name="getYears" datasource="pfrentals">
SELECT rentalYear
FROM rates
GROUP BY rentalYear
ORDER BY rentalYear
</cfquery>

<cfchart format="flash"
chartWidth="790"
chartheight="125"
showXGridlines="no"
showYGridlines="yes"
showmarkers="yes"
show3d="no"
font="arial"
fontsize="12"
fontbold="yes"
yaxistitle="Monthly Rates">

<cfoutput>

<!--- CREATE LIST OF EXISTING YEARS --->
<cfset yearList = ''>
<cfloop query="getYears"><cfset yearList = #ListAppend(yearList, rentalYear, ',')#></cfloop>

<cfchartseries type="bar" seriesColor="cc0000">

<cfloop from="1" to="#ListLen(yearList)#" index="z">
<!--- CREATE THE ARRAY --->
<cfloop from="1" to="12" index="loopCount">
<cfquery name="findRates" datasource="pfrentals">
SELECT rentalRate
FROM rates
WHERE rentalMonth = #loopCount#
AND rentalYear = #z#
</cfquery>
<cfset monthlyRate[loopCount]=structNew()>
<cfset monthlyRate[loopCount].monthlyAmount=#findRates.rentalRate#>
</cfloop>

<cfset Jan=#monthlyRate[1].monthlyAmount#>
<cfset Feb=#monthlyRate[2].monthlyAmount#>
<cfset Mar=#monthlyRate[3].monthlyAmount#>
<cfset Apr=#monthlyRate[4].monthlyAmount#>
<cfset May=#monthlyRate[5].monthlyAmount#>
<cfset Jun=#monthlyRate[6].monthlyAmount#>
<cfset Jul=#monthlyRate[7].monthlyAmount#>
<cfset Aug=#monthlyRate[8].monthlyAmount#>
<cfset Sep=#monthlyRate[9].monthlyAmount#>
<cfset Oct=#monthlyRate[10].monthlyAmount#>
<cfset Nov=#monthlyRate[11].monthlyAmount#>
<cfset Dec=#monthlyRate[12].monthlyAmount#>

<cfchartdata item="Jan" value=#Jan#>
<cfchartdata item="Feb" value=#Feb#>
<cfchartdata item="Mar" value=#Mar#>
<cfchartdata item="Apr" value=#Apr#>
<cfchartdata item="May" value=#May#>
<cfchartdata item="Jun" value=#Jun#>
<cfchartdata item="Jul" value=#Jul#>
<cfchartdata item="Aug" value=#Aug#>
<cfchartdata item="Sep" value=#Sep#>
<cfchartdata item="Oct" value=#Oct#>
<cfchartdata item="Nov" value=#Nov#>
<cfchartdata item="Dec" value=#Dec#>
</cfloop>

</cfchartseries>--->
</cfoutput>
</cfchart>

I think you are over complicating things a bit.

Just run a query to retrieve the rates you want, ordering by year and month. Then use the query values as your chartdata elements. You can use the MonthAsString(..) and Left(..) functions to get the month title from the RentalMonth value.



Though another option is to use your database's date functions to do all this in the sql query itself. Then you could use <cfchartseries query="..."> instead of individual <cfchartdata> elements.

Also, you should limit the query results to a small number of years. So the chart does not become unreadable ... or error out due to too many elements.

Trying your suggestion only displays the last year

So I worked the code around to this:
<!--- SET LIST OF YEARS THAT EXIST IN DB --->
<cfquery name="getYears" datasource="pfrentals">
SELECT rentalYear
FROM rates
GROUP BY rentalYear
ORDER BY rentalYear
</cfquery>
<cfset yearList = "">
<cfloop query="getYears"><cfset yearList = #listAppend(yearList, rentalYear, ',')#></cfloop>

<cfchart format="flash"
chartWidth="790"
chartheight="125"
showXGridlines="no"
showYGridlines="yes"
showmarkers="no"
show3d="no"
font="arial"
fontsize="12"
fontbold="yes"
yaxistitle="Monthly Rates">

<cfchartseries type="line">

<cfloop list="yearList" index="yr" delimiters=",">
<cfquery name="getRates" datasource="pfrentals">
SELECT rentalMonth, rentalYear, rentalRate
FROM rates
WHERE propertyID = #propertyID#
AND rentalYear = #yr#
ORDER BY RentalYear, RentalMonth
</cfquery>

<cfloop query="getRates">
<cfchartdata item="#Left(MonthAsString(RentalMonth), 3)#" value="#RentalRate#">
</cfloop>
</cfloop>

</cfchartseries>

</cfchart>

BUT... it doesn't like my query
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression 'propertyID = 1 AND rentalYear = 2009,2010'.

The error occurred in C:\Websites\182973ef2\adminRates.cfm: line 409

407 : FROM rates
408 : WHERE propertyID = #propertyID#
409 : AND rentalYear = #yearList#
410 : ORDER BY RentalYear, RentalMonth
411 : </cfquery>

No, get rid of all the nested loops and queries. All should you need is

1. One query, to get the data.
2. A single cfloop to generate the chart points.

Take a look at my example again. It is missing some properties, but the basic structure is there. Notice there is only a single loop, no nested queries or loops through #yearList#.



That error is not coming from the code you posted. But, the problem is #yearList# contains multiple values. You cannot use multiple values with the equality operator. You must use the keyword IN instead.

I still only get one year displayed

<cfquery name="getYears" datasource="pfrentals">
SELECT rentalYear
FROM rates
GROUP BY rentalYear
ORDER BY rentalYear
</cfquery>
<cfset yearList = "">
<cfloop query="getYears"><cfset yearList = #listAppend(yearList, rentalYear, ',')#></cfloop>

<cfquery name="getRates" datasource="pfrentals">
SELECT rentalMonth, rentalYear, rentalRate
FROM rates
WHERE propertyID = #propertyID#
AND rentalYear IN (#yearList#)
ORDER BY RentalYear, RentalMonth
</cfquery>

<cfchart format="flash"
chartWidth="790"
chartheight="125"
showXGridlines="no"
showYGridlines="yes"
showmarkers="no"
show3d="no"
font="arial"
fontsize="12"
fontbold="yes"
yaxistitle="Monthly Rates">

<cfchartseries type="bar">
<cfloop query="getRates">
<cfchartdata item="#Left(MonthAsString(RentalMonth), 3)#" value="#RentalRate#">
</cfloop>
</cfchartseries>
</cfchart>

Probably because you are charting the values by month, not month _and_ year, so cfchart lumps the values together. Try using month and year as your data label.



That code is really not necessary. If you want to select all years, just remove rentalYear from the WHERE clause.



BTW, after you get the chart figured out you should read up on cfqueryparam

The results still come up in a single series of data, but I need a cfchart series for each year

current code:

<cfquery name="getRates" datasource="pfrentals">
SELECT rentalMonth, rentalYear, rentalRate
FROM rates
WHERE propertyID = <cfqueryparam value="#propertyID#" cfsqltype="cf_sql_numeric">
ORDER BY RentalMonth, RentalYear
</cfquery>

<cfchart format="flash"
chartWidth="790"
chartheight="200"
showXGridlines="no"
showYGridlines="yes"
showmarkers="no"
show3d="no"
font="arial"
fontsize="12"
fontbold="yes"
yaxistitle="Monthly Rates">

<cfchartseries type="bar" colorlist="cc9966, 000099">
<cfloop query="getRates">
<cfchartdata item="#Left(MonthAsString(RentalMonth), 3)#, #rentalYear#" value="#RentalRate#">
</cfloop>
</cfchartseries>
</cfchart>

Yes, that is what the code is telling it to do. Take a look at cfoutput's group attribute. That would allow you to group the results by year, creating a separate series for each year.

here are my current results http://www.pfrentals.com/test.cfm
but this is not what I want

as you can see I'm getting 24 column groupings, I only want 12 (one for each month)
then above each month there should be a bar for each year's amount in that month

I appreciate your help, but obviously I'm just not getting it - I hardly ever work with charts

current code:
<cfquery name="getRates" datasource="pfrentals">
SELECT rentalYear, rentalMonth, rentalRate
FROM rates
WHERE propertyID = <cfqueryparam value="#propertyID#" cfsqltype="cf_sql_numeric">
ORDER BY RentalYear, RentalMonth
</cfquery>

<cfchart format="flash"
chartWidth="790"
chartheight="200"
showXGridlines="no"
showYGridlines="yes"
showmarkers="no"
show3d="no"
font="arial"
fontsize="12"
fontbold="yes"
yaxistitle="Monthly Rates">
<cfoutput query="getRates" group="rentalYear">
<cfchartseries type="bar" colorlist="cc9966, 000099">
<cfloop query="getRates">
<cfchartdata item="#Left(MonthAsString(RentalMonth), 3)#, #rentalYear#" value="#RentalRate#">
</cfloop>
</cfchartseries>
</cfoutput>
</cfchart>

Your cfoutput "group" is not quite right. Grouping requires at least two sets of <cfoutput> tags (not cfloop) and the tags _must_ be nested correctly or you will get the wrong results. It is very picky!



"Group" is a great feature, but it definitely ranks up there as one of the most misunderstood ones in the language.

Yuck. So much for using code tags. ..

no difference

<cfquery name="getRates" datasource="pfrentals">
SELECT rentalYear, rentalMonth, rentalRate
FROM rates
WHERE propertyID = <cfqueryparam value="#propertyID#" cfsqltype="cf_sql_numeric">
ORDER BY RentalYear, RentalMonth
</cfquery>

<cfchart format="flash"
chartWidth="790"
chartheight="200"
showXGridlines="no"
showYGridlines="yes"
showmarkers="no"
show3d="no"
font="arial"
fontsize="12"
fontbold="yes"
yaxistitle="Monthly Rates">
<cfoutput query="getRates" group="rentalYear">
<cfchartseries type="bar" colorlist="cc9966, 000099">
<cfoutput>
<cfloop query="getRates">
<cfchartdata item="#Left(MonthAsString(RentalMonth), 3)#, #rentalYear#" value="#RentalRate#">
</cfloop>
</cfoutput>
</cfchartseries>
</cfoutput>
</cfchart>

You are still not using cfoutput correctly. Look at the difference between your code and my example. It is all there but the cfchart properties!

still doesn't break it out correctly
See http://www.pfrentals.com/test.cfm

top chart is current results
bottom chart is an image of what I am looking for

code:
<cfquery name="getRates" datasource="pfrentals">
SELECT rentalYear, rentalMonth, rentalRate
FROM rates
WHERE propertyID = <cfqueryparam value="#propertyID#" cfsqltype="cf_sql_numeric">
ORDER BY RentalYear, RentalMonth
</cfquery>

<cfchart format="flash"
chartWidth="790"
chartheight="200"
showXGridlines="no"
showYGridlines="yes"
showmarkers="no"
show3d="no"
font="arial"
fontsize="12"
fontbold="yes"
yaxistitle="Monthly Rates">

<cfoutput query="getRates" group="rentalYear">
<cfchartseries type="bar" colorlist="cc9966, 000099">
<cfoutput>
<cfchartdata item="#Left(MonthAsString(RentalMonth), 3)#, #rentalYear#" value="#RentalRate#">
</cfoutput>
</cfchartseries>
</cfoutput>

</cfchart>

Oh, I see what you are saying. Since you are now using one series per year, you have remove the #rentalYear# from the data item value. Otherwise, the values are not grouped together.

<cfchartdata item="#Left(MonthAsString(RentalMonth), 3)#" value="#RentalRate#">