CFCHART Loop Query

Forum|Forum|19 years ago
January 11, 2007
4 replies
1136 views

Hi -

I want to loop over a query and Get users with hours charged to a project. When this runs it just shows the total of all the hours for each person. This query should return 2 people one with 12 hours and the other with four. instead it shows both with 16?
This has to be something right in front of me... Any help will be greatly appreciated - Thank you.

<CFQUERY datasource="DataSource" name="Chart_Hours">
SELECT URS.First, URS.Last, URS.User_ID, PHO.Edition_ID,
PHO.date_entered, PHO.User_ID, PHO.Date_Time, PHO.Project, PHO.Hours, PHO.Billed,
PRO.PROJECT_NAME, PRO.PROJECT_ID
FROM Users URS, Project_Hours PHO, PROJECTS PRO
WHERE URS.User_ID = PHO.User_ID
AND PHO.Project = 160
AND PRO.Project_ID = 160
</CFQUERY>

<CFSET counter = 0>

<cfloop index="i" from="1" to="#PROJECT_HOURS.RecordCount#">
<CFSCRIPT>
counter = counter + #PROJECT_HOURS.Hours#;
</CFSCRIPT>
</cfloop>

<cfchart format="png" scalefrom="0" SHOW3D="YES" scaleto="#PROJECT_HOURS.RECORDCOUNT#" SHOWLEGEND="YES" TITLE="#PROJECT_HOURS.Project_Name#">

<cfchartseries type="bar" serieslabel="Website Traffic 2006" seriescolor="blue">
<CFOUTPUT QUERY="PROJECT_HOURS">
<cfchartdata item="#PROJECT_HOURS.USERFIRST# #PROJECT_HOURS.USERLAST#" value="#counter#">
</CFOUTPUT>
</cfchartseries>
</cfchart>

Reporting

This topic has been closed for replies.

L

Ling0Author

Participating Frequently

I switch back to the Array and can't get the loop right. What am I doing wrong here this is killing me! Thank you in advance.

<CFQUERY datasource="#Application.PrimaryDB#" name="MyQuery1">
SELECT U.First, U.Last, U.User_ID, PH.User_ID, PH.Project, PH.Hours
FROM USERS U, Project_Hours PH
WHERE PH.User_ID = U.User_ID
AND PH.Project = 620
</cfquery>

<cfset myarray=arraynew(1)>

<cfloop query="MyQuery1">
<cfset myarray[currentrow][1]=First[currentrow]>
<cfset myarray[currentrow][2]=Last[currentrow]>
<cfset myarray[currentrow][3]=Hours[currentrow]>
</cfloop>

<CFSCRIPT>
counter = arraySum(MyQuery1["Hours"]);
</CFSCRIPT>

<CFOUTPUT QUERY="MyQuery1">
#LAST# = #HOURS#<BR>
</CFOUTPUT>

MyQuery1
<CFDUMP VAR="#MyQuery1#">

E

efecto747

Participating Frequently

Hi,

Not sure exactly what isn't working for you in that last code block, but this:

<cfset myarray=arraynew(1)>

should be:

<cfset myarray=arraynew(2)>

as its a 2 dimensional array - not sure if that helps with anything though..

One other thing is its good to always scope your variables so there's no confusion as to where the values are coming from.

cheers

L

Ling0Author

Participating Frequently

Ok this is still returning 16 for one user I was using QoQ to get the total for each user. I am at a loss -

I can get the hours with this however I can get the first and last name so I tried the QoQ:
SELECT Urs.user_id, sum(PHO.Hours) as hours
FROM Users URS, Project_Hours PHO, PROJECTS PRO
WHERE URS.User_ID = PHO.User_ID
AND PHO.Project = 60
AND PRO.Project_ID = 60
group by Urs.user_id;

<Cfquery datasource="#Application.PrimaryDB#" name="PROJECT_HOURS1">
SELECT U.First, U.Last, U.User_ID, PH.User_ID, PH.Project, PH.Hours
FROM USERS U, Project_Hours PH
WHERE PH.User_ID = U.User_ID
AND PH.Project = 60
</cfquery>

<CFQUERY DBTYPE="QUERY" name="UP_PROJECT_HOURS">
SELECT sum(Hours) as THOURS
FROM PROJECT_HOURS1
</CFQUERY>

<CFOUTPUT QUERY="UP_PROJECT_HOURS">
#PROJECT_HOURS1.FIRST# #PROJECT_HOURS1.LAST# #UP_PROJECT_HOURS.THOURS#<BR>
</CFOUTPUT>

N

Newsgroup_User

Inspiring

> <cfloop index="i" from="1" to="#PROJECT_HOURS.RecordCount#">
> <CFSCRIPT>
> counter = counter + #PROJECT_HOURS.Hours#;
> </CFSCRIPT>
> </cfloop>

You set counter here. From what you say, it ends up being 16.

> <CFOUTPUT QUERY="PROJECT_HOURS">
> <cfchartdata item="#PROJECT_HOURS.USERFIRST# #PROJECT_HOURS.USERLAST#"
> value="#counter#">
> </CFOUTPUT>

And you're generating you're chart data here. the thing is, you're not
deriving #counter# from the query, you're deriving it from the code block
above. It's not going to change: it equals 16. Irrespective of how many
records you have in your query, you're simply going to be plotting 16 for
all of them.

Back to this:
counter = counter + #PROJECT_HOURS.Hours#;

Why don't you just go:

counter = arraySum(PROJECT_HOURS["Hours"]);

?

--
Adam

L

Ling0Author

Participating Frequently

Well I am not that great at using array's I thought I could do it with what I had. As for the chart I thought I would try building reports using CFCHART. Thanks anyway I will keep trying.

D

Dan_Bracuk

Inspiring

Is the query giving you 16, or the chart. I can see how the chart would, since you are charting the sum, not the hours per user.

L

Ling0Author

Participating Frequently

It is the chart - if I loop over a table the query works. If I just output to chart it gives me 16 for both.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded