...

View Full Version : How can I display table data along with column totals on asp page



smclane
08-29-2007, 10:30 PM
Hi - I need to display table data along with column totals at the bottom of each column on an asp page. Does anyone know if this is possible and what the code would be? Thanks for your help!:o

Daemonspyre
08-30-2007, 02:02 PM
This would be done by your SQL statement.

You may have to run 2 queries, but you should be able to do it in one if you use the right syntax.

If you could post an example of your table rows/structure and a sample row or two, we can help you write it.

smclane
08-30-2007, 03:55 PM
I'm planning to display the information in Excel. I need to display the table columns and values with totals at the bottom of columns:

<%
SQL = "SELECT * FROM CommHealthEvents WHERE CommHealthEvents.EventDate BETWEEN '" & fromDate & "' AND '"& endDate & "'AND CommHealthEvents.DeptName LIKE '" & Dept & "' ORDER BY EventDate"
CommEvent.Open SQL, DataConn
%>

<!-- Table which will be translated into an Excel spreadsheet -->
<TABLE WIDTH=75% BORDER=1 CELLSPACING=1 CELLPADDING=1>
<TR>
<TD><b>Event Code</b></TD>
<TD><b>Event Date</b></TD>
<TD><b>Program Name</b></TD>
<TD><b>Description</b></TD>
<TD><b>SAH Requestor</b></TD>
<TD><b>CAT</b></TD>
<TD><b>Number of People</b></TD>
<TD><b>Total Revenue</b></TD>
<TD><b>Operating Expense</b></TD>
<TD><b>Total Facility</b></TD>
<TD><b>Salary Expense</b></TD>
<TD><b>Hourly Expense</b></TD>

</TR>
<!-- server-side loop adding Table entries -->
<% do while not CommEvent.EOF %>
<TR>
<TD><%=CommEvent("EventCode")%></TD>
<TD><%=CommEvent("EventDate")%></TD>
<TD><%=CommEvent("ProgramName")%></TD>
<TD><%=CommEvent("Description")%></TD>
<TD><%=CommEvent("SAHRequestor")%></TD>
<TD><%=CommEvent("CAT")%></TD>
<TD><%=CommEvent("NumberPeople")%></TD>
<TD><%=CommEvent("TotalRevenue")%></TD>
<TD><%=CommEvent("OperatingExpense")%></TD>
<TD><%=CommEvent("TotalFacility")%></TD>
<TD><%=CommEvent("SalaryExpense")%></TD>
<TD><%=CommEvent("HourlyExpense")%></TD>

</TR>

Thank you for your help!

Daemonspyre
08-30-2007, 04:14 PM
Here's the way that you would do this: It takes 2 queries, one you have already written, one below.


<!-- server-side loop adding Table entries -->
<% do while not CommEvent.EOF %>
<TR>
<TD><%=CommEvent("EventCode")%></TD>
<TD><%=CommEvent("EventDate")%></TD>
<TD><%=CommEvent("ProgramName")%></TD>
<TD><%=CommEvent("Description")%></TD>
<TD><%=CommEvent("SAHRequestor")%></TD>
<TD><%=CommEvent("CAT")%></TD>
<TD><%=CommEvent("NumberPeople")%></TD>
<TD><%=CommEvent("TotalRevenue")%></TD>
<TD><%=CommEvent("OperatingExpense")%></TD>
<TD><%=CommEvent("TotalFacility")%></TD>
<TD><%=CommEvent("SalaryExpense")%></TD>
<TD><%=CommEvent("HourlyExpense")%></TD>
</TR>

<% Loop
'
CommEvent.Close
'
'
SQL2 = "SELECT SUM(NumberPeople) 'NumPeopleSum', SUM(TotalRevenue) 'TotRevSum', SUM(OperatingExpense) 'OpExpenseSum', SUM(TotalFacility) 'TotFacSum', SUM(SalaryExpense) 'SalSum', SUM(HourlyExpense) 'HourlySum' WHERE CommHealthEvents.EventDate BETWEEN '" & fromDate & "' AND '"& endDate & "'AND CommHealthEvents.DeptName LIKE '" & Dept & "' GROUP BY NumberPeople, TotalRevenue, OperatingExpense, TotalFacility, SalaryExpense, HourlyExpense"
'
CommEvent.Open SQL2, DataConn
%>
<TR>
<TD>&nbsp;</TD>
<TD>&nbsp;</TD>
<TD>&nbsp;</TD>
<TD>&nbsp;</TD>
<TD>&nbsp;</TD>
<TD>&nbsp;</TD>
<TD><%=CommEvent("NumPeopleSum")%></TD>
<TD><%=CommEvent("TotRevSum")%></TD>
<TD><%=CommEvent("OpExpenseSum")%></TD>
<TD><%=CommEvent("TotFacSum")%></TD>
<TD><%=CommEvent("SalSum")%></TD>
<TD><%=CommEvent("HourlySum")%></TD>
</TR>
<% CommEvent.Close
Set CommEvent = nothing %>


