2 Replies Latest reply on Mar 13, 2007 8:09 AM by kristocks1

    Getting Subtotals in a cfoutput group

      Hi, I'm trying to create a report that will show call data (# calls, avg duration, etc.). It will output a section for each rep and within that section it will break down by market and finally by date. I also want to include subtotals for each rep and market (this might make more sense when you look at the code below).

      Right now I'm using cfoutput grouping with a couple q of q's inside to get the subtotals. It's almost working, except the q of q for the market subtotals isn't returning anything. If anyone has suggestions for a more efficient way to do this, I'm open to that too.

      Thanks in advance for any help. Here's my code:
        • 1. Re: Getting Subtotals in a cfoutput group
          Dan Bracuk Level 5
          I think it would be easier without the q of q's. Something like this;

          <cfoutput query="someQuery" group="somefield">
          display group data
          display more data
          count = count + 1;
          total = total + something;
          you can get your group average at this point
          • 2. Re: Getting Subtotals in a cfoutput group
            kristocks1 Level 1
            Hi Dan, thank you for the reply. Your solution would normally work, but I'm actually displaying the section totals at the top of the detail, so I can't take advantage of the existing looping structure. However, your idea did get me thinking and I found a solution.

            I moved the innermost Q of Q outside of the cfoutput block so it creates a result set with a row for each market. Then within the cfoutput I reference the query using #qryRepMarketCalls.ColName[loopcount]# syntax to output the correct subtotals for that row.

            It works perfectly and cuts down on the number of Q of Qs to about 1/6th of what I needed before (since it's outside the market level grouping). In case my rambling didn't make sense, I'll also post the final code.