Dim g As New GroupTableBuilder("月末汇总", DataTables("运单管理"))
g.Groups.AddDef("部门")
g.Groups.AddDef("上游客户")
g.Groups.AddDef("车辆类型")
g.Totals.AddDef("上游金额")
g.Totals.AddDef("已付成本")
g.Totals.AddDef("计提成本")
g.Totals.AddDef("总成本")
g.Totals.AddDef("毛利润")
g.VerticalTotal = True
g.Decimals = 2
g.SubTotal = True
g.FromServer = True
g.Build()
MainTable = Tables("月末汇总")
With Tables("月末汇总")
.MergeMode = MergeModeEnum.Standard '标准合并模式
.MergeCols.Clear() '清除原来的合并列
.MergeCols.Add("部门") '加入要合并的列
.MergeSort = "部门" '设置合并模式下的排序方式
.AllowMerge = True '启用合并模式
End With
Dim t1 As Table = Tables("月末汇总")
Dim g1 As Subtotalgroup
t1.SubtotalGroups.Clear()
t1.GroupAboveData = False
t1.TreeVisible = False
t1.SpillNode = True
g1 = New Subtotalgroup
g1.Aggregate = AggregateEnum.Sum
g1.GroupOn = "上游客户"
g1.TotalOn = "上游金额,已付成本,计提成本,总成本,毛利润"
g1.Caption = "{0} 小计"
t1.SubtotalGroups.Add(g1)
Dim Products As List(Of String) = DataTables("月末汇总").GetValues("车辆类型")
For Each s As String In Products
g1 = New Subtotalgroup
g1.Aggregate = AggregateEnum.Sum
g1.GroupOn = "部门"
g1.Caption = "{0} " & s & " 小计_"
t1.SubtotalGroups.Add(g1)
Next
g1 = New Subtotalgroup
g1.Aggregate = AggregateEnum.Sum
g1.GroupOn = "部门"
g1.TotalOn = "上游金额,已付成本,计提成本,总成本,毛利润"
g1.Caption = "{0} 合计"
t1.SubtotalGroups.Add(g1)
Dim Ps As List(Of String) = DataTables("月末汇总").GetValues("车辆类型")
For Each m As String In Ps
g1 = New Subtotalgroup
g1.Aggregate = AggregateEnum.Sum
g1.GroupOn = "*"
g1.Caption = "{0} " & m & " 合计_"
t1.SubtotalGroups.Add(g1)
Next
g1 = New Subtotalgroup
g1.Aggregate = AggregateEnum.Sum
g1.GroupOn = "*"
g1.TotalOn = "上游金额,已付成本,计提成本,总成本,毛利润"
g1.Caption = "总计"
t1.SubtotalGroups.Add(g1)
t1.Subtotal()
Dim r As Row
Dim arr() As String
With Tables("月末汇总")
For i As Integer = .Rows.Count(True) - 1 To 0 Step -1 'Count加上参数True
r = .Rows(i,True) 'Rows也需要加上参数True
If r.IsGroup AndAlso r("部门") Like "*小计_" Then '如果是分组行
arr = r("部门").split(" ")
r("部门") = Nothing
r("车辆类型") = arr(1) & "小计"
r("已付成本") = .Compute("sum(已付成本)","部门='" & arr(0) & "' And 车辆类型='" & arr(1) & "'")
r("计提成本") = .Compute("sum(计提成本)","部门='" & arr(0) & "' And 车辆类型='" & arr(1) & "'")
End If
Next
End With
Dim rr As Row
Dim ar() As String
With Tables("月末汇总")
For ii As Integer = .Rows.Count(True) - 1 To 0 Step -1 'Count加上参数True
rr = .Rows(ii,True) 'Rows也需要加上参数True
If rr.IsGroup AndAlso rr("部门") Like "*合计_" Then '如果是分组行
ar = rr("部门").split(" ")
rr("部门") = Nothing
rr("车辆类型") = ar(1) & "小计"
rr("已付成本") = .Compute("sum(已付成本)","部门='" & ar(0) & "' And 车辆类型='" & ar(1) & "'")
rr("计提成本") = .Compute("sum(计提成本)","部门='" & ar(0) & "' And 车辆类型='" & ar(1) & "'")
End If
Next
End With
加好友 
发短信
Post By：2020/12/2 22:00:00 [只看该作者]
此主题相关图片如下：汇总.png
