Dim a As Table = Tables("多栏式明细账_Table1")
Dim drs As List(Of DataRow) = DataTables("财务数据源").Select("[日期] >= #" & e.Form.Controls("StartDate").Value & "# And [日期] <= #" & e.Form.Controls("EndDate").Value & "# And [会计科目辅助列] ='" & e.form.controls("combobox1").items(0) & "'")
Dim g As Subtotalgroup
a.SubtotalGroups.Clear()
Dim cmd As New SQLCommand
Dim dt As DataTable
cmd.CommandText = "Select 日期,字号,凭证号,摘要,二级科目,借方金额,贷方金额 fr om {财务数据源} Where [日期] >= #" & e.Form.Controls("StartDate").Value _
& "# And [日期] <= #" & e.Form.Controls("EndDate").Value & "# And [凭证号] ='" & drs("凭证号") & "'And [会计科目辅助列] <>'" & e.Form.Controls("combobox1").Value & "'"
dt = cmd.ExecuteReader()
'对临时进行分组统计
Dim b As New CrossTableBuilder("统计表1",dt)
b.HGroups.AddDef("日期",DateGroupEnum.Year,"年")
b.HGroups.AddDef("日期","月")
b.HGroups.AddDef("字号")
b.HGroups.AddDef("凭证号")
b.HGroups.AddDef("摘要")
b.VGroups.AddDef("二级科目","{0}")
b.Totals.AddDef("借方金额")
b.Build(True)
Tables("多栏式明细账_Table1").DataSource = b.BuildDataSource
g = New Subtotalgroup
g.GroupOn = "月"
g.VGroups.AddDef("二级科目","{0}")
g.TotalOn = "借方金额"
g.Caption = "本月发生额"
a.SubtotalGroups.Add(g)
g = New Subtotalgroup
g.GroupOn = "月"
g.VGroups.AddDef("二级科目","{0}")
g.Totals.AddDef("借方金额")
g.Caption = "{0}月"
a.SubtotalGroups.Add(g)
a.Sort = "年,月"
a.Subtotal(True)
Dim r As Row
For i As Integer = 0 To a.Rows.Count(True) - 1
r = a.Rows(i,True)
If r.IsGroup AndAlso r("年") Like "*月" '如果是分组行
Dim f As String = "年 = " & a.Rows(i-2,True)("年") & " And 月 <= " & r("年").trim("月")
r("年") = "本年累计"
r("借方金额") = a.Compute("Sum(借方金额)",f)
End If
Next
Dim ckb As DataTable = e.Form.Controls("Table1").Table.DataTable
If ckb.DataCols.Contains("借或贷") = False And ckb.DataCols.Contains("借方合计") = False And ckb.DataCols.Contains("贷方合计") = False Then
ckb.DataCols.add("借或贷", Gettype(String))
ckb.DataCols.add("借方合计", Gettype(Double))
ckb.DataCols.add("贷方合计", Gettype(Double))
End If
Dim drs As List(Of DataRow) = DataTables("多栏式明细账_Table1").Select("[凭证号] Is Not Null")
Dim nms As List(Of String) = DataTables("财务数据源").SQLGetUniqueValues("[会计科目辅助列] ='" & e.form.controls("combobox1").items(0) & "' And [日期] >= #" & e.Form.Controls("StartDate").Value & "# And [日期] <= #" & e.Form.Controls("EndDate").Value & "#" ,"二级科目")
Dim Sum As Integer
For Each nm As String In nms
Sum = Sum + drs(0)(nm)
DataTables("多栏式明细账_Table1").DataCols(nm).SetFormat("#,###.##")
Next
DataTables("多栏式明细账_Table1").DataCols("借方合计").SetFormat("#,###.##")
DataTables("多栏式明细账_Table1").DataCols("贷方合计").SetFormat("#,###.##")
Tables("多栏式明细账_Table1").SetHeaderRowHeight(45)
drs(0)("借或贷") = "借"
drs(0)("借方合计") = Sum
加好友 
发短信
Post By：2021/6/3 14:51:00 [只看该作者]
此主题相关图片如下：qq图片20210603144544.png
