Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Apr 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How can I display table data along with column totals on asp page

    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!

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    New Coder
    Join Date
    Apr 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Here's the way that you would do this: It takes 2 queries, one you have already written, one below.

    Code:
    <!-- 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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #5
    New Coder
    Join Date
    Apr 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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>

  • #6
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •