cfoutput total by group

[bloodbanker]

I have a single table that I want to display by calendar quarter and have totals by quarter and by group inside of the quarter.
Here's a copy of the current report so you can see what I have so far.
I'd like a total after each of the Allo and Auto groups, and a total by each quarter.As an added bonus, is it possible to get a pagebreak after each quarter?  I used cfdocument to create the report.As  I've posted in the past, I am not a programmer, nor a database person,  but a lab worker, and I often employee a poor mans solution to my  problems - so please be kind in your review .
My poor mans solution for this one will be to create several tables based on what was submitted.Here is the query I am using:

SELECT
    TOP (100) PERCENT
    DATEPART(yy, MNCDate) AS CalYear,
    DATEPART(q, MNCDate) AS CalQuarter,
    Donortype,
    Staff,
    COUNT(Efficiency) AS Num_All_MNC,
    DATEPART(yy, DATEADD(q, 1, MNCDate)) AS Year,
    DATEPART(q, DATEADD(q, 1, MNCDate)) AS Quarter
FROM 
   MNC
WHERE
   MNCDate >= <cfqueryparam value="#form.startDate#" cfsqltype="cf_sql_timestamp"> AND
   MNCDate <= <cfqueryparam value="#form.endDate#" cfsqltype="cf_sql_timestamp">
GROUP BY
    DATEPART(q, MNCDate),
    DATEPART(yy, MNCDate),
    Staff,
    Donortype,
    DATEPART(yy, DATEADD(q, 1, MNCDate)),
    DATEPART(q, DATEADD(q, 1,  MNCDate))
HAVING
    (NOT (Staff IS NULL))
ORDER BY
    CalYear,
    CalQuarter,
    Donortype

Then  I output it like this (I know it's a bad thing to have the query inside  of the output, but I couldn't think of any other way to get this info.

<table width="100%" border="0" cellspacing="0" cellpadding="3">
  <tr>
    <td class="tdBold">Year</td>
    <td class="tdBold">Quarter</td>
    <td class="tdBold">Staff</td>
    <td class="tdBold">All MNC</td>
    <td class="tdBold">Efficient MNC</td>
    <td class="tdBold">% of Efficient MNC</td>
  </tr>
  <cfoutput query="ByStaff" group="Donortype">

  <tr>
    <td colspan="6" class="tdGroup">#Donortype#</td>
  </tr>
  <cfoutput>
    <cfquery name="EffMNC" DataSource="#DSN#"> 
SELECT
   Count(Efficiency) AS EffMNC
FROM
   MNC
WHERE
   MNCDate >= <cfqueryparam value="#form.startDate#" cfsqltype="cf_sql_timestamp"> AND
   MNCDate <= <cfqueryparam value="#form.endDate#" cfsqltype="cf_sql_timestamp"> AND
    Efficiency >= 40 AND
    Staff = #ByStaff.Staff# AND
    Donortype = '#ByStaff.Donortype#' AND
    DATEPART(yy, MNCDate) = #ByStaff.CalYear# AND
    DATEPART(q, MNCDate) = #ByStaff.CalQuarter#
</cfquery>
    <cfquery name="EffCount" DataSource="#DSN#"> 
SELECT
   Count(Efficiency) AS EffMNC
FROM
   MNC
WHERE
   MNCDate >= <cfqueryparam value="#form.startDate#" cfsqltype="cf_sql_timestamp"> AND
   MNCDate <= <cfqueryparam value="#form.endDate#" cfsqltype="cf_sql_timestamp"> AND
    Efficiency >= 40
</cfquery>
  <tr>
    <td class="tdNotBold">#CalYear#</td>
    <td class="tdNotBold">#CalQuarter#</td>
    <td class="tdNotBold">#Staff#</td>
    <td class="tdNotBold">#Num_All_MNC#</td>
    <td class="tdNotBold">#EffMNC.EffMNC#</td>
    <td class="tdNotBold">
    <cfif #NumberFormat(Evaluate((('#EffMNC.EffMNC#')/('#Num_All_MNC#'))*100), '999')# EQ 100>
    <cfset EffColl = "#NumberFormat(Evaluate((('#EffMNC.EffMNC#')/('#Num_All_MNC#'))*100), '999')#">
    <cfelse>
    <cfset EffColl = "#NumberFormat(Evaluate((('#EffMNC.EffMNC#')/('#Num_All_MNC#'))*100), '99.9')#">
    </cfif>
    #EffColl# %
    </td>
  </tr>
  </cfoutput>  </cfoutput>
  <tr>
    <td class="tdBold"> </td>
    <td class="tdBold"> </td>
    <td class="tdBold"> </td>
    <td class="tdBold"><cfoutput>Sum: #ArraySum(ByStaff["Num_All_MNC"])#</cfoutput></td>
    <td class="tdBold"><cfoutput>Sum: #ArraySum(EffCount["EffMNC"])#</cfoutput></td>
    
 <td class="tdBold"><cfoutput>Average:  
#NumberFormat(Evaluate((('#ArraySum(EffCount["EffMNC"])#')/('#ArraySum(ByStaff["Num_All_MNC"])#'))*100), 
 '99.9')#</cfoutput> %</td>
  </tr>
</table>

[Dan_Bracuk]

First, you don't need the cfoutput tag to make the query work.  Any variables in your sql will be read.

Next, your sql is not appropriate for the task as you described it.  You say you want to qroup by quarter and group, but your query is grouping by other things.

Next, you are overengineering your math.  You don't need the evaluate function to do math.  You just do it.  It's as simple as this:

<cfoutput query="yourquery">

<cfset x = #field1 / #field2#>

[bloodbanker]

Thanks Dan, I know I posted a lot, and I appreciate you reviewing it.

It was kind of buried in the top of the post, but were you able to view the example?  I posted it here: http://www.jptm.info/example.pdf

Dan Bracuk wrote:

First, you don't need the cfoutput tag to make the query work.  Any variables in your sql will be read.

I don't understand what you mean here.

I used the second cfoutput to get the second group, first by product group, then by year / quarter.

Dan Bracuk wrote:

Next, your sql is not appropriate for the task as you described it.  You say you want to qroup by quarter and group, but your query is grouping by other things.

I need the report to stay together the way it looks now:

  Allo

    Quarter

      Year

  Auto

     Quarter

       Year

It is also counting by Staff and MNC.

Is there a better way to query the data?  I inherited this report from someone who no longer works here (I'm sure that's not the first time that comments been made)

I'll remove the evaluate function as recommended.

Still, what I really need is a way to have totals for each of the sections (4 in the example) as well as the overall report one that is already listed.

[Dan_Bracuk]

I took but a quick look.  The group attibute of the cfoutput tag may help you out.  Details are in the docs.