Need Help in displaying Line Chart

Question

Please consider the following code:

Parameters I have used :

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

<cfquery datasource = "XX.XX.X.XX" name="qQuery">


SELECT Timestamp , Count(*) as 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"> GROUP BY Timestamp;
</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):

<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="Timestamp" valueColumn="COUNT"  query="qQuery">

        </cfchartseries>
</cfchart>

Problem I'm getting facing:

Nothing is displayed on the web browser except cfform fields. :(

When I right click on it, it says Movie not loaded.

Please let me know if there are some questions I can answer.

So what's the question here: how to write a SQL statement that returns more than one row? You've got three different things here: a form (which, I think, is mostly irrelevant to the question); some SQL; a chart. If you don't know how to do any of the parts of the sum, then focus on each part separately until you've got a handle on them, and then work out how to interconnect them. Let's ignore the form, but can you write the SQL that returns the data you need? Say you have that data (mock it up), can you create a chart that displays it? If "no" to either of those: explain your difficulties. — Adam Cameron, Jul 15 '13 at 07:21
@AdamCameron Sorry for the confusion. I have updated the SQL Query I'm using now in my post above. I'm displaying it in the chart as follows: `` 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: (Please see my next comment below for the error) — Tan, Jul 15 '13 at 15:34
`"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: ` ` 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. — Tan, Jul 15 '13 at 15:34
I checked the SQL query by harcoding all formats of dates but still ended up getting the same error. I believe there is some other reason for the error? — Tan, Jul 15 '13 at 16:17
When you respond with these updates, update the QUESTION, not a comment. Makes it easier to read, and not everyone will read the comments. — Adam Cameron, Jul 15 '13 at 17:34
The COMPILER won't be misinterpreting anything, cos `#qQuery.Timestamp#` doesn't even exist until runtime. Are those two `` tags in your code a typo? I'd expect *that* to give you a compile error. Which makes me think you've not actually posting the code you're using? Is your query returning multiple results now? If so, you need to pass the query to the `` so CF knows to use the whole thing, not just the first row when you give it to ``. It sounds to me like you might need to read the docs a bit, and try the sample code to get up to speed with charts. — Adam Cameron, Jul 15 '13 at 17:38
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/33515/discussion-between-tan-and-adam-cameron) — Tan, Jul 15 '13 at 21:13

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Error Occurred While Processing Request For input string: "2013-07-11".

<cfchartseries itemColumn="COUNT" valueColumn="Timestamp" ...>

The Y-Axis value should be numeric. The string "2013-07-11" is obviously not numeric, hence the error. Looks like you accidentally swapped itemColumn and valueColumn.

Update:

The chart code posted works fine with date strings and numeric counts (once you swap item/value).

<cfset qQuery = queryNew("")>
<cfset queryAddColumn(qQuery, "TimeStamp", "date", listToArray("2013-07-30,2013-08-01,2013-08-02"))>
<cfset queryAddColumn(qQuery, "Count", "integer", listToArray("10,8,6"))>

<cfchart format="flash" chartwidth="500" chartheight="500" showxgridlines="no">
    <cfchartseries type="line" 
        itemColumn="Timestamp" 
        valueColumn="Count"  
        query="qQuery" />
</cfchart>

So if it is not working for you, you are doing something different in the code than what you have shown us. Please update your question with a small, self-contained, example that demonstrates the problem. Also "not working" is very vague description. You need to tell what IS happening and how it differs from what you expected. ie Actual versus expected results.

Thanks for pointing that out. But after changing the values, nothing is displayed on the Web Browser except `cfform` fields. Could you please tell me what may be the reason? Also,where it is mentioned that itemColumn is meant for Y-Axis? — Tan, Jul 15 '13 at 22:22

Need Help in displaying Line Chart

1 Answers1