|
 |
asp_web_howto thread: What's the Best Way to Sub Total Recordset Table Rows?
Message #1 by "Kathy Bond" <klbond@n...> on Wed, 10 Apr 2002 15:33:03
|
|
Hello,
I have an asp query selecting records from my database into a recordset
and displaying them in a table format (using response.writes):
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
Communications Bill Comm Systems 14,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
I want to put in sub-totals by department and expenditure type and a grand
total. The only way I know to do this is to use standard logic that
stores the value of the last dept and expenditure type and compare it to
their current values, then break and response.write the sub-totals.
I am new at coding, so is there an easier (or better) way to do this in
asp to get:
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
--------
Exp Total 55,000
Communications Bill Comm Systems 14,000
--------
Exp Total 14,000
Dept Total 69,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
--------
Exp Total 27,000
Dept Total 27,000
Grand Total 96,000
Thanks, Kathy
Message #2 by Oleg Kapeljushnik <c-oleg.kapeljushnik@w...> on Wed, 10 Apr 2002 10:39:26 -0400
|
|
You can let SQL to make this job for you.
Just create another query that will calculate it for you.
and you can group it by department so you can summary for every department.
Oleg.
-----Original Message-----
From: Kathy Bond [mailto:klbond@n...]
Sent: April 10, 2002 11:33 AM
To: ASP Web HowTo
Subject: [asp_web_howto] What's the Best Way to Sub Total Recordset
Table Rows?
Hello,
I have an asp query selecting records from my database into a recordset
and displaying them in a table format (using response.writes):
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
Communications Bill Comm Systems 14,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
I want to put in sub-totals by department and expenditure type and a grand
total. The only way I know to do this is to use standard logic that
stores the value of the last dept and expenditure type and compare it to
their current values, then break and response.write the sub-totals.
I am new at coding, so is there an easier (or better) way to do this in
asp to get:
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
--------
Exp Total 55,000
Communications Bill Comm Systems 14,000
--------
Exp Total 14,000
Dept Total 69,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
--------
Exp Total 27,000
Dept Total 27,000
Grand Total 96,000
Thanks, Kathy
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
Message #3 by "Kathy Bond" <klbond@n...> on Wed, 10 Apr 2002 16:02:22
|
|
Oleg,
I am brand new at coding so I don't follow what you're suggesting. I used
group by when the database records were selected into the recordset.
How can I do group by again and get the totals to display and also show
what total it is (Exp Total, Dept Total, Grand Total)?
Can you show a snippet code example for this?
Thanks, Kathy
> You can let SQL to make this job for you.
Just create another query that will calculate it for you.
and you can group it by department so you can summary for every department.
Oleg.
-----Original Message-----
From: Kathy Bond [mailto:klbond@n...]
Sent: April 10, 2002 11:33 AM
To: ASP Web HowTo
Subject: [asp_web_howto] What's the Best Way to Sub Total Recordset
Table Rows?
Hello,
I have an asp query selecting records from my database into a recordset
and displaying them in a table format (using response.writes):
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
Communications Bill Comm Systems 14,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
I want to put in sub-totals by department and expenditure type and a grand
total. The only way I know to do this is to use standard logic that
stores the value of the last dept and expenditure type and compare it to
their current values, then break and response.write the sub-totals.
I am new at coding, so is there an easier (or better) way to do this in
asp to get:
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
--------
Exp Total 55,000
Communications Bill Comm Systems 14,000
--------
Exp Total 14,000
Dept Total 69,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
--------
Exp Total 27,000
Dept Total 27,000
Grand Total 96,000
Thanks, Kathy
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
Message #4 by Oleg Kapeljushnik <c-oleg.kapeljushnik@w...> on Wed, 10 Apr 2002 11:43:44 -0400
|
|
The general idea is :
SELECT Department,Expenditure Type, SUM(Amount) FROM Table GROUP BY
Department,Expenditure Type.
Try this , I thin it should do what you need.
If its more complicated in your case send me your SQL, I'll take a look.
Oleg.
-----Original Message-----
From: Kathy Bond [mailto:klbond@n...]
Sent: April 10, 2002 4:02 PM
To: ASP Web HowTo
Subject: [asp_web_howto] RE: What's the Best Way to Sub Total Recordset
Table Rows?
Oleg,
I am brand new at coding so I don't follow what you're suggesting. I used
group by when the database records were selected into the recordset.
How can I do group by again and get the totals to display and also show
what total it is (Exp Total, Dept Total, Grand Total)?
Can you show a snippet code example for this?
Thanks, Kathy
> You can let SQL to make this job for you.
Just create another query that will calculate it for you.
and you can group it by department so you can summary for every department.
Oleg.
-----Original Message-----
From: Kathy Bond [mailto:klbond@n...]
Sent: April 10, 2002 11:33 AM
To: ASP Web HowTo
Subject: [asp_web_howto] What's the Best Way to Sub Total Recordset
Table Rows?
Hello,
I have an asp query selecting records from my database into a recordset
and displaying them in a table format (using response.writes):
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
Communications Bill Comm Systems 14,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
I want to put in sub-totals by department and expenditure type and a grand
total. The only way I know to do this is to use standard logic that
stores the value of the last dept and expenditure type and compare it to
their current values, then break and response.write the sub-totals.
I am new at coding, so is there an easier (or better) way to do this in
asp to get:
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
--------
Exp Total 55,000
Communications Bill Comm Systems 14,000
--------
Exp Total 14,000
Dept Total 69,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
--------
Exp Total 27,000
Dept Total 27,000
Grand Total 96,000
Thanks, Kathy
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
Message #5 by "Kathy Bond" <klbond@n...> on Wed, 10 Apr 2002 18:36:11
|
|
Here is a cut-down portion of my code. I didn't post the entire query
because it has a rather large select statement of fields and it has table
joins for five tables. I can email it to you if you'd like to see the
query.
Again, what I want is to have the page in the format that looks like a
printed report does, just like my example showing the control totals.
In the sql select statement I had the GROUP BY which summarized the data
the way I needed it but now I need to insert control totals and display
what the total represents like "exp total", "dept total", "grand total".
Here's the snippet of code which is reading the recordset and
response.writing the table lines:
Thanks for your help,
Kathy
Set oRSp = objConn.Execute (sSQL)
oRSp.MoveFirst
If oRSp("proj_status_cd").Value = 0 then
vSTAT = "open"
else
vSTAT = "closed"
end if
Response.Write "<h3 align=center>Actual Dollars by Department</h3>"
Response.Write "<h3 align=center>For NUMBER" & oRSp("proj_num").Value
& "</h3>"
Response.Write "<TABLE>"
Response.Write "<TR><TD>Description:" & " </TD><TD>"
Response.Write oRSp("description_1").Value & "</TD></TR>"
Response.Write "<TR><TD> </TD>"
Response.Write "<TD>" & oRSp("description_2").Value & "</TD></TR><br>"
Response.Write "<TR><TD> </TD>" & "<TD> </TD></TR></TABLE>"
Response.Write "<TABLE>"
Response.Write "<TR><TD>Capital Contact:</TD> "
Response.Write "<TD>" & oRSp("contact").Value & "</TD><TD>
</TD><TD>Phone:</TD><TD>" & oRSp("phone").Value & "</TD></TR>"
Response.Write "<TR><TD>Project Status:</TD><TD>" & vSTAT
& "</TD><TD> </TD><TD> </TD><TD> </TD></TR>"
Response.Write
"<TR><TD> </TD><TD> </TD><TD> </TD><TD>
</TD><TD> </TD></TR></TABLE>"
Response.Write "<TABLE cellspacing='0'>"
Response.Write "<TR><TD><U>Department</U></TD><TD><U>Expenditure
Type</U></TD><TD><U>Item</U></TD><TD
align=right><U>Budget</U></TD><TD
align=right><U>Actual</U></TD></TR>"
Response.Write
"<TR><TD> </TD><TD> </TD><TD> </TD><TD>
</TD><TD> </TD><TD> </TD></TR>"
vSTAT = ""
Do While NOT oRSp.EOF
If vSHADE = "<TR BGCOLOR='#CFCFCF'><TD>" then
vSHADE = "<TR BGCOLOR='#EFEFEF'><TD>"
else
vSHADE = "<TR BGCOLOR='#CFCFCF'><TD>"
end if
If IsNull(oRSp("item_num").Value) then
Response.Write "<TR><TD><font color=red><b>There are No Items for
this project</b></font></TD></TR>"
else
Response.Write vSHADE & oRSp("department_name").Value
& "</TD><TD> "
Response.Write oRSp("combined_exp_type").Value & "</TD><TD> "
Response.Write oRSp("item_name").Value & "</TD><TD align=right> "
Response.Write FormatNumber(oRSp("bud_amt").Value,0,,-1,-1)
& "</TD><TD align=right> "
Response.Write FormatNumber(oRSp("act_amt").Value,0,,-1,-1)
& "</TD></TR>"
end if
oRSp.MoveNext
loop
Response.Write "</TABLE>"
oRSp.Close
Set oRSp=nothing
objConn.close
Set objConn=nothing
%>
asp that the user gets show
> The general idea is :
SELECT Department,Expenditure Type, SUM(Amount) FROM Table GROUP BY
Department,Expenditure Type.
Try this , I thin it should do what you need.
If its more complicated in your case send me your SQL, I'll take a look.
Oleg.
-----Original Message-----
From: Kathy Bond [mailto:klbond@n...]
Sent: April 10, 2002 4:02 PM
To: ASP Web HowTo
Subject: [asp_web_howto] RE: What's the Best Way to Sub Total Recordset
Table Rows?
Oleg,
I am brand new at coding so I don't follow what you're suggesting. I used
group by when the database records were selected into the recordset.
How can I do group by again and get the totals to display and also show
what total it is (Exp Total, Dept Total, Grand Total)?
Can you show a snippet code example for this?
Thanks, Kathy
> You can let SQL to make this job for you.
Just create another query that will calculate it for you.
and you can group it by department so you can summary for every department.
Oleg.
-----Original Message-----
From: Kathy Bond [mailto:klbond@n...]
Sent: April 10, 2002 11:33 AM
To: ASP Web HowTo
Subject: [asp_web_howto] What's the Best Way to Sub Total Recordset
Table Rows?
Hello,
I have an asp query selecting records from my database into a recordset
and displaying them in a table format (using response.writes):
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
Communications Bill Comm Systems 14,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
I want to put in sub-totals by department and expenditure type and a grand
total. The only way I know to do this is to use standard logic that
stores the value of the last dept and expenditure type and compare it to
their current values, then break and response.write the sub-totals.
I am new at coding, so is there an easier (or better) way to do this in
asp to get:
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
--------
Exp Total 55,000
Communications Bill Comm Systems 14,000
--------
Exp Total 14,000
Dept Total 69,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
--------
Exp Total 27,000
Dept Total 27,000
Grand Total 96,000
Thanks, Kathy
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
Message #6 by Oleg Kapeljushnik <c-oleg.kapeljushnik@w...> on Wed, 10 Apr 2002 13:58:01 -0400
|
|
Ok,
First of all when you create your output don't use Response.Write right away
but collect all output into some string. In this case you could get more
control over
the data that you want to present. Especially if you need to present some
combination of
2 different recordset.
For example :
Dim strOutput,Str1,Str2,Str3
''' Execute your recordset
Str1 = .....
''' Execute your recordset
Str2 = .....
''' Execute your recordset
Str3 = .....
strOutput = Str1 & Str2 & Str3 & Str2
There are also another way.
You can create 2 recordsets. One for regular data and second one for summary
results.
So then you'll have to manipulate between those 2 recordsets.
For the beginning I would create 2 pages that create 2 different outputs and
then
will combine them together.
You can send me the code if you want to my e-mail I'll take a look.
Oleg.
-----Original Message-----
From: Kathy Bond [mailto:klbond@n...]
Sent: April 10, 2002 2:36 PM
To: ASP Web HowTo
Subject: [asp_web_howto] RE: What's the Best Way to Sub Total Recordset
Table Rows?
Here is a cut-down portion of my code. I didn't post the entire query
because it has a rather large select statement of fields and it has table
joins for five tables. I can email it to you if you'd like to see the
query.
Again, what I want is to have the page in the format that looks like a
printed report does, just like my example showing the control totals.
In the sql select statement I had the GROUP BY which summarized the data
the way I needed it but now I need to insert control totals and display
what the total represents like "exp total", "dept total", "grand total".
Here's the snippet of code which is reading the recordset and
response.writing the table lines:
Thanks for your help,
Kathy
Set oRSp = objConn.Execute (sSQL)
oRSp.MoveFirst
If oRSp("proj_status_cd").Value = 0 then
vSTAT = "open"
else
vSTAT = "closed"
end if
Response.Write "<h3 align=center>Actual Dollars by Department</h3>"
Response.Write "<h3 align=center>For NUMBER" & oRSp("proj_num").Value
& "</h3>"
Response.Write "<TABLE>"
Response.Write "<TR><TD>Description:" & " </TD><TD>"
Response.Write oRSp("description_1").Value & "</TD></TR>"
Response.Write "<TR><TD> </TD>"
Response.Write "<TD>" & oRSp("description_2").Value & "</TD></TR><br>"
Response.Write "<TR><TD> </TD>" & "<TD> </TD></TR></TABLE>"
Response.Write "<TABLE>"
Response.Write "<TR><TD>Capital Contact:</TD> "
Response.Write "<TD>" & oRSp("contact").Value & "</TD><TD>
</TD><TD>Phone:</TD><TD>" & oRSp("phone").Value & "</TD></TR>"
Response.Write "<TR><TD>Project Status:</TD><TD>" & vSTAT
& "</TD><TD> </TD><TD> </TD><TD> </TD></TR>"
Response.Write
"<TR><TD> </TD><TD> </TD><TD> </TD><TD>
</TD><TD> </TD></TR></TABLE>"
Response.Write "<TABLE cellspacing='0'>"
Response.Write "<TR><TD><U>Department</U></TD><TD><U>Expenditure
Type</U></TD><TD><U>Item</U></TD><TD
align=right><U>Budget</U></TD><TD
align=right><U>Actual</U></TD></TR>"
Response.Write
"<TR><TD> </TD><TD> </TD><TD> </TD><TD>
</TD><TD> </TD><TD> </TD></TR>"
vSTAT = ""
Do While NOT oRSp.EOF
If vSHADE = "<TR BGCOLOR='#CFCFCF'><TD>" then
vSHADE = "<TR BGCOLOR='#EFEFEF'><TD>"
else
vSHADE = "<TR BGCOLOR='#CFCFCF'><TD>"
end if
If IsNull(oRSp("item_num").Value) then
Response.Write "<TR><TD><font color=red><b>There are No Items for
this project</b></font></TD></TR>"
else
Response.Write vSHADE & oRSp("department_name").Value
& "</TD><TD> "
Response.Write oRSp("combined_exp_type").Value & "</TD><TD> "
Response.Write oRSp("item_name").Value & "</TD><TD align=right> "
Response.Write FormatNumber(oRSp("bud_amt").Value,0,,-1,-1)
& "</TD><TD align=right> "
Response.Write FormatNumber(oRSp("act_amt").Value,0,,-1,-1)
& "</TD></TR>"
end if
oRSp.MoveNext
loop
Response.Write "</TABLE>"
oRSp.Close
Set oRSp=nothing
objConn.close
Set objConn=nothing
%>
asp that the user gets show
> The general idea is :
SELECT Department,Expenditure Type, SUM(Amount) FROM Table GROUP BY
Department,Expenditure Type.
Try this , I thin it should do what you need.
If its more complicated in your case send me your SQL, I'll take a look.
Oleg.
-----Original Message-----
From: Kathy Bond [mailto:klbond@n...]
Sent: April 10, 2002 4:02 PM
To: ASP Web HowTo
Subject: [asp_web_howto] RE: What's the Best Way to Sub Total Recordset
Table Rows?
Oleg,
I am brand new at coding so I don't follow what you're suggesting. I used
group by when the database records were selected into the recordset.
How can I do group by again and get the totals to display and also show
what total it is (Exp Total, Dept Total, Grand Total)?
Can you show a snippet code example for this?
Thanks, Kathy
> You can let SQL to make this job for you.
Just create another query that will calculate it for you.
and you can group it by department so you can summary for every department.
Oleg.
-----Original Message-----
From: Kathy Bond [mailto:klbond@n...]
Sent: April 10, 2002 11:33 AM
To: ASP Web HowTo
Subject: [asp_web_howto] What's the Best Way to Sub Total Recordset
Table Rows?
Hello,
I have an asp query selecting records from my database into a recordset
and displaying them in a table format (using response.writes):
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
Communications Bill Comm Systems 14,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
I want to put in sub-totals by department and expenditure type and a grand
total. The only way I know to do this is to use standard logic that
stores the value of the last dept and expenditure type and compare it to
their current values, then break and response.write the sub-totals.
I am new at coding, so is there an easier (or better) way to do this in
asp to get:
Department Expenditure Type Item Amount
Communications Capital Comm Systems 50,000
Communications Capital Systems 5,000
--------
Exp Total 55,000
Communications Bill Comm Systems 14,000
--------
Exp Total 14,000
Dept Total 69,000
Mechanical Capital Power Equip 2,000
Mechanical Capital Other Equip 25,000
--------
Exp Total 27,000
Dept Total 27,000
Grand Total 96,000
Thanks, Kathy
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
|
|
|
View Cart
