Skip to main content
K
kristocks1
Inspiring
March 13, 2007
Answered

Getting Subtotals in a cfoutput group

  • March 13, 2007
  • 1 reply
  • 739 views
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:
This topic has been closed for replies.
Correct answer kristocks1
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.

1 reply

D
Dan_Bracuk
Inspiring
March 13, 2007
I think it would be easier without the q of q's. Something like this;


<cfoutput query="someQuery" group="somefield">
<cfscript>
count=0;
total=0;
</cfscript>
display group data
<cfoutput>
display more data
<cfscript>
count = count + 1;
total = total + something;
</cfoutput>
you can get your group average at this point
</cfoutput>
K
kristocks1AuthorCorrect answer
Inspiring
March 13, 2007
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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices