Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Subtotaling, Totaling by Group

0 views
Skip to first unread message

jrschaef

unread,
Sep 4, 2002, 9:17:19 AM9/4/02
to
I have been struggling with this for too long. I have never displayed any calculation and so I am unfamiliar with calculating in CF totally. :)

I need to display a table grouped by Representative subtotaling by Risk rating/Representative and then totaling all Risk Rating by Representative. YIKES. I have attached the code I have so far.

Also, the MAXROW in my output isn't working, any ideas??

Thanks for your help in advance!!!
<!--- Begin queries --->

<CFQUERY NAME="getLoans" DATASOURCE="#APPLICATION.ProblemLoans#">
SELECT qry_PLRS_data_file.*, tbl_Bank_Names.*, ITI_Class_Codes.*
FROM qry_PLRS_data_file, tbl_Bank_Names, ITI_Class_Codes
WHERE qry_PLRS_data_file.Bk = #URL.Bank#
AND tbl_Bank_Names.Bank_No = #URL.Bank#
AND tbl_Bank_Names.Bank_No = qry_PLRS_data_file.Bk
AND ITI_Class_Codes.ClassCode = qry_PLRS_data_file.Class
AND qry_PLRS_data_file.Resp_Cd
ORDER BY Resp_Cd, Risk_Rating, Client
</CFQUERY>

<CFQUERY NAME="getBank" DATASOURCE="#APPLICATION.ProblemLoans#">
SELECT *
FROM tbl_Bank_Names
WHERE Bank_No = #URL.Bank#
</CFQUERY>
<!--- For Paging --->
<CFPARAM name="RecordsPerPage" default="100">
<CFPARAM name="StartRow" default="1">
<cfoutput>
<CFPARAM name="CountRow" default="#(Evaluate(URL.StartRow + RecordsPerPage) - 1)#">
<cfif getLoans.RecordCount GT CountRow>
<CFPARAM name="EndRow" default="#(Evaluate(getLoans.RecordCount - CountRow))#">
<cfelse>
<CFPARAM name="EndRow" default="#getLoans.RecordCount#">
</cfif>
</cfoutput>
<!--- End Paging --->
<!--- End queries --->
<html>
<head>
<title>Watchlist</title>
<link rel="STYLESHEET" type="text/css" href="../stylesheet.css">
</head>