As your first 4 columns cannot be aggregated (they are names), I have left them out.

In order to do any SUM, MIN, MAX, AVG, etc calculations, you have to use the GROUP BY statement.

As I don't have your data, I can't test this, but I am pretty sure that it will work as is.

Let me know if you need any other help.

smclane
08-31-2007, 04:32 PM
Hi - thanks for your help! It is sort of working. If I display the results for one row, it works. If I display multiple rows, it doesn't display any totals. I'm sure I've done something wrong. Could you take a look to see what I didn't do? Thank you!

' Get a recordset of info from table...
SQL = "SELECT * FROM CommHealthEvents WHERE CommHealthEvents.EventDate BETWEEN '" & fromDate & "' AND '"& endDate & "' AND CommHealthEvents.DeptName LIKE '" & Dept & "' ORDER BY EventDate"
CommEvent.Open SQL, DataConn
%>

<!-- Table which will be translated into an Excel spreadsheet -->
<TABLE WIDTH=100% BORDER=1 CELLSPACING=1 CELLPADDING=1>
<TR>
<TD><b>Event Code</b></TD>
<TD><b>Event Date</b></TD>
<TD><b>Program Name</b></TD>
<TD><b>Description</b></TD>
<TD><b>SAH Requestor</b></TD>
<TD><b>CAT</b></TD>
<TD><b>Number of People</b></TD>
<TD><b>Total Revenue</b></TD>
<TD><b>Operating Expense</b></TD>
<TD><b>Total Facility</b></TD>
<TD><b>Salary Expense</b></TD>
<TD><b>Hourly Expense</b></TD>
</TR>

<!-- server-side loop adding Table entries -->
<% do while not CommEvent.EOF %>
<TR>
<TD><%=CommEvent("EventCode")%></TD>
<TD><%=CommEvent("EventDate")%></TD>
<TD><%=CommEvent("ProgramName")%></TD>
<TD><%=CommEvent("Description")%></TD>
<TD><%=CommEvent("SAHRequestor")%></TD>
<TD><%=CommEvent("CAT")%></TD>
<TD><%=CommEvent("NumberPeople")%></TD>
<TD><%=CommEvent("TotalRevenue")%></TD>
<TD><%=CommEvent("OperatingExpense")%></TD>
<TD><%=CommEvent("TotalFacility")%></TD>
<TD><%=CommEvent("SalaryExpense")%></TD>
<TD><%=CommEvent("HourlyExpense")%></TD>
</TR>

<% CommEvent.MoveNext
Loop

CommEvent.Close

SQL2 = "SELECT SUM(NumberPeople) 'NumPeopleSum', SUM(TotalRevenue) 'TotRevSum', SUM(OperatingExpense) 'OpExpenseSum',
SUM(TotalFacility) 'TotFacSum', SUM(SalaryExpense) 'SalSum', SUM(HourlyExpense) 'HourlySum' FROM CommHealthEvents WHERE CommHealthEvents.EventDate BETWEEN '" & fromDate & "' AND '"& endDate & "'AND CommHealthEvents.DeptName LIKE '" & Dept & "' GROUP BY NumberPeople, TotalRevenue, OperatingExpense, TotalFacility, SalaryExpense, HourlyExpense"
CommEvent.Open SQL2, DataConn
%>
<TR>
<TD>&nbsp;</TD>
<TD>&nbsp;</TD>
<TD>&nbsp;</TD>
<TD>&nbsp;</TD>
<TD>&nbsp;</TD>
<TD>&nbsp;</TD>
<TD><%=CommEvent("NumPeopleSum")%></TD>
<TD><%=CommEvent("TotRevSum")%></TD>
<TD><%=CommEvent("OpExpenseSum")%></TD>
<TD><%=CommEvent("TotFacSum")%></TD>
<TD><%=CommEvent("SalSum")%></TD>
<TD><%=CommEvent("HourlySum")%></TD>
</TR>

<%

' Clean up
CommEvent.Close
set CommEvent = Nothing
DataConn.Close
set DataConn = Nothing
%>
</TABLE>
</BODY>
</HTML>

Daemonspyre
08-31-2007, 04:46 PM
It looks OK to me, but there are a couple of questions:

Is it not displaying the fields in Excel or in your HTML?
Are you getting any errors?
Can you run the totals SQL query in your db program and get the answers you want?

I'm sorry I can't be of more help, but I can't see your data or table structure, so I can't test this out.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum