Graphing multiple sets of data

Forum|Forum|17 years ago
August 4, 2008
3 replies
629 views

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>

Advanced techniques

This topic has been closed for replies.

D

Dan_Bracuk

Inspiring

Put an index attribute in your cfloop tag.

Use it in the where clause of your query.

A

Anonymous

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>

A

Anonymous

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>

D

Dan_Bracuk

Inspiring

Your Q of Q needs a where clause.

A

Anonymous

i put in a where statement but no difference.


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

D

Dan_Bracuk

Inspiring

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

A

Anonymous

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>

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded