• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

cfloop query help

sbennett gave me an awesome answer to my previous question about summarizing a line by line excel sheet with totals at the bottom.  he gave me the code in the first block below.  can someone (sbennett) help me actually put it in my actual code which I've pasted below his?  i only need the Corp07 column totaled.  thank you!!

<cfoutput>
<table>
<cfset MyTotal = "0">
<cfloop query="MyQuery">
<cfset MyTotal = MyTotal + MyQuery.DataColumn>
      <tr>
            <td>#MyQuery.DataColumn#</td>
      </tr>
</cfloop>
      <tr>
            <td>#MyTotal#</td>
      </tr>
</table>
</cfoutput>

MY QUERY STARTS HERE
<cfquery name="campaign07" datasource="Pluto" dbtype="ODBC">
SELECT     ORGACCOUNTNUMBER AS Oacct, ORGNAME1 AS Name, TP_ORG_CORP_34_2007_TOTAL AS Corp07
FROM         dbo.DATAEXTRACTANDARCAMPAIGN2006S
ORDER BY EmplGoal DESC
</cfquery>

<cfheader name="Content-Disposition" value="inline;filename=CampaignUpdate.xls">
<cfcontent type="application/msexcel">

<Table border="1" cellspacing="1">
<thead>
<tr>
<th><div align="left">Oacct</div></th>
<th><div align="left">Name</div></th>
<th><div align="left">Corp07</div></th>
</tr>
</thead>

<tbody>
<cfoutput query="campaign07">
<tr>
<td>#Oacct#</td>
<td>#Name#</td>
<td>#NumberFormat(Corp07,"$99,999,999")#</td>
</tr>
</cfoutput>
</tbody>

<tfoot>

</tfoot>

</table>
0
ssdanish
Asked:
ssdanish
  • 3
1 Solution
 
_agx_Commented:
Hi ssdanish,

What is your question?
0
 
ssdanishAuthor Commented:
i messed up the first time and re-edited my question with the text below:

sbennett gave me an awesome answer to my previous question about summarizing a line by line excel sheet with totals at the bottom.  he gave me the code in the first block below.  can someone (sbennett) help me actually put it in my actual code which I've pasted below his?  i only need the Corp07 column totaled.  thank you!!

<cfoutput>
<table>
<cfset MyTotal = "0">
<cfloop query="MyQuery">
<cfset MyTotal = MyTotal + MyQuery.DataColumn>
      <tr>
            <td>#MyQuery.DataColumn#</td>
      </tr>
</cfloop>
      <tr>
            <td>#MyTotal#</td>
      </tr>
</table>
</cfoutput>

MY QUERY STARTS HERE
<cfquery name="campaign07" datasource="Pluto" dbtype="ODBC">
SELECT     ORGACCOUNTNUMBER AS Oacct, ORGNAME1 AS Name, TP_ORG_CORP_34_2007_TOTAL AS Corp07
FROM         dbo.DATAEXTRACTANDARCAMPAIGN2006S
ORDER BY EmplGoal DESC
</cfquery>

<cfheader name="Content-Disposition" value="inline;filename=CampaignUpdate.xls">
<cfcontent type="application/msexcel">

<Table border="1" cellspacing="1">
<thead>
<tr>
<th><div align="left">Oacct</div></th>
<th><div align="left">Name</div></th>
<th><div align="left">Corp07</div></th>
</tr>
</thead>

<tbody>
<cfoutput query="campaign07">
<tr>
<td>#Oacct#</td>
<td>#Name#</td>
<td>#NumberFormat(Corp07,"$99,999,999")#</td>
</tr>
</cfoutput>
</tbody>

<tfoot>

</tfoot>

</table>


0
 
_agx_Commented:
This is not tested, but try something like this

MY QUERY STARTS HERE
<cfquery name="campaign07" datasource="Pluto" dbtype="ODBC">
SELECT     ORGACCOUNTNUMBER AS Oacct, ORGNAME1 AS Name, TP_ORG_CORP_34_2007_TOTAL AS Corp07
FROM         dbo.DATAEXTRACTANDARCAMPAIGN2006S
ORDER BY EmplGoal DESC
</cfquery>

<cfset myTotal = 0>
<cfheader name="Content-Disposition" value="inline;filename=CampaignUpdate.xls">
<cfcontent type="application/msexcel">

<Table border="1" cellspacing="1">
<thead>
<tr>
<th><div align="left">Oacct</div></th>
<th><div align="left">Name</div></th>
<th><div align="left">Corp07</div></th>
</tr>
</thead>

<cfset myTotal = 0>

<tbody>
<cfoutput query="campaign07">
<tr>
<td>#Oacct#</td>
<td>#Name#</td>
<td>#NumberFormat(Corp07,"$99,999,999")#</td>
</tr>
<cfset myTotal  = myTotal  + Corp07>
</cfoutput>
</tbody>
<tfoot>
<cfoutput>
<tr><th colspan="3">#NumberFormat(myTotal,"$99,999,999")#</th></tr>
</cfoutput>
</tfoot>
</table>
0
 
_agx_Commented:
Yes, that should work.   You may want to add a description to the total line like this:

MY QUERY STARTS HERE
<cfquery name="campaign07" datasource="Pluto" dbtype="ODBC">
SELECT     ORGACCOUNTNUMBER AS Oacct, ORGNAME1 AS Name, TP_ORG_CORP_34_2007_TOTAL AS Corp07
FROM         dbo.DATAEXTRACTANDARCAMPAIGN2006S
ORDER BY EmplGoal DESC
</cfquery>

<Table border="1" cellspacing="1">
<thead>
<tr>
<th><div align="left">Oacct</div></th>
<th><div align="left">Name</div></th>
<th><div align="left">Corp07</div></th>
</tr>
</thead>

<cfset myTotal = 0>

<tbody>
<cfoutput query="campaign07">
<tr>
<td>#Oacct#</td>
<td>#Name#</td>
<td>#NumberFormat(Corp07,"$99,999,999")#</td>
</tr>
<cfset myTotal  = myTotal  + Corp07>
</cfoutput>
</tbody>
<tfoot>
<cfoutput>
<tr><th align="right" colspan="2">Total:</th>
      <th>#NumberFormat(myTotal,"$99,999,999")#</th>
</tr>
</cfoutput>
</tfoot>
</table>
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now