2. ASPRunnerPro General questions
  3. Column Total
This topic is locked

Column Total

 		1/4/2004 3:27:18 AM
ASPRunnerPro General questions
M
 mwill9872 author

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=285&image=1&table=forumtopics' class='bbc_emoticon' alt=':lol:' /> Is it possible to display column total i.e. like an excel sheet
Great Product Serg.... Keep up the great work.
Sergey Kornilov admin 1/5/2004

Hi,
after you displayed table with data you need to calculate and display totals.

Set rsTotals = server.CreateObject ("ADODB.Recordset")

strSQL = "select sum(Money) as MoneyTotal, sum(XYZ) as XYZTotal from table"

rsTotals.open strSQL, dbConnection

Response.Write rsTotals("MoneyTotal")

Response.Write rsTotals("XYZTotal")

rsTotals.Close

set rsTotals = Nothing


I hope this helps.
Best regards,

Sergey Kornilov
Becca 1/8/2004

Hi,
I thought I had done this right, but I think I goofed it...I added the code to my <tablename>_list.asp; but it didn't turn out right. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=892&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' /> Any chance you can tell me if I'm pasting the code in the right area? below's what I did:
Here's the last few lines above the code I added:

<%

  iNumberOfRows = iNumberOfRows + 1

  rsData.movenext

 Loop

end if

end sub


Here's the code I added (inserted at or around line 985):

'Begin Column Total Code

Set rsTotals = server.CreateObject ("ADODB.Recordset")

strSQL = "select sum(Jan) as JanTotal, sum(Feb) as FebTotal from 2004_Nuke"

rsTotals.open strSQL, dbConnection

Response.Write rsTotals("JanTotal")

Response.Write rsTotals("FebTotal")

rsTotals.Close

set rsTotals = Nothing

'End Colum Total Code


Here's a few lines of the code after the column total:

Sub CalculateOrderBy

if Request.Form("action")="OrderBy" then

strOrderBy = " order by " & AddWrappers(Request.Form("orderby"))


Thanks in advance for your help!
Sergey Kornilov admin 1/8/2004

Becca,
try this (see code snippet in bold to insert).

<%

call WriteTableHeader

call loopRs(rsDetails, CLng(PageSize))

%>
<%

rsDetails.Close

Set rsDetails = Nothing
%>

</table>
'Begin Column Total Code

Set rsTotals = server.CreateObject ("ADODB.Recordset")

strSQL = "select sum(Jan) as JanTotal, sum(Feb) as FebTotal from 2004_Nuke"

rsTotals.open strSQL, dbConnection

Response.Write rsTotals("JanTotal")

Response.Write rsTotals("FebTotal")

rsTotals.Close

set rsTotals = Nothing

'End Colum Total Code
<%' Pagination: Write Control%>


Best regards,

Sergey Kornilov
Becca 1/14/2004

I hate to be a thorn in your side with this subject, but I did exactly as the code you added says, and I cannot get totals to display on my list. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=916&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' /> Just to make sure I haven't overlooked anything, I've gathered everything together so that you can look at it a bit easier.
The files I created are already on the web and can be found here: http://www.cms-reports.com/E104/serg
In case you'd like the zipped files containing ASP Runner's output, I've already compressed them and made them available for download from the link listed above.
Any help or guidance you can give me would be greatly appreciated!
Thanks,
Becca
Sergey Kornilov admin 1/14/2004

Becca,
I found the problem - connection needed to beopen explicitly. Here is the update code:

<%

call WriteTableHeader

call loopRs(rsDetails, CLng(PageSize))

%>
<%

rsDetails.Close

Set rsDetails = Nothing
%>

</table>
'Begin Column Total Code

set dbConnection = server.CreateObject ("ADODB.Connection")

dbConnection.ConnectionString = strConnection

dbConnection.Open
Set rsTotals = server.CreateObject ("ADODB.Recordset")

strSQL = "select sum(Jan) as jant, sum(Feb) as febt, sum(Mar) as mart, sum(Apr) as aprt, sum(May) as mayt, sum(Jun) as junt, sum(Jul) as jult, sum(Aug) as augt, sum(Sep) as sept, sum(Oct) as octt, sum(Nov) as novt, sum(Dec) as dect, sum(Total) as gt from serg_test"

rsTotals.open strSQL, dbConnection

Response.Write rsTotals("jant")

rsTotals.Close

set rsTotals = Nothing

dbConnection.Close

set dbConnection = nothing
'End Colum Total Code
<%' Pagination: Write Control%>


Make sure your SQL query do not returns NULLs - this will break ASP code that displays totals.
Best regards,

Sergey Kornilov
Becca 1/14/2004

sweet, thanks, Serg. It works. One more question, and I'll let this topic die, promise!
I have each column setup as currency format - and I'd like to carry that over to this. Any idea off the top of your head how I can apply my currency formatting that's used in the above rows to my new total row?
Thanks again,
Becca
Sergey Kornilov admin 1/14/2004

Becca,
you can use FormatCurrency function:

Response.Write FormatCurrency(rs("..."))


Best regards,

Sergey Kornilov