p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 1.0 and 1.1 Basics (http://p2p.wrox.com/forumdisplay.php?f=60)
-   -   How to make subtotal of a column (http://p2p.wrox.com/showthread.php?t=3657)

Sanjay Bajpai September 9th, 2003 06:53 AM

How to make subtotal of a column
 
I am writing a report on asp i selected the data
from oracle table
"select challan date challan no, Order no, amount from table x
where challan date = (date input)"

the data are as follow :
challan no. Order no. amount(rs)
10 2 2000
11 3 1000
12 2 4000
13 5 3500
14 3 5000


Now my requirment is like this

challan no. order no. amount
10 2 2000
12 2 4000
subtotal (amount) 6000

11 3 1000
14 3 5000
subtotal (amount) 6000

13 5 3500
subtotal (amount 3500

Pl. write me how to program in asp to get desired output
input is only date


melvik September 9th, 2003 07:53 AM

I guess u should put it in a dataset & then make a counter by urself.

Always:),
Hovik Melkomian.

planoie September 9th, 2003 08:34 AM

So you need to group by "order no." and then display a separate table for each "order no." with a corresponding subtotal. Normally I would provide a suggestion on how to do this in your SQL statement, but it would typically involve a stored procedure. And you asked for an ASP solution so here's some pseudo code...

Modify your query to order by "order no."

nLastOrderNo = firstRecord("order no.")
for each record
    if nLastOrderNo <> currentRecord("order no")
        output total row with nTotalAmount
        nTotalAmount = 0
    end if
    output 1 record
next record
output total row for final total in nTotalAmount

Hope that makes sense.

Peter

Sanjay Bajpai September 11th, 2003 07:00 AM

sorry planoie i tried your suggestion but could not get
through. Actualy there is no currentrecord concept in ASP
In any case we have to match first to next record then only
we can get subtotal of that column.
Pl. suggest something more


Thanks
[V]



jacob September 11th, 2003 07:23 AM

OK, let me have a go!

What technology do you use!? You say "...there is no currentrecord concept in ASP", but it is an ASPX forum.

Jacob.

jacob September 11th, 2003 08:22 AM

Ok, first of all you have to order the data when executing the SQL. Append somthing like...

ORDER BY 'orderNo'

...to you sql statement. Be careful with the quotes, and make sure that the orderNo name above corresponds to your table.

Then when you have got your recordset you can do like this (some would probably argue with the way output is done, but you can change that!)...

Dim subTotal
subTotal = 0
Dim currentOrderNo
currentOrderNo = -1
Dim tmp

If Not rSet.EOF Then
  Response.Write("<TABLE CELLSPACING='3' CELLPADDING='3' BORDER='1'>")
  While Not rSet.EOF
    If CInt(rSet("orderNo")) <> currentOrderNo Then
      Response.Write("<TR><TD>challan no.</TD><TD>order no.</TD><TD>amounts</TD></TR>")
      If currentOrderNo <> -1 Then
        tmp = "<TD COLSPAN='2'><B>sub total</B></TD>"
        tmp = tmp & "<TD ALIGN='right'><B>" & subTotal & "</B></TD>"
        Response.Write("<TR>" & tmp & "<TR>")
      End If
      subTotal = 0
    End If
    currentOrderNo = CInt(rSet("orderNo"))
    tmp = "<TD ALIGN='right'>" & rSet("challanNo") & "</TD>"
    tmp = tmp & "<TD ALIGN='right'>" & rSet("orderNo") & "</TD>"
    tmp = tmp & "<TD ALIGN='right'>" & rSet("amount") & "</TD>"
    Response.Write("<TR>" & tmp & "</TR>")
    subTotal = subTotal + CInt(rSet("amount"))
    rSet.MoveNext
Wend
Response.Write("</TABLE>")

End If

As you can see I have tried to output the data in a table, however the above code has NOT been tested since I do not have such database structure. Moreover I cannot remember the conversion to integer but I think is CInt.

Before you can use the code above you have to make sure that the names of the columns in your database corresponds to the ones I have used. I use orderNo, amount, challanNo. Furthermore no record can have challan no = -1.

Well, that was a lot of code without running it (no garanties [:p])! I hope that you are able to use it. Let me know!?

I am no 100% sure but I think that you can actually do the work in pure SQl using the SUM function, however I cannot find my SQL book :)

Jacob.

Sanjay Bajpai September 12th, 2003 05:40 AM

Quote:

quote:Originally posted by jacob
 OK, let me have a go!

What technology do you use!? You say "...there is no currentrecord concept in ASP", but it is an ASPX forum.

Jacob.
hi jacob
thanks for your codes, but i have solved the problem
it was very simple. The problem was when it was run "exception occured" was coming and program was terminated. we just wrote on error resume next. We got a positive output
u can have a look which is in ASP. I want a help from u why exception
occured message comes. if I do give on error resume next.
:)
    str = "select td_totamt,td_wono from td_da order by td_wono "
        rs1.Open str,con
       while not rs1.eof
        fstwono=rs1("td_wono")
           str2 = "select sum(td_totamt),td_wono from td_da where td_wono='"&fstwono&"' group by td_wono"
        rs2.Open str2,con
        %>
        <TD><% Response.Write "rs1 PONO" & rs1("td_wono")%></TD>
        <TD><%Response.Write rs1("td_totamt")%></TD> </tr>
            <% sum1=sum1+cint(rs1("td_totamt"))
        rs1.MoveNext
        on error resume next
       if (fstwono)<>(rs1("td_wono"))then%>
      <tr><TD><%' Response.Write rs2("sum(td_totamt)")%></TD></tr>
      <tr><TD><% Response.Write sum1 %></TD></tr>
        <% sum1=0
        end if

        'rs2.MoveNext
        rs2.Close
    wend
        rs1.Close
     %>



jacob September 14th, 2003 02:16 PM

The code you posted fails?

If it does I would guess that it is because you cannot extract the sum from the recordset the way you do! You do this...

rs2("sum(td_totamt)")

I am however not sertain about this. I can see that you have made this line a comment, so does the page work when you do not include the line?! Youd o not include the movenext command either.

There is no space before 'then' in this line...

if (fstwono)<>(rs1("td_wono"))then%>

And to extract the sum value... Are you able to get values from the recordset with an index instead of a label?!

Jacob.



All times are GMT -4. The time now is 03:09 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.