<body>
<!--- Testing page counts --->
<cfoutput>
Record Count = #getLoans.recordcount#<br>
StartRow = #StartRow#<br>
Records Per Page = #RecordsPerPage#<br>
CountRow = #CountRow#<br>
EndRow = #EndRow#</cfoutput>
<!--- End of Testing page counts --->
<!---Begin Header--->
<table border="0" width="100%" cellspacing="0" cellpadding="4" align="center">
<tr>
<td valign="top" style="BORDER-TOP: SOLID NAVY 1"><font color="#00309C" size="4"><b>Watch Accounts by Loan Officer (Acct)</b></font></td>
<td valign="top" align="right" style="BORDER-TOP: SOLID NAVY 1"><font color="#00309C" size="2"><b><i>As of <cfoutput>#DateFormat(Now(),"mm/dd/yy")#</cfoutput></i></b></font></td>
</tr>
<tr>
<td colspan="2" height="30" style="BORDER-RIGHT: SOLID 1; BORDER-TOP: SOLID 1; BORDER-BOTTOM: SOLID 1; BORDER-LEFT: SOLID 1" ><b><font size="+1"><cfoutput query="getBank">#Bank_Name#</cfoutput></font></b></td>
</tr>
</table>
<br>
<!--- End Header --->
<!--- Decide if paging is necessary --->
<CFIF getLoans.RecordCount GT RecordsPerPage>
<table border="0" width="100%" cellspacing="0" cellpadding="4" align="center">
<TR>
<TD align="right">
<CFMODULE template="WatchBank_next.cfm" ThisPagesName="Watch_Bank.cfm" ExtraURL="Bank=#URL.Bank#" Records="#getLoans.RecordCount#" StartRow="#URL.StartRow#" RecordsPerPage="#RecordsPerPage#">
</TD>
</TR>
</CFIF>
<!--- End paging --->
<table width="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0" align="center">
<cfoutput>
<TR>
<TD valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1">&nbsp;&nbsp;</TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>Br</i></b></font></TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>Line No</i></b></font></TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>Risk<br>Rate</i></b></font></TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>Line<br>Balance</i></b></font></TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>Acct No</i></b></font></TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>Loan<br>Type</i></b></font></TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>NAC</i></b></font></TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>Gvt<br>Gty</i></b></font></TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>Curr<br>Balance</i></b></font></TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>Avail</i></b></font></TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>NAC<br>Interest</i></b></font></TD>
<TD align="center" valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>Bank<br>Balance</i></b></font></TD>
<TD valign="bottom" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><font size="1"><b><i>Charge-<br>off</i></b></font></TD>
</TR>
</cfoutput>
<cfoutput query="getLoans" maxrows="#RecordsPerPage#" startrow="#URL.StartRow#" group="Resp_Cd">
<!--- Each Representative needs to list with records related to that rep --->
<TR>
<td colspan="14" style="BORDER-BOTTOM: SOLID NAVY 1; BORDER-TOP: SOLID NAVY 1"><b>#Resp_Cd# #FullName#</b></td>
</TR>
<!--- The records related to the representative need to loop --->
<cfoutput>
<cfif getLoans.currentrow mod 2 is 0>
<tr class="EVEN">
<cfelse>
<tr class="ODD">
</cfif>
<TD><font size="1">#Client#</font>&nbsp;</TD>
<TD align="center"><font size="1">#Br#</font>&nbsp;</TD>
<TD align="right"><font size="1">#Line_No#</font>&nbsp;</TD>
<TD align="center"><font size="1">#Risk_Rating#</font>&nbsp;</TD>
<TD align="right"><font size="1">#Numberformat(Evaluate(Round(Line_Bal)/1000), "9,999.9")#</font>&nbsp;</TD>
<TD align="right"><font size="1">#Numberformat(Acct_No, 999999999999)#</font>&nbsp;</TD>
<TD align="center"><font size="1">#Loan_Type#</font>&nbsp;</TD>
<TD align="center"><font size="1">#NAC#</font>&nbsp;</TD>
<TD align="right"><font size="1">#Numberformat(Gty_Pct, 999)#%</font>&nbsp;</TD>
<TD align="right"><font size="1">#Numberformat(Evaluate(Round(Active_Curr_Bal)/1000), "9,999.9")#</font>&nbsp;</TD>
<TD align="right"><font size="1">#Numberformat(Evaluate(Round(Avai_Bal)/1000), "9,999.9")#</font>&nbsp;</TD>
<TD align="right"><font size="1">#Numberformat(Evaluate(NAC_Interest/1000), "9,999.9")#</font>&nbsp;</TD>
<TD align="right"><font size="1">#Numberformat(Evaluate(Round(Active_Curr_Bal)/1000), "9,999.9")#</font>&nbsp;</TD>
<TD align="right"><font size="1">#Numberformat(Evaluate(Round(Curr_Chf)/1000), "9,999.9")#</font>&nbsp;</TD>
</TR>
</cfoutput>
<!--- Need subtotal by Risk Rating by Representative --->
<tr>
<TD colspan="9" style="BORDER-TOP: SOLID NAVY 1"><b><i>Summary for Risk Rating Group #Risk_Rating#- (#recordcount# notes)</i></b>&nbsp;</TD>
<TD align="right" style="BORDER-TOP: SOLID NAVY 1"><font size="1"><i>#Numberformat(Evaluate(Active_Curr_Bal/1000), "9,999.9")#</i></font>&nbsp;</TD>
<TD align="right" style="BORDER-TOP: SOLID NAVY 1"><font size="1"><i>#Numberformat(Evaluate(Avai_Bal/1000), "9,999.9")#</i></font>&nbsp;</TD>
<TD align="right" style="BORDER-TOP: SOLID NAVY 1"><font size="1"><i>#Numberformat(Evaluate(NAC_Interest/1000), "9,999.9")#</i></font>&nbsp;</TD>
<TD align="right" style="BORDER-TOP: SOLID NAVY 1"><font size="1"><i>#Numberformat(Evaluate(Round(Active_Curr_Bal)/1000), "9,999.9")#</i></font>&nbsp;</TD>
<TD align="right" style="BORDER-TOP: SOLID NAVY 1"><font size="1"><i>#Numberformat(Evaluate(Round(Curr_Chf)/1000), "9,999.9")#</i></font>&nbsp;</TD>
</tr>
<!--- Need Totals by Representative --->
<tr>
<TD align="center" colspan="9" style="BORDER-TOP: SOLID NAVY 1"><b><i>Summary for Participations Purchased - (#recordcount# notes)</i></b>&nbsp;</TD>
<TD align="right" style="BORDER-TOP: SOLID NAVY 1"><font size="1"><i>#Numberformat(Evaluate(Active_Curr_Bal/1000), "9,999.9")#</i></font>&nbsp;</TD>
<TD align="right" style="BORDER-TOP: SOLID NAVY 1"><font size="1"><i>#Numberformat(Evaluate(Avai_Bal/1000), "9,999.9")#</i></font>&nbsp;</TD>
<TD align="right" style="BORDER-TOP: SOLID NAVY 1"><font size="1"><i>#Numberformat(Evaluate(NAC_Interest/1000), "9,999.9")#</i></font>&nbsp;</TD>
<TD align="right" style="BORDER-TOP: SOLID NAVY 1"><font size="1"><i>#Numberformat(Evaluate(Round(Active_Curr_Bal)/1000), "9,999.9")#</i></font>&nbsp;</TD>
<TD align="right" style="BORDER-TOP: SOLID NAVY 1"><font size="1"><i>#Numberformat(Evaluate(Round(Curr_Chf)/1000), "9,999.9")#</i></font>&nbsp;</TD>
</tr>
</cfoutput>
</TABLE>

<cfinclude template="../include/include/bremer_footer.cfm">

On occassion we use Access 97 databases but primarily we use MS SQL 7.0

CFScuba

unread,
Sep 4, 2002, 10:38:10 AM9/4/02
to
Unfortunatly, I don't really have time to dig into your code too much, but here's the gist of what you're going to need to do.

From what I saw just glancing over the code, it seems you think aggregate (summary, etc) data is calculated for you - it's not. You need to do your own calculations. When you leave the nested cfoutput loop there isn't any aggregate data available to you, so you need to maintain and track it yourself within the inner cfoutput loop. Think about it this way - you want to define a "bucket" to drop totals into - within each iteration of the inner loop you'll increase the size of the bucket. After you understand this code, you'll see how easily you can add buckets that would track totals across all years (simply incrementing at the end of each outer loop.

So, for instance, if we have a query called "associateSales" that lists the montly sales by associate you may have a record set that looks like this (pretending that a year is only three months):

Associate, Year, Jan, Feb, Mar, AssocTotal
Joe Blow, 2001, 100, 200, 150, 450
Sally Sue, 2001, 100, 200, 150, 450
Joe Blow, 2002, 100, 200, 150, 450
Sally Sue, 2002, 100, 200, 150, 450
Euben Hadd, 2002, 10, 20, 30, 50

If you wanted to be able to display these results grouped by year (along with both monthly and grand totals), you would do something like this:

<table border=1>
<cfoutput query="associateSales" group="year">
<!--- Create/reset Aggregate Buckets - each yearly iteration will reset these numbers to zero. --->
<cfset janTotal=0>
<cfset febTotal=0>
<cfset marTotal=0>
<cfset yearTotal=0>

<tr>
<td>Associate</td>
<td>Jan</td>
<td>Feb</td>
<td>Mar</td>
<td>Total</td>
</tr>

<!--- Begin inner Loop --->
<cfoutput>
<tr>
<td> #associateSales.associate#</td>
<td>#associateSales.Jan</td>
<td>#associateSales.Feb</td>
<td>#associateSales.Mar</td>
<td>#associateSales.AssocTotal#</td>
</tr>

<!--- Add the current record to the buckets --->
<cfset janTotal=variables.janTotal + associateSales.Jan>
<cfset janTotal=variables.febTotal + associateSales.Feb>
<cfset janTotal=variables.marTotal + associateSales.Mar>
<cfset janTotal=variables.yearTotal + associateSales.AssocTotal>

</cfoutput>

<!--- We've finished the current year - now display the year's summary data --->
<tr bgcolor="someOffsetColorHere">
<td>Yearly Totals:</td>
<td>#variables.janTotal#</td>
<td>#variables.febTotal#</td>
<td>#variables.marTotal#</td>
<td>#variables.yearTotal#</td>
</tr>
</cfoutput>
</table>

Robert Hudson

0 new messages