CodingSome CFML Stuffs Me Do

  

Press Ctrl+Enter to quickly submit your post
Quick Reply  
 
 
  
 From:  Peter (BOUGHTONP)   9 May 2011 16:07 
 To:  Ken (SHIELDSIT)       33 of 75 
38447.33 In reply to 38447.32 
Uh.... like this:

Select sum (colname)
from table
where colname > 0

?
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 11:57 
 To:  Peter (BOUGHTONP)      34 of 75 
38447.34 In reply to 38447.33 
What is your schedule looking like this fine day? I need you to hold my hand and whisper in my ear!
0/0
 Reply   Quote More 

 From:  Peter (BOUGHTONP)   10 May 2011 12:20 
 To:  Ken (SHIELDSIT)       35 of 75 
38447.35 In reply to 38447.34 
Dunno. Ask me again in an hour, when I've eaten and might be more awake.
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 12:22 
 To:  Peter (BOUGHTONP)      36 of 75 
38447.36 In reply to 38447.35 
k thanks you handsome man!
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 13:38 
 To:  Peter (BOUGHTONP)      37 of 75 
38447.37 In reply to 38447.35 

OK I'm having a hell of a time getting the summing to work, do you think you can assist?

 

I've attached the code and the output thus far.

 

Thanks!

Attachments:
monthly_kiln_report.cfm
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 13:50 
 To:  Peter (BOUGHTONP)      38 of 75 
38447.38 In reply to 38447.37 
Hmm, this is kinda strange. It you look at the ungrouped output you can see that the numbers returned are correct. So where am I messing up Peter?

I've changed my code a bit: I moved the bit that zero's out the total and tests for a negative to the query output area.

code:
<table cellpadding="2" align="center">
			<tr>
				<td align="center"><font face="arial" size="4"><b>Kiln Number</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Batch Date</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Species</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Footage In</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Value In</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Footage Out</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Value Out</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Variance</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Absorption</b></font></td>
				<td align="center"><font face="arial" size="4"><b>MFG Variance</b></font></td>
			</tr>
 
<!---
Loop the output and display it
--->
	<CFOUTPUT QUERY="kilns">
	<cfset intotal = 0>
<cfif rudTotal GT 0>
	<cfset intotal = intotal + rudTotal>
</cfif>			
<!---
Set values in our variables
--->
 
			<TR>
				<td align="center"><font face="arial" size="4">#ruhID#</TD>
				<td align="center"><font face="arial" size="4">#DateFormat(ruhDate, "MM/DD/YYYY")#</TD>
				<td align="center"><font face="arial" size="4">#MID(rudProductID,6,2)#</TD>
				<td align="center"><font face="arial" size="4">#NumberFormat(ConsumedVol, ",")#</TD>
				<td align="center"><font face="arial" size="4">#intotal#</td>
				<td align="center"><font face="arial" size="4">#NumberFormat(ProducedVol, ",")#</TD>
			</TR>
	</CFOUTPUT>
</TABLE>

Attachments:
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 13:53 
 To:  Peter (BOUGHTONP)      39 of 75 
38447.39 In reply to 38447.38 
I know why it's doing it, but my head hurts and I can't figure out how to fix it. I'm zeroing the total out every loop. How the fuck to I fix it? Make another variable to hold the grand total?
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 14:04 
 To:  Ken (SHIELDSIT)       40 of 75 
38447.40 In reply to 38447.39 
Just as I suspected. If I ungroup them I get the correct value. Grouped is wrong. Any idea how to get what I want?

This is the code that I've changed.

code:
CFOUTPUT QUERY="kilns" GROUP="ruhID">
	<cfset intotal = 0>
<cfif rudTotal GT 0>
	<cfset intotal = intotal + rudTotal>
	<cfset Total_Value_In = Total_Value_In + intotal>
</cfif>


Attachments:
0/0
 Reply   Quote More 

 From:  Peter (BOUGHTONP)   10 May 2011 14:41 
 To:  Ken (SHIELDSIT)       41 of 75 
38447.41 In reply to 38447.40 
Still don't feel entirely awake, so I might be missing something stupid, but for your main query you don't need GROUP BY unless your SELECT contains an aggregate for that query, which it doesn't.

(Your two aggregates, ConsumedVol and ProducedVol, are both in sub-queries, which would have their own GROUP BY if they needed it, but they don't since they're totals)


For the totalling bit of code... well, when you group on cfoutput you need an inner loop to do the more specific bit - and, more importantly, you need to ORDER BY the grouped bit first, otherwise you tend not to get what you're after.

So try this...
code:
<cfquery name="KilnsById" dbtype="query">
	SELECT *
	FROM Kilns
	ORDER BY ruhId ASC
</cfquery>
 
<cfset TotalTotal = 0 />
<cfoutput query="KilnsById" group="ruhId">
	<cfset InTotal = 0 />
	<cfoutput>
		<cfset InTotal += rudTotal />
	</cfoutput>
	<cfset TotalTotal += InTotal />
</cfloop>



