8 Replies Latest reply on Aug 12, 2008 3:36 AM by harpso

    Graphing multiple sets of data

    harpso
      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>
        • 1. Re: Graphing multiple sets of data
          Dan Bracuk Level 5
          <cfchart>
          <cfloop query="yourquery">
          <cfquery dbtype="query">
          <cfchartseries>
          closing tags
          • 2. Re: Graphing multiple sets of data
            harpso Level 1
            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>
            • 3. Re: Graphing multiple sets of data
              Dan Bracuk Level 5
              Your Q of Q needs a where clause.
              • 4. Re: Graphing multiple sets of data
                harpso Level 1
                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.
                • 5. Re: Graphing multiple sets of data
                  Dan Bracuk Level 5
                  Put an index attribute in your cfloop tag.

                  Use it in the where clause of your query.
                  • 6. Re: Graphing multiple sets of data
                    harpso Level 1
                    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>
                    • 7. Re: Graphing multiple sets of data
                      harpso Level 1
                      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>
                      • 8. Re: Graphing multiple sets of data
                        harpso Level 1
                        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?