3 Replies Latest reply on Oct 1, 2010 11:02 AM by Dan Bracuk

    cfoutput total by group

    bloodbanker Level 1
      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>

        • 1. Re: cfoutput total by group
          Dan Bracuk Level 5

          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#>

          • 2. Re: cfoutput total by group
            bloodbanker Level 1

            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

            function(){return A.apply(null,[this].concat($A(arguments)))}

            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.

             

            function(){return A.apply(null,[this].concat($A(arguments)))}

            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.

            • 3. Re: cfoutput total by group
              Dan Bracuk Level 5

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