Oh... except InTotal is needed for final table... so you probably actually want this:

code:
<cfquery name="KilnsById" dbtype="query">
	SELECT *
	FROM Kilns
	ORDER BY ruhId ASC
</cfquery>
 
<cfset TotalTotal = 0 />
<cfset InTotal = [] />
<cfoutput query="KilnsById" group="ruhId">
	<cfset InTotal[ruhId] = 0 />
	<cfoutput>
		<cfset InTotal[ruhId] += rudTotal />
	</cfoutput>
	<cfset TotalTotal += InTotal[ruhId] />
</cfloop>


Then do #InTotal[ruhId]# in the final td bit.

Maybe.

I'm not entirely convinced I'm not completely overcomplicating things here. :S
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 14:48 
 To:  Peter (BOUGHTONP)      42 of 75 
38447.42 In reply to 38447.41 
Can you tell me where I want to put that bit of code in my code? I've confused myself!
0/0
 Reply   Quote More 

 From:  Peter (BOUGHTONP)   10 May 2011 15:39 
 To:  Ken (SHIELDSIT)       43 of 75 
38447.43 In reply to 38447.42 
Replace your current bit of totalling code... everything after the comment saying "set variables for totals" and before the one saying "start the output of table headers".
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 16:23 
 To:  Peter (BOUGHTONP)      44 of 75 
38447.44 In reply to 38447.43 
OK. What about the missing beginning of the loop? I'd guess about where to start it, but I'm sure I'd be wrong.
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 16:34 
 To:  Ken (SHIELDSIT)       45 of 75 
38447.45 In reply to 38447.44 
I ended up getting a book for SRS too because I'm going to need to learn it.

I got this one.

Fuck - Peter this was to you.
0/0
 Reply   Quote More 

 From:  Peter (BOUGHTONP)   10 May 2011 16:42 
 To:  Ken (SHIELDSIT)       46 of 75 
38447.46 In reply to 38447.44 
Oh bugger! Told you I wasn't awake. That should be a </cfoutput> not a </cfloop>. Like this:

code:
<cfquery name="KilnsById" dbtype="query">
	SELECT *
	FROM Kilns
	ORDER BY ruhId ASC
</cfquery>
 
<cfset TotalTotal = 0 />
<cfset InTotal = [] />
<cfoutput query="KilnsById" group="ruhId">
	<cfset InTotal[ruhId] = 0 />
	<cfoutput>
		<cfset InTotal[ruhId] += rudTotal />
	</cfoutput>
	<cfset TotalTotal += InTotal[ruhId] />
</cfoutput>


(Although, ideally it should all be a cfloop, and whoever at Allaire thought it was a good idea to implement grouping on cfoutput and not on cfloop should be kicked hard.)
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 16:47 
 To:  Peter (BOUGHTONP)      47 of 75 
38447.47 In reply to 38447.46 
OK so I do that and I get this error:

quote:
can't cast [K217] string to a number value
0/0
 Reply   Quote More 

 From:  Peter (BOUGHTONP)   10 May 2011 17:00 
 To:  Ken (SHIELDSIT)       48 of 75 
38447.48 In reply to 38447.47 
That's because I did another stupid thing, and gave you an array instead of a structure.

Change:
code:
<cfset InTotal = [] />

to:
code:
<cfset InTotal = {} />


Sorry. :(
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 17:07 
 To:  Peter (BOUGHTONP)      49 of 75 
38447.49 In reply to 38447.48 

Not a problem at all. It didn't work, but I'm taking a break and going to look at the SQL Reporting side of it for a bit. It has me confused and I need a break!

 

Thanks for your time!
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    10 May 2011 20:13 
 To:  Peter (BOUGHTONP)      50 of 75 
38447.50 In reply to 38447.49 

Paeter most of my issue wasn't coding at all. My database contains both consumed and produced product. I only wanted to add consumed and ignore the produced.

 

Armed with that information I kicked it's ass and now have it adding what I want.

 

Appreciate you taking the time to whisper sweet nothings in my ear!
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    13 May 2011 00:33 
 To:  Peter (BOUGHTONP)      51 of 75 
38447.51 In reply to 38447.50 
Well my book came today, and I was able to make a report right off the get go. SQL Server Reporting Services is very similar to CF. There is still a bit of logic I need to figure out, and figure out how to implement it in the SSRS using the right code, but so far so good!
0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)    13 May 2011 01:00 
 To:  Ken (SHIELDSIT)       52 of 75 
38447.52 In reply to 38447.51 

What do you suggest the best way to put this data in to a database so I can use it to compare against another database?

 

I will need to check for type of wood and thickness and then pull that value to multiply by.

Attachments:
ScreenShot001.bmp
0/0
 Reply   Quote More 

Reply to All  
 

1–20  21–40  41–60  61–75

Rate my interest:

Adjust text size : Smaller 10 Larger

Beehive Forum 1.5.2 |  FAQ |  Docs |  Support |  Donate! ©2002 - 2024 Project Beehive Forum

Forum Stats