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(qJobs Logged.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