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

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3

    Need Help in Displaying Line chart


    Please consider the following code:

    Parameters I have used :

    Code:
    <cfparam name="Form.startdate" default="#dateformat(now()-5, 'mm/dd/yyyy')#">
    <cfparam name="Form.enddate" default="#dateformat(now()-1, 'mm/dd/yyyy')#">
    <cfparam name="Form.selectdate" default="#dateformat(now(), 'mm/dd/yyyy')#">
    The cfquery I'm using is as follows:

    Information:
    MyDatabase = Name of the Database:
    Events = Column name with several different events(First,Second,Third etc).In this case I have just included FIRST. -
    Timestamp = Name of the column which contains date and time.
    Code:
    <cfquery datasource = "XX.XX.X.XX" name="qQuery">
    SELECT    (SELECT count(*) 
              FROM MyDatabase) AS TOTAL_CONNECTIONS,
                              (SELECT count(*)
                              FROM MyDatabase 
                              WHERE Events = "FIRST"  AND Timestamp >= <cfqueryparam value="#form.startDate#" cfsqltype="cf_sql_date"> AND Timestamp <  <cfqueryparam value="#dateAdd('d', 1, form.endDate)#" cfsqltype="cf_sql_date"> ) AS FIRST_CONNECTIONS;
    </cfquery>
    The above query is just displaying one dot in the line chart which is obvious because I have used there count clause with condition for Events = FIRST.

    The way I'm displaying is as follows (Please consider the following code):

    Code:
    <cfform format="flash" preloader ="false">
    
    <cfformgroup type="horizontal">
      <cfinput type="dateField" name="startdate" label="Start Date" width="100" value="#form.startdate#">
      <cfinput type="dateField" name="enddate" label="End Date" width="100" value="#Form.enddate#">
      <cfinput name="submitApply" type="submit" value = "Apply">
      <cfinput name="cancel" type="submit" value="Download CSV">
    
    </cfformgroup>
    
    <cfformitem type = "hrule" style="" ></cfformitem>
    </cfform>
    <cfif Isdefined("form.submitApply")>
    <cfchart format="flash" chartwidth="500" chartheight="500" scalefrom="0" scaleto="2500" showxgridlines="no"  >
             
            <cfchartseries type="line"> 
            <cfchartseries type="line">
            <cfchartdata item=" FIRST" value="#qQuery.FIRST_CONNECTIONS#"> 
    		</cfchartseries>
    </cfchart> 
    </cfif>
    My Goal:

    To display the data gathered in FIRST_CONNECTIONS from the above SQL query over the date range (Startdate to Enddate). I've spent many hours in figuring out
    this but still unable to do so. I'm new to coldfusion ,and therefore any hint regarding how to display the data will be highly appreciated.

    Please let me know if there are some questions I can answer.
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,273
    Rep Power
    968
    I don't really follow because I don't see anything related to the FIRST_CONNECTIONS query you're talking about. What does the query data in this query look like?
  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
    I don't really follow because I don't see anything related to the FIRST_CONNECTIONS query you're talking about. What does the query data in this query look like?
    Sorry for the confusion. Here is the modified query which I'm using:

    Code:
    SELECT Timestamp 
    FROM MyDatabase WHERE Events = "FIRST"
    AND Timestamp >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> 
    AND Timestamp <=  <cfqueryparam value="#dateAdd('d', 1, form.enddate)#" cfsqltype="cf_sql_date">;
    I'm displaying it in the chart as follows:

    Code:
    <cfchartdata item="FIRST" value="#qQuery.Timestamp#">
    The default start date I'm using here is: 07/10/2013 and End date : 07/14/2013 .

    I'm getting the following error after running my file in the browser:

    "Error Occured While Processing Request For input string: "2013-07-10" . The line number pointed by the compiler is the line where the following
    piece of code is written:


    Code:
    <cfchartdata item="FIRST" value="#qQuery.Timestamp#">

    Am I getting the error because the default date format is "07/10/2013" and the compiler might be interpreting it as "2013-07-10". Please let me know.
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,273
    Rep Power
    968
    hmm that really shouldn't matter. You should be able to make the value anything you want, as far as I know.

    While CF tries to show you the correct line number for the error, it is sometimes off a bit. My first guess is that the error actually isn't happening on this line. Try adding a cfabort just before the cfchart tag and rerun the page. If you still get the error, you know it's actually happening before the cfchart. If you don't get the error, then it would mean it actually is happening in the chart and we'll have to keep digging.

    Also: if you manually type a date into the cfchartdata tag instead of using #qQuery.timestamp#, does it still fail?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3

    Updated the Code and Question:


    Please consider the following code:

    Parameters I have used :
    Code:
        <cfparam name="Form.startdate" default="#dateformat(now()-5, 'mm/dd/yyyy')#">
        <cfparam name="Form.enddate" default="#dateformat(now()-1, 'mm/dd/yyyy')#">
        <cfparam name="Form.selectdate" default="#dateformat(now(), 'mm/dd/yyyy')#">
    The cfquery I'm using is as follows:

    Information:
    MyDatabase = Name of the Database

    Events = Column name with several different events(First,Second,Third etc).In this case I have just included FIRST.

    Timestamp = Name of the column which contains date and time.
    Code:
        <cfquery datasource = "XX.XX.X.XX" name="qQuery">
       
    
         SELECT Timestamp , Events
        FROM MyDatabase WHERE Events = "FIRST"
        AND Timestamp >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> 
        AND Timestamp <=  <cfqueryparam value="#dateAdd('d', 1, form.enddate)#" cfsqltype="cf_sql_date">;
        </cfquery>
    The above query is just displaying one dot in the line chart which is obvious because I have used there count clause with condition for Events = FIRST.

    The way I'm displaying is as follows (Please consider the following code):

    Code:
       <cfform format="flash" preloader ="false">
        
        <cfformgroup type="horizontal">
          <cfinput type="dateField" name="startdate" label="Start Date" width="100" value="#form.startdate#">
          <cfinput type="dateField" name="enddate" label="End Date" width="100" value="#Form.enddate#">
          <cfinput name="submitApply" type="submit" value = "Apply">
          <cfinput name="cancel" type="submit" value="Download CSV">
        
        </cfformgroup>
        
        <cfformitem type = "hrule" style="" ></cfformitem>
        </cfform>
        
        <cfchart format="flash" chartwidth="500" chartheight="500" scalefrom="0" scaleto="2500" showxgridlines="no"  >
                 
                
                <cfchartseries type="line" itemColumn="EVENTS" valueColumn="Timestamp"  query="qQuery">
                
        		</cfchartseries>
        </cfchart>

    My Goal:

    To display the data gathered in Timestamp and Events from the above SQL query over the date range (Startdate to Enddate). I've spent many hours in figuring out
    this but still unable to do so. I'm new to coldfusion ,and therefore any hint regarding how to display the data will be highly appreciated.

    Please let me know if there are some questions I can answer.
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,273
    Rep Power
    968
    OK, and now with the use of the chartseries tag specifying the query name and the columns, what is the error now?

    As an aside, when asking a question, ALWAYS include as much information about the error as you can please.

IMN logo majestic logo threadwatch logo seochat tools logo