Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

cfchart and cfchartdata question. Kindly help!

Status
Not open for further replies.

cfdeveloper

Programmer
Nov 20, 2003
144
GB
Hello everybody, I really need someone to help me here. I'm new to charting. I'm developing a graphical interface to report the no of jobs logged in a period. The x axis displays the period (days) and the y-axis displays the number of jobs logged in a given day.

The data is pulled from the jobs table. The job logged date value is stored in the logdateTime column. I've written a stored procedure to get the count of jobs logged in a given day. Here is the code.

<cfquery name="qJobsLogged" datasource="dbname">
EXEC countJobsLogged
@startdatedate = '2005/02/01',
@stopdate = '2005/02/28'
</cfquery>

<!-- sp code starts here -->
CREATE proc countJobsLogged @startdate datetime, @stopdate datetime

as

set nocount on

declare @x datetime,
@count int


create table #temp_report(
dtlogged varchar(15),
jobsCount int
)

select @x = @startdate

while @x <= @stopdate
begin
select @count = isnull(count(1),0) from call
where logdatetime >= @x + '00:00:00' and logdatetime <= @x + '23:59:59'

insert into #temp_report
values(convert(varchar(15),@x,106), @count)

select @x = dateadd(d, 1, @x)
end

select * from #temp_report

drop table #temp_report

set nocount off
GO
<!-- sp code ends here -->

The above sp loops from the user selected start date to the user selected end date and returns a resultset showing the no of jobs logged. Ex

JOBSCOUNT DTLOGGED
10 01 Feb 2005
20 02 Feb 2005
30 03 Feb 2005

Here is how I'm building the chart

<cfset scTo=evaluate(ArrayMax(ListToArray(valuelist(qJobsLogged.jobsCount)))+1)>

<cfchart show3d="yes" gridlines="#evaluate(scTo+1)#" labelformat="number" scalefrom="0" scaleto="#scTo#" format="jpg" xAxisTitle="log date" yAxisTitle="jobs logged" chartheight="300" chartwidth="450" showxgridlines="yes" showygridlines="yes" seriesplacement="default">
<cfchartseries type="bar" serieslabel="Jobs Logged">
<cfloop query="qJobsLogged">
<cfchartdata item="#qJobsLogged.DTLOGGED#" value="#qJobsLogged.jobsCount#">
</cfloop>
</cfchartseries>
</cfchart>

The above code builds the graph with the data returned by the query. So far, so good. I want each data point in the bar chart to give me a break-down of urgent jobs, critical jobs, normal jobs and jobs fixed on time.

I guess what I'm trying to say is take the results returned from the sp above and instead of just displaying a bar chart showing the count of jobs
logged in day, I want the chart to show me the urgent, critical, normal jobs for a particular day. I really hope this is making sense. The job priority information is in the jobPriority column in the job table.
Job Priorities

Urgent - 5
Critical - 4
Normal - 3

Ex:

Jobs logged on 01 Feb 2005 - 10.
Urgent jobs - 4, Critical jobs - 4, Normal Jobs - 2

Can somebody show me the sql to get this information and the code to build the chart

I would really appreciate your help

Best regards
cfcoder
 
<cfchart show3d="yes" gridlines="#evaluate(scTo+1)#" labelformat="number" scalefrom="0" scaleto="#scTo#" format="jpg" xAxisTitle="log date" yAxisTitle="jobs logged" chartheight="300" chartwidth="450" showxgridlines="yes" showygridlines="yes" seriesplacement="default">
<cfchartseries type="bar" serieslabel="Jobs Logged">
<cfloop query="qJobsLogged">
<cfchartdata item="#qJobsLogged.DTLOGGED#" value="#qJobsLogged.jobsCount#">
</cfloop>
</cfchartseries>
</cfchart>


you shouldn't use <cfloop> instead something like

<cfchartseries type="bar" query="qJobsLogged" valuecolumn="jobsCount" itemcolumn="Name_ofTheJob" serieslabel="urgent" >
</cfchartseries>
<cfchartseries type="bar" query="qJobsLogged" valuecolumn="jobsCount" itemcolumn="Name_ofTheJob" serieslabel="critical" >
</cfchartseries>
<cfchartseries type="bar" query="qJobsLogged" valuecolumn="jobsCount" itemcolumn="Name_ofTheJob" serieslabel="normal" >
</cfchartseries>

