Subtotals

  • Thread starter Thread starter Paul Ilacqua
  • Start date Start date
P

Paul Ilacqua

I'm using VB .NET and I'm trying to "roll my own" report subtotals. I'm
building a string from a datareader top pass to a reporting class and I want
to break and subtotal QTY at each change in shipper and I have struggled
with the code to do so for days and just can't seem to get it. I can get it
to insert a draw a line at a shipper change, but I can't get the subtotals.
The actual Code is down below data sample. I appreciate any advice I can
receive.
Paul


SHIP PART QTY
----------------------------------------------------
01082153 54472 180
01082155 54472 40
01082156 51166 8
01082156 51167 16
01082156 51168 8
01082156 51169 184
01082156 53442 8
01082157 51167 16
01082157 51168 8
01082157 51169 192
01082157 53442 8
01082158 51167 24
01082158 51168 8
01082158 51169 184
01082158 53442 8
01082159 50120 15
01082159 54827 90
01082159 54829 15
01082159 54830 225
01082160 54827 90

'code
While dr.Read
If iCounter = 0 Then
FirstPass = dr.GetString(0)
iCounter = CShort(iCounter + 1)
End If

iTotal = iTotal + dr.GetInt32(2)
CurrentPass = dr.GetString(0)

If CurrentPass <> FirstPass Then

FirstPass = CurrentPass
sb.Append(StrDup(80, "-") & vbCrLf)
sb.Append("Sub Total: " & SubTotal & vbCrLf)
sb.Append(StrDup(80, "-") & vbCrLf)
SubTotal = 0
sb.Append(dr.GetString(0).PadRight(15) _
& dr.GetString(1).PadRight(15) _
& dr.GetInt32(2).ToString.PadRight(15) _
& dr.GetString(3).PadRight(15) & dr.GetString(4) & vbCrLf)
Else
SubTotal = SubTotal + dr.GetInt32(2)
sb.Append(dr.GetString(0).PadRight(15) _
& dr.GetString(1).PadRight(15) _
& dr.GetInt32(2).ToString.PadRight(15) _
& dr.GetString(3).PadRight(15) & dr.GetString(4) & vbCrLf)
End If
End While
 
Paul said:
I'm using VB .NET and I'm trying to "roll my own" report subtotals. I'm
building a string from a datareader top pass to a reporting class and I
want to break and subtotal QTY at each change in shipper and I have
struggled with the code to do so for days and just can't seem to get it.
I can get it to insert a draw a line at a shipper change, but I can't
get the subtotals. The actual Code is down below data sample. I
appreciate any advice I can receive.
Paul


SHIP PART QTY
----------------------------------------------------
01082153 54472 180
01082155 54472 40
01082156 51166 8
01082156 51167 16
01082156 51168 8
01082156 51169 184
01082156 53442 8
01082157 51167 16
01082157 51168 8
01082157 51169 192
01082157 53442 8
01082158 51167 24
01082158 51168 8
01082158 51169 184
01082158 53442 8
01082159 50120 15
01082159 54827 90
01082159 54829 15
01082159 54830 225
01082160 54827 90

'code
While dr.Read
If iCounter = 0 Then
FirstPass = dr.GetString(0)
iCounter = CShort(iCounter + 1)
End If

iTotal = iTotal + dr.GetInt32(2)
CurrentPass = dr.GetString(0)

If CurrentPass <> FirstPass Then

FirstPass = CurrentPass
sb.Append(StrDup(80, "-") & vbCrLf)
Click to expand...

Ouch! Don't concatenate strings that you append to the StringBuilder.
You are using the StringBuilder so that you don't have to concatenate
strings. Also, it's totally superflous to create a string using StrDup,
as the Append method can do the job without creating the string.
sb.Append("Sub Total: " & SubTotal & vbCrLf)
sb.Append(StrDup(80, "-") & vbCrLf)
SubTotal = 0
sb.Append(dr.GetString(0).PadRight(15) _
& dr.GetString(1).PadRight(15) _
& dr.GetInt32(2).ToString.PadRight(15) _
& dr.GetString(3).PadRight(15) & dr.GetString(4) & vbCrLf)
Click to expand...

You forgot to add this item to the SubTotal.
Else
SubTotal = SubTotal + dr.GetInt32(2)
sb.Append(dr.GetString(0).PadRight(15) _
& dr.GetString(1).PadRight(15) _
& dr.GetInt32(2).ToString.PadRight(15) _
& dr.GetString(3).PadRight(15) & dr.GetString(4) & vbCrLf)
End If
End While
Click to expand...

This should work. It also creates about 80% less string objects.

Dim first As Boolean = True
Dim pass As String
Dim current As String
Dim quantity As Integer
Dim subTotal As Integer = 0
Dim ship As String
Dim quantityString As String
Dim info3 As String
Dim info4 As String

While dr.Read
If first Then
current = dr.GetString(0)
first = False
End If

ship = dr.GetString(0)
part = dr.GetString(1)
quantity = dr.GetInt32(2)
info3 = dr.GetString(3)
info4 = dr.GetString(4)

iTotal = iTotal + quantity
subTotal = subTotal + quantity
If ship <> current Then
current = ship
sb _
.Append("-"C, 80).AppendLine() _
.Append("Sub Total: ").Append(subTotal).AppendLine() _
.Append("-"C, 80).AppendLine()
subTotal = 0
End If
quantityString = quantity.ToString()
sb _
.Append(ship).Append(" "C, 15 - pass.Length) _
.Append(part).Append(" "C, 15 - part.Length) _
.Append(quantityString).Append(" "C, 15 - quantityString.Length) _
.Append(info3).Append(" "C, 15 - info3.Length) _
.Append(info4).AppendLine()
End While

