grouping data and outputting totals for each group

[devasp]

hi there,
I am writing a report using ASP and am having trouble displaying the data in the way i want.
I'm collecting turnovers for various companies and each of the companies has a county code. e.g. Yorkshire is defined as number 2.

I need to group the companies together by county and need to display a totals line for those members only and then on the following lines I want the next county and its companies and so on.

I can group the counties together but the totals line I'm displaying appears after each company rather than after the group of companies for a given county.

I use a do while loop to check that there is data in the database for each company. I tried putting the totals line outside the loop and that only shows 1 totals line for ALL the companies.


Here's how it works

* Declaring variables

* dim objrs3
dim objConn

call dbConn( )

call get_sladdress( )

* Displaying Headers

* do while not ObjRs3.eof

* Calculations for turnovers over certain periods of time

* displaying the data

* Totals line

* objrs3.movenext
loop

* response.flush
response.clear
call dbDisConn( )

* DB
********************************************

sub get_sladdress( )
'==SQL STATMENT========================================== =================================
'below is the SQL statment which is going to be used.
'Open the recordset-This selects everything from the table
SET objRs3 = SERVER.createObject( "ADODB.recordSet" )

dim sqlStatment

sqlStatment = sqlStatment + ""
sqlStatment = sqlStatment + "SELECT DISTINCT [slaccount].[Name], [slaccount].[Customer], "
sqlStatment = sqlStatment + "[slaccount].[bus-code], [slaccount].[Opened], "
sqlStatment = sqlStatment + "[slaccount].[cus-anal1] AS Buy_Group, [slaccount].[Credit-limit], "

sqlStatment = sqlStatment + "[sladdress].[customer], [sladdress].[Reps-code] AS ASM_County, "
sqlStatment = sqlStatment + "[sladdress].[Address@1], [sladdress].[Address@2], [sladdress].[Address@3], "
sqlStatment = sqlStatment + "[sladdress].[Address@4], [sladdress].[Postcode], [sladdress].[Telephone], [sladdress].[Fax], "
sqlStatment = sqlStatment + "[sladdress].[notes@1] AS VisitD, [sladdress].[add-anal1] AS ASM_Name,"
sqlStatment = sqlStatment + "[sladdress].[notes@2] AS HealthD, [Sales_Turnover_Ranking].[ranking], "

sqlStatment = sqlStatment + "[uslaccount].[nmbs-anal2] AS unimer, [uslaccount].[gross-profit], [uslaccount].[tot-sales], "
sqlStatment = sqlStatment + "[uslaccount].[tover-group], [uslaccount].[last-acc-d] "

sqlStatment = sqlStatment + "FROM sladdress INNER JOIN slaccount ON [sladdress].[customer] = [slaccount].[customer] "
sqlStatment = sqlStatment + "INNER JOIN uslaccount ON [sladdress].[Customer] = [uslaccount].[Customer] "
sqlStatment = sqlStatment + "INNER JOIN Sales_Turnover_Ranking ON [sladdress].[Customer] = [Sales_Turnover_Ranking].[Customer] "

sqlStatment = sqlStatment + "WHERE [sladdress].[add-code] = ' ' "
sqlStatment = sqlStatment + "AND [slaccount].[Opened] = '1' "
sqlStatment = sqlStatment + "AND [sladdress].[add-anal1] BETWEEN '1' AND '5' "

sqlStatment = sqlStatment + "GROUP BY "
sqlStatment = sqlStatment + " [slaccount].[Name], "
sqlStatment = sqlStatment + " [slaccount].[Customer], "
sqlStatment = sqlStatment + " [slaccount].[bus-code], "
sqlStatment = sqlStatment + " [slaccount].[Opened], "
sqlStatment = sqlStatment + " [slaccount].[cus-anal1], "
sqlStatment = sqlStatment + " [slaccount].[Credit-limit], "
sqlStatment = sqlStatment + " [sladdress].[customer], "
sqlStatment = sqlStatment + " [sladdress].[Reps-code], "
sqlStatment = sqlStatment + " [sladdress].[Address@1], "
sqlStatment = sqlStatment + " [sladdress].[Address@2], "
sqlStatment = sqlStatment + " [sladdress].[Address@3], "
sqlStatment = sqlStatment + " [sladdress].[Address@4], "
sqlStatment = sqlStatment + " [sladdress].[Postcode], "
sqlStatment = sqlStatment + " [sladdress].[Telephone], "
sqlStatment = sqlStatment + " [sladdress].[Fax], "
sqlStatment = sqlStatment + " [sladdress].[add-code], "
sqlStatment = sqlStatment + " [sladdress].[notes@1], "
sqlStatment = sqlStatment + " [sladdress].[notes@2], "
sqlStatment = sqlStatment + " [sladdress].[add-anal1], "
sqlStatment = sqlStatment + " [uslaccount].[nmbs-anal2], "
sqlStatment = sqlStatment + " [uslaccount].[gross-profit], "
sqlStatment = sqlStatment + " [uslaccount].[tot-sales], "
sqlStatment = sqlStatment + " [uslaccount].[tover-group], "
sqlStatment = sqlStatment + " [uslaccount].[last-acc-d], "
sqlStatment = sqlStatment + " [Sales_Turnover_Ranking].[ranking] "

sqlStatment = sqlStatment + "ORDER BY [sladdress].[add-anal1], [sladdress].[Reps-code], [Sales_Turnover_Ranking].[ranking] "

objRs3.OPEN sqlStatment,objConn,1,3
'================================================= ========================================
end sub

* CLOSING DB CODE
*********************************************************


I am summing the columns I want. They're in another table called [slsummary] and im joining that table to [sladdress] and I'm grouping by [sladdress].[Reps-code] which is the area code for each company. The grouping works perfectly. But I don't undrestand why the totals line won't provide a total for the group instead it displays something like:

Acct[tab]Name[tab][tab]Jan[tab]Feb[tab]Mar[tab]Reps-Code
p12d[tab]Private[tab]£1000 £2510 £5203[tab]2
TOTALS[tab][tab][tab]£1000 £2510 £5203
r72t[tab] Retail[tab] £5965 £7512 £1316[tab] 2
TOTALS[tab][tab][tab]£5965 £7512 £1316


Any help would be greatly appreciated.

Thanks

 

[kss444]

If I understand correctly you want the Totals line to be at the end of your report and it will show the total of all accounts.
I would create a variable for each month and then in you loop add to the variables then at the end when your done with you loop write out the summed variables.

 

[devasp]

i am already doing that. what i need is the totals line like this:

Acct Name Jan Feb Mar Reps-Code
p12d Private £1000 £2510 £5203 2
r72t Retail £5965 £7512 £1316 2
TOTALS £6965 £10012 £6519

s87e Albion £2395 £1000 £500 19
ayt4 Range £950 £7512 £1316 19
TOTALS £3345 £8512 £1816

So basically i need it after all companies grouped by the Reps-code. So all companies with reps-code 2 have their own totals, then the ones with the code 19 and so on.

Im sure its pretty simple to do but i cant seem to get around what im doing wrong.