#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3

    Cfchart labels disappear with multiple line charts


    I'm running the following query and after running the QoQ for SECONDCONN , I am not getting the desired output in my cfchart.
    Code:
        <!--- QoQ for FIRSTCONN --->
        <!--- Master Query --->
        <cfquery datasource = "XX.XX.X.XX" name="master1">
             SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as FIRSTCONN
                    , COUNT(Timedetail) as FIRSTOccurances
                    , EVENTS 
             FROM  MyDatabase
             WHERE EVENTS = "FIRST" 
             GROUP BY FIRSTCONN ;
        </cfquery> 
    
        <!--- Detail Query --->
        <!--- <cfdump var = "#master#"> --->
        <cfquery dbtype="query" name="detail1">
            SELECT  *
            FROM master1 
            WHERE FIRSTCONN  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar"> 
            AND   FIRSTCONN  <  <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">;
        </cfquery>  
        
        
        <!--- QoQ for SECONDCONN --->
        <!--- Master Query --->
        <cfquery datasource = "XX.XX.X.XX" name="master2">
            SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as FIRSTCONN
                   , COUNT(Timedetail) as SECONDOccurances
                   , EVENTS 
            FROM  MyDatabase
            WHERE EVENTS = "SECOND" 
            GROUP BY SECONDCONN ;
        </cfquery> 
    
        <!--- Detail Query --->
        <!--- <cfdump var = "#master#"> --->
        <cfquery dbtype="query" name="detail2">
            SELECT  *
            FROM   master2 
            WHERE  SECONDCONN  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar"> 
            AND    SECONDCONN  <  <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">;
        </cfquery>  
        
        
        <cfchart format="flash"  chartwidth="1000" chartheight="500" scalefrom="0" scaleto="50000" xAxisTitle="Dates" yaxistitle="Number of Connections">
             <cfchartseries  query="detail1" type="line" itemColumn="FIRSTCONN" valueColumn="FIRSTOccurances" > 
             <cfchartseries  query="detail2" type="line" itemColumn="SECONDCONN" valueColumn="SECONDOccurances" > 
             </cfchartseries>
        </cfchart>

    On X Axis, the chart is displaying the correct `startdate` but it stops displaying the dates after middle point somewhere and rest of the line chart is displayed without any dates mentioned on the x-axis. What could be the reason? The dates are displaying properly when I ran just one query, that is QoQ for `FIRSTCONN`.



    Image #1[1] attached shows the output with only one query , that is QoQ for `FIRSTCONN`


    Image #2[2] is for the both and showing undesirable output.


    Please let me know if I can answer more questions.

    [1]: http://i.stack.imgur.com/vx4W6.jpg
    [2]: http://i.stack.imgur.com/RXfwD.jpg
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    Have you dumped the second query and the second QofQ to ensure the data is correct and the dates match with the first?

    It also might not hurt to drop the Flash chart and switch to the HTML format. I really have no idea if they've even maintained the Flash charting at all.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3
    Originally Posted by kiteless
    Have you dumped the second query and the second QofQ to ensure the data is correct and the dates match with the first?

    It also might not hurt to drop the Flash chart and switch to the HTML format. I really have no idea if they've even maintained the Flash charting at all.

    Yes, I have tested all the queries by using cfdump. They are producing correct results. The only problem is that it starts displaying x-axis labels. I'll try switching to HTML.

    By the way, what do you mean by that you have no idea if they've even maintained the Flash charting at all.?

    And do you see any problem with my QoQ?
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    I don't see a problem with your QofQ, but if the only way to know for sure is to dump them and inspect the data they contain, which you said you've already done.

    The Flash charting has been bypassed by HTML charting, since there's no reason to use Flash for this sort of thing any more.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3
    Originally Posted by kiteless
    I don't see a problem with your QofQ, but if the only way to know for sure is to dump them and inspect the data they contain, which you said you've already done.

    The Flash charting has been bypassed by HTML charting, since there's no reason to use Flash for this sort of thing any more.

    Okay, I tried removing flash but still it didn't work. Another point I noticed is that when I run the query for only SECONDCONN, I see an output without any dates mentioned on the x-axis. What could be th reason? I have attached ![image #3][3] below for the reference.there is no change in the query.

    The cfchart code I'm using is as follows:
    Code:
        <cfchart format="flash"  
                  chartwidth="1000" 
                  chartheight="500" 
                  scalefrom="0" 
                  scaleto="50000" 
                  xAxisTitle="Date" 
                  yaxistitle="Number of Connections" 
                  showLegend = "yes" 
                  showMarkers = "yes"
                  sortXAxis= "yes"
                  tipStyle="MouseDown"
                  >
                 
                
              
                 <cfchartseries  query="detail2" type="line" itemColumn="SECONDCONN " valueColumn="SECONDOccurances" >
    </cfchartseries>
    </cfchart>
    Please find the updated images below. I have added an image 3 for the query with only SECONDCONN.

    [1]: http://i.stack.imgur.com/xDpCw.jpg
    [2]: http://i.stack.imgur.com/RXfwD.jpg
    [3]: http://i.stack.imgur.com/qYQVY.jpg
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    Take a screenshot of the query dump:

    <cfdump var="#detail2#"><cfabort>
  12. #7
  13. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as FIRSTCONN

    The column needs to be named SECONDCONN, not FIRSTCONN.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3
    Originally Posted by kiteless
    SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as FIRSTCONN

    The column needs to be named SECONDCONN, not FIRSTCONN.
    Sorry for the typo. I think I just did this mistake while copying pasting here.

    Please find the cfdump of detail2 here for date range 6/24-7/17
    Code:
    query
    RESULTSET 	
    query
      	SECONDCONN 	              SECONDOCCURANCES   EVENTS
    1 	{ts '2013-06-24 00:00:00'} 	556 			SECOND
    2 	{ts '2013-06-25 00:00:00'} 	2710 			SECOND
    3 	{ts '2013-06-26 00:00:00'} 	2854 			SECOND
    4 	{ts '2013-06-27 00:00:00'} 	6348 			SECOND
    5 	{ts '2013-06-28 00:00:00'} 	4285 			SECOND
    6 	{ts '2013-06-29 00:00:00'} 	2843 			SECOND
    7 	{ts '2013-06-30 00:00:00'} 	875 			SECOND
    8 	{ts '2013-07-01 00:00:00'} 	4033 			SECOND
    9 	{ts '2013-07-02 00:00:00'} 	3211 			SECOND
    10 	{ts '2013-07-03 00:00:00'} 	2882 			SECOND
    11 	{ts '2013-07-04 00:00:00'} 	978 			SECOND
    12 	{ts '2013-07-05 00:00:00'} 	1727 			SECOND
    13 	{ts '2013-07-06 00:00:00'} 	811 			SECOND
    14 	{ts '2013-07-07 00:00:00'} 	522 			SECOND
    15 	{ts '2013-07-08 00:00:00'} 	2556 			SECOND
    16 	{ts '2013-07-09 00:00:00'} 	1160 			SECOND
    17 	{ts '2013-07-10 00:00:00'} 	8580 			SECOND
    18 	{ts '2013-07-11 00:00:00'} 	2630 			SECOND
    19 	{ts '2013-07-16 00:00:00'} 	12 	     		SECOND
  16. #9
  17. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    hmm, I'm really not sure then. I could be an issue with the format of the date value that you're trying to use as the axis values. You might try selecting it so the dates are strings (like "2/15/2013") rather than the ODBC date values.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3
    Originally Posted by kiteless
    hmm, I'm really not sure then. I could be an issue with the format of the date value that you're trying to use as the axis values. You might try selecting it so the dates are strings (like "2/15/2013") rather than the ODBC date values.
    I'm wondering why after using STR_TO_DATE(), the date format is not displaying as per the format I have mentioned. When I removed the function, it worked fine, I mean whatever date format I assign, it comes out accordingly. But there is no change in the issue of labels disappearing.
  20. #11
  21. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    I'm pretty much at a loss then. From what you're showing, it looks like it should work. Especially if you can compare the first and second QofQ and they both look the same (aside from the data itself).

    If they have identical structure, and the first one works but the second one doesn't, I'm pretty much left with assuming it's some sort of bug in the Flash-based charting. As I said, the Flash version of the charting is quite old. All the work Adobe is doing with charting in CF is now done via HTML, using WebCharts as the underlying engine.

    Edit: By the way, which version of CF are you using?
    Last edited by kiteless; July 23rd, 2013 at 12:23 AM.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3
    Originally Posted by kiteless
    I'm pretty much at a loss then. From what you're showing, it looks like it should work. Especially if you can compare the first and second QofQ and they both look the same (aside from the data itself).

    If they have identical structure, and the first one works but the second one doesn't, I'm pretty much left with assuming it's some sort of bug in the Flash-based charting. As I said, the Flash version of the charting is quite old. All the work Adobe is doing with charting in CF is now done via HTML, using WebCharts as the underlying engine.

    Edit: By the way, which version of CF are you using?
    I see. I'm using CF 08. I'll test without flash then.
  24. #13
  25. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    That may also be part of the problem. CF 8 is over six years old, so it's definitely using a very old version of the charting engine. Which unfortunately means even switching to HTML may not solve the problem.

IMN logo majestic logo threadwatch logo seochat tools logo