this will display 3 bars for each day indicating
Urgent - 5
Critical - 4
Normal - 3
 
Hi there, thanks a ton for replying to my query. I have done just what you asked me to. I'm having a problem with the values on the y-axis.

Code:
<cfset scTo=evaluate(ArrayMax(ListToArray(valuelist(qJobsLogged.JOBCOUNT)))+1)>

<cfchart show3d="yes" gridlines="#evaluate(scTo+1)#" labelformat="number" scalefrom="0" scaleto="#scTo#" format="jpg" xAxisTitle="log date" yAxisTitle="jobs logged" chartheight="300" chartwidth="450" sortxaxis="no" showborder="yes" showxgridlines="yes" showygridlines="yes" seriesplacement="stacked" font="Arial" fontsize="12" fontbold="yes">
  <cfchartseries type="bar" serieslabel="Critical" query="qJobsLogged" itemcolumn="DTLOGGED" valuecolumn="JOBCOUNT">
  <cfif qJobsLogged.jobPriorityCode eq 1>	
	<cfchartdata item="#qJobsLogged.DTLOGGED#" value="#qJobsLogged.JOBCOUNT#">
  </cfif> 
  </cfchartseries>
  <cfchartseries type="bar" serieslabel="Urgent" query="qJobsLogged" itemcolumn="DTLOGGED" valuecolumn="JOBCOUNT">
  <cfif qJobsLogged.jobPriorityCode eq 2>	
  	<cfchartdata item="#qJobsLogged.DTLOGGED#" value="#qJobsLogged.JOBCOUNT#">
  </cfif> 
  </cfchartseries>
  <cfchartseries type="bar" serieslabel="Normal" query="qJobsLogged" itemcolumn="DTLOGGED" valuecolumn="JOBCOUNT">
  <cfif qJobsLogged.jobPriorityCode eq 3>	
	  <cfchartdata item="#qJobsLogged.DTLOGGED#" value="#qJobsLogged.JOBCOUNT#">
  </cfif> 
  </cfchartseries>
  <cfchartseries type="bar" serieslabel="Other" query="qJobsLogged" itemcolumn="DTLOGGED" valuecolumn="JOBCOUNT">
  <cfif qJobsLogged.jobPriorityCode gt 3>	
	  <cfchartdata item="#qJobsLogged.DTLOGGED#" value="#qJobsLogged.JOBCOUNT#">
  </cfif> 
  </cfchartseries>
</cfchart>

Now then, the variable srcTo stores the Max count no of jobs logged in a day. In my case it is 87. When I run this code, the values displayed on the y-axis are "27.7, 83, 138.4, 193.8, 249.1, 304.5". As you can see these are of type float.

If I run the code with just one chartseries, the values get correctly displayed.

Code:
<cfset scTo=evaluate(ArrayMax(ListToArray(valuelist(qJobsLogged.JOBCOUNT)))+1)>

<cfchart show3d="yes" gridlines="#evaluate(scTo+1)#" labelformat="number" scalefrom="0" scaleto="#scTo#" format="jpg" xAxisTitle="log date" yAxisTitle="jobs logged" chartheight="300" chartwidth="450" sortxaxis="no" showborder="yes" showxgridlines="yes" showygridlines="yes" seriesplacement="stacked" font="Arial" fontsize="12" fontbold="yes">
  <cfchartseries type="bar" serieslabel="Critical" query="qJobsLogged" itemcolumn="DTLOGGED" valuecolumn="JOBCOUNT">
  <cfif qJobsLogged.jobPriorityCode eq 1>	
  	<cfchartdata item="#qJobsLogged.DTLOGGED#" value="#qJobsLogged.JOBCOUNT#">
  </cfif> 
  </cfchartseries>
</cfchart>

"6, 18, 30, 42, 54, 66, 78"

Any thoughts?

Regards
cfcoder
 
could you try using NumberFormat() function to eliminate decimals ?
 
tried it already. Didn't work. when I output srcto outside the cfchart tag, the value is 88
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top