[Disclaimer: The code is not tested and might contains minor errors.]
 
Göran Andersson said:
Ouch! Don't concatenate strings that you append to the StringBuilder.
You are using the StringBuilder so that you don't have to concatenate
strings. Also, it's totally superflous to create a string using StrDup,
as the Append method can do the job without creating the string.


You forgot to add this item to the SubTotal.


This should work. It also creates about 80% less string objects.

Dim first As Boolean = True
Dim pass As String
Dim current As String
Dim quantity As Integer
Dim subTotal As Integer = 0
Dim ship As String
Dim quantityString As String
Dim info3 As String
Dim info4 As String

While dr.Read
If first Then
current = dr.GetString(0)
first = False
End If

ship = dr.GetString(0)
part = dr.GetString(1)
quantity = dr.GetInt32(2)
info3 = dr.GetString(3)
info4 = dr.GetString(4)

iTotal = iTotal + quantity
subTotal = subTotal + quantity
Click to expand...

Correction: that line should of course be after the If statement.
If ship <> current Then
current = ship
sb _
.Append("-"C, 80).AppendLine() _
.Append("Sub Total: ").Append(subTotal).AppendLine() _
.Append("-"C, 80).AppendLine()
subTotal = 0
End If
quantityString = quantity.ToString()
sb _
.Append(ship).Append(" "C, 15 - pass.Length) _
Click to expand...

That should of course be ship.Length.
.Append(part).Append(" "C, 15 - part.Length) _
.Append(quantityString).Append(" "C, 15 - quantityString.Length) _
.Append(info3).Append(" "C, 15 - info3.Length) _
.Append(info4).AppendLine()
End While

[Disclaimer: The code is not tested and might contains minor errors.]
Click to expand...
 
Göran ,
I appreciate the feedback... naturally I'm a VB6 convert and used to love
my "&" operator. I was pretty good at it and was maintainable. However I do
want to learn the "proper" method to code .NET. Thank you very much.... I'm
using VS 2003 so I do not have the AppenLine() function but I did get it to
work.
Thanks again.
Paul

Göran Andersson said:
Paul said:
I'm using VB .NET and I'm trying to "roll my own" report subtotals. I'm
building a string from a datareader top pass to a reporting class and I
want to break and subtotal QTY at each change in shipper and I have
struggled with the code to do so for days and just can't seem to get it.
I can get it to insert a draw a line at a shipper change, but I can't get
the subtotals. The actual Code is down below data sample. I
appreciate any advice I can receive.
Paul


SHIP PART QTY
----------------------------------------------------
01082153 54472 180
01082155 54472 40
01082156 51166 8
01082156 51167 16
01082156 51168 8
01082156 51169 184
01082156 53442 8
01082157 51167 16
01082157 51168 8
01082157 51169 192
01082157 53442 8
01082158 51167 24
01082158 51168 8
01082158 51169 184
01082158 53442 8
01082159 50120 15
01082159 54827 90
01082159 54829 15
01082159 54830 225
01082160 54827 90

'code
While dr.Read
If iCounter = 0 Then
FirstPass = dr.GetString(0)
iCounter = CShort(iCounter + 1)
End If

iTotal = iTotal + dr.GetInt32(2)
CurrentPass = dr.GetString(0)

If CurrentPass <> FirstPass Then

FirstPass = CurrentPass
sb.Append(StrDup(80, "-") & vbCrLf)
Click to expand...

Ouch! Don't concatenate strings that you append to the StringBuilder. You
are using the StringBuilder so that you don't have to concatenate strings.
Also, it's totally superflous to create a string using StrDup, as the
Append method can do the job without creating the string.
sb.Append("Sub Total: " & SubTotal & vbCrLf)
sb.Append(StrDup(80, "-") & vbCrLf)
SubTotal = 0
sb.Append(dr.GetString(0).PadRight(15) _
& dr.GetString(1).PadRight(15) _
& dr.GetInt32(2).ToString.PadRight(15) _
& dr.GetString(3).PadRight(15) & dr.GetString(4) & vbCrLf)
Click to expand...

You forgot to add this item to the SubTotal.
Else
SubTotal = SubTotal + dr.GetInt32(2)
sb.Append(dr.GetString(0).PadRight(15) _
& dr.GetString(1).PadRight(15) _
& dr.GetInt32(2).ToString.PadRight(15) _
& dr.GetString(3).PadRight(15) & dr.GetString(4) & vbCrLf)
End If
End While
Click to expand...

This should work. It also creates about 80% less string objects.

Dim first As Boolean = True
Dim pass As String
Dim current As String
Dim quantity As Integer
Dim subTotal As Integer = 0
Dim ship As String
Dim quantityString As String
Dim info3 As String
Dim info4 As String

While dr.Read
If first Then
current = dr.GetString(0)
first = False
End If

ship = dr.GetString(0)
part = dr.GetString(1)
quantity = dr.GetInt32(2)
info3 = dr.GetString(3)
info4 = dr.GetString(4)

iTotal = iTotal + quantity
subTotal = subTotal + quantity
If ship <> current Then
current = ship
sb _
.Append("-"C, 80).AppendLine() _
.Append("Sub Total: ").Append(subTotal).AppendLine() _
.Append("-"C, 80).AppendLine()
subTotal = 0
End If
quantityString = quantity.ToString()
sb _
.Append(ship).Append(" "C, 15 - pass.Length) _
.Append(part).Append(" "C, 15 - part.Length) _
.Append(quantityString).Append(" "C, 15 - quantityString.Length) _
.Append(info3).Append(" "C, 15 - info3.Length) _
.Append(info4).AppendLine()
End While

[Disclaimer: The code is not tested and might contains minor errors.]
Click to expand...
 
Back
Top