asked on

cfquery, cfchart, and cfchartseries help

Hi,
I'm trying to create a bar/line chart. I have to show values for last 5 months. I think i got the query and getting result. when i use <cfchart> and <cfchartseries> tags to pull up values on a chart i get 3-4 different graphs with the same values instead of showing values for each months on one graph. Also on the graph i only get 3 months values instead of 5 months.

<CFQUERY NAME="query_name" DATASOURCE="Datasource_name">
SELECT Wtr_Distr_DataEntry_Date, Wtr_Distr_Sys_Int_Rate from Wtr_Distribution_System_Integrity where
MONTH(Wtr_Distr_DataEntry_Date) > (month(getdate()) - 5)
</cfquery>

<cfchart chartheight="340" chartwidth="800" format="flash" font="ARIAL" foregroundcolor="WHITE"fontsize="18" databackgroundcolor = "BLACK" backgroundcolor="BLACK"
yaxistitle="Water Distr Sys Integrity" showygridlines="yes" showlegend="YES" xaxistitle="Months" scalefrom="0" scaleto="20" gridlines="5"
show3d="NO" seriesplacement="stacked">

<cfchartseries seriescolor="66CCFF" type="line" query="query_name" itemcolumn="Wtr_Distr_DataEntry_Date" valuecolumn="Wtr_Distr_Sys_Int_Rate">

</cfchartseries>

</cfchart>

Please help me with this.
Thanks - KD

kuntald

ASKER

hi,
i just figured out a way to put values in one graph but i'm still having a problem with the months. It still pulls up only 3 months even though i have 5 rows(months) in a table.

Thanks.
KD

kkhipple

simple things to try is first do a CFDUMP of what your query is bringing... that should tell you if ur getting info for 3 mths or 5... this way you can determine if the issue is with the query or with the info ur passing ot the chart

kuntald

ASKER

I'm not sure how to test with CFDUMP tag. can you show me the code? I tested a query in Query Analyzer and getting 5 months.

Thanks,
Kuntal

pinaldave

Hi kuntald,
are those month different or same... just curious...
is it like jan, feb, mar, apr
or like jan, jan, feb, mar, mar

Regards,
---Pinal

kuntald

ASKER

It's Jan, Feb, March ....

Thanks

wolffy18

to do a cfdump:

<cfdump var="#query_name#">

This will show you all records returned in a table format.

kuntald

ASKER

ok. this shows only 3 rows. so do i guess i need to change my query? the same query brings up 5 rows in query analyzer. I'm not sure how else can I change my query.

SELECT datename(month, convert(varchar(11), Wtr_Distr_DataEntry_Date, 101)) as month_1, datename(year, convert(varchar(11), Wtr_Distr_DataEntry_Date, 101)) as year_1,
Wtr_Distr_Sys_Int_Rate from Wtr_Distribution_System_Integrity_test where MONTH(Wtr_Distr_DataEntry_Date) > (month(getdate()) - 12)

Thanks.

kkhipple

is there 5mths of data available? or is there only 3 mths of data avail ?

im not thinking straight and having a look at your query ... so maybe play around with that

kuntald

ASKER

I have 5 months data in my table. I changed my query to select * from table and tested with <cfdump> and it still pull up 3 months of data.

kuntald

ASKER

I got it. I had to put Set Rowcount 5 then i can see all 5 rows. That's strange to me that if you select all shoud populate all rows. i think <cfquery> by default set to populate 3 rows only, i guess.

I have another question related to cfchart. is there a way i can show up values on the top of each bar?

Thanks for all your help!

ASKER CERTIFIED SOLUTION

pinaldave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial