Graphing multiple sets of data

[Guest]

I am running an auction site and wish to display the bids for each supplier in a line graph. the following code will display just one line on the graph. i need it to show a different line (with different color) for each supplier and their bids. is there a way to group by in cfchart? or do i need to do a cfloop query?

<CFQUERY NAME="GetChartData" DATASOURCE="#Application.Datasource#">
SELECT Bids.BidID, Bids.SupplierID, Bids.AuctionID, Bids.Bid, Bids.Bid_Date, Suppliers.Company
FROM Bids INNER JOIN Suppliers ON Bids.SupplierID = Suppliers.SupplierID
WHERE Bids.AuctionID=<CFQUERYPARAM VALUE="#URL.AuctionID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">
ORDER BY Bids.Bid_Date ASC;
</CFQUERY>

<cfchart format="flash" chartheight="400" chartwidth="500" scalefrom="0" scaleto="50" seriesplacement="default" showborder="yes" labelformat="number" xaxistitle="Date/Time" yaxistitle="Bid Amounts" tipstyle="mouseOver">

<cfchartseries type="line" query="GetChartData" itemcolumn="Bid_Date" valuecolumn="Bid" serieslabel="E-Auction" seriescolor="FF00FF" markerstyle="circle" />

</cfchart>

[Dan_Bracuk]

<cfchart>
<cfloop query="yourquery">
<cfquery dbtype="query">
<cfchartseries>
closing tags

[Guest]

i've tried the following but i get a really thick red line - not separate lines of different colors. plus the whole page takes ages to fully load as well.

<cfchart format="png" chartheight="400" chartwidth="500" scalefrom="0" scaleto="50" seriesplacement="default" showborder="yes" labelformat="number" xaxistitle="Date/Time" yaxistitle="Bid Amounts" tipstyle="mouseOver">

<cfloop query="GetChartData">

<cfquery dbtype="query" name="GetChartData2">
select * from GetChartData
</cfquery>

<cfchartseries type="line" colorlist="purple,blue,green,yellow,gray,silver,teal" query="GetChartData2" itemcolumn="Bid_Date" valuecolumn="Bid" seriesColor="red" serieslabel="E-Auction" markerstyle="circle" />

</cfloop>
</cfchart>

[Dan_Bracuk]

Your Q of Q needs a where clause.

[Guest]

i put in a where statement but no difference.

<!--- Query to find Data for the Chart --->
<CFQUERY NAME="GetChartData" DATASOURCE="#Application.Datasource#">
SELECT Bids.BidID, Bids.SupplierID, Bids.AuctionID, Bids.Bid, Bids.Bid_Date, Suppliers.Company
FROM Bids INNER JOIN Suppliers ON Bids.SupplierID = Suppliers.SupplierID
WHERE Bids.AuctionID=<CFQUERYPARAM VALUE="#URL.AuctionID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">
ORDER BY Bids.Bid_Date ASC;
</CFQUERY>

<cfchart format="png" chartheight="400" chartwidth="500" scalefrom="0" scaleto="50" seriesplacement="default" showborder="yes" labelformat="number" xaxistitle="Date/Time" yaxistitle="Bid Amounts" tipstyle="mouseOver">

<cfloop query="GetChartData">

<cfquery dbtype="query" name="GetChartData2">
select * from GetChartData
where SupplierID IN (<CFQUERYPARAM VALUE="#GetAuctions.Suppliers#" LIST="YES" CFSQLTYPE="CF_SQL_VARCHAR">)
</cfquery>

<cfchartseries type="line" colorlist="purple,blue,green,yellow,gray,silver,teal" query="GetChartData2" itemcolumn="Bid_Date" valuecolumn="Bid" seriesColor="red" serieslabel="E-Auction" markerstyle="circle" />

</cfloop>
</cfchart>

GetAuctions.Suppliers is a comma delimited list of the Supplier ID's.

[Dan_Bracuk]

Put an index attribute in your cfloop tag.

Use it in the where clause of your query.

[Guest]

I had to leave this for a few days but I have just tried the following code and it works to the extent that there is a different coloured line for each supplier on the chart but it seems that each supplier has its own time axis. I need it to show one time axis on the bottom, with all bids, regardless of supplier plotted along that, with each individual supplier uniquely identified by a colour.

<CFQUERY NAME="GetChartData" DATASOURCE="#Application.Datasource#">
SELECT Bids.BidID, Bids.SupplierID, Bids.AuctionID, Bids.Bid, Bids.Bid_Date, Suppliers.Company
FROM Bids INNER JOIN Suppliers ON Bids.SupplierID = Suppliers.SupplierID
WHERE Bids.AuctionID=<CFQUERYPARAM VALUE="#URL.AuctionID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">
ORDER BY Bids.Bid_Date ASC;
</CFQUERY>

<cfset Suppliers=ValueList(GetSuppliers.SupplierID)>

<cfchart chartheight="325" chartwidth="475" format="png" font="Arial" fontbold="Yes" scalefrom="0" scaleto="50" seriesplacement="default" showborder="yes" labelformat="number" xaxistitle="Date/Time" yaxistitle="Bid Amounts" tipstyle="mouseOver">

<cfloop index="ListSuppliers" list="#Suppliers#">

<cfquery dbtype="query" name="GetChartData2">
select * from GetChartData
where SupplierID='#ListSuppliers#'
</cfquery>

<cfchartseries type="line" query="GetChartData2" itemcolumn="Bid_Date" valuecolumn="Bid" serieslabel="E-Auction"/>

</cfloop>
</cfchart>

[Guest]

I enabled sortxaxis as "yes" and it now works apart from the fact that the data points do not connect properly. it seems that they will connect until a different supplier puts a bid in. see example chart at: http://img166.imageshack.us/my.php?image=chartbidsxl8.jpg

<cfchart chartheight="325" chartwidth="475" format="png" font="Arial" fontbold="Yes" seriesplacement="default"
labelformat="number" showBorder="Yes" showLegend="Yes" showMarkers="Yes" sortxaxis="yes" xaxistitle="Date/Time" xaxistype="Category" yaxistitle="Bid Amount" tipstyle="mouseOver">

<CFLOOP INDEX="ListSuppliers" LIST="#Suppliers#">

<CFQUERY DBTYPE="query" NAME="GetChartData2">
SELECT * FROM GetChartData
WHERE SupplierID='#ListSuppliers#'
</CFQUERY>

<cfchartseries type="line" query="GetChartData2" itemcolumn="Bid_Date" valuecolumn="Bid"

serieslabel="#GetChartData2.Company#" markerstyle="circle" paintstyle="plain" />

</CFLOOP>
</cfchart>

[Guest]

As each supplier has placed different bids at different dates/times, basically each chart series is unrelated to the other ones. The only thing they have in common is the ranges of the x and y data. It seems that unless the date values line up in the different series, then the graph won't display the series properly. Is there a way around this? is it possible in a case like this to make the x-axis values the same in all the data sets?