- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 多栏式会计账 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=169124)
-- 发布时间：2021/6/3 14:51:00
-- 多栏式会计账
此主题相关图片如下：qq图片20210603144544.png
这样的数据源结构，(注：截图上没有管理费用，现在是编代码，暂不纠结具体数据。）
做成这样的结构：
此主题相关图片如下：qq图片20210603143527.png
统计数据源表一级科目为管理费用、安二级科目作列表项目的借方金额，并计算出本月发生额，本年累计，第一张统计表中的借方合计为本行合计，贷方合计等于借方本月发生额（即本月的借方合计总额）。
我看过交叉统计方法，做了一下，红色标注报错：
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
[此贴子已经被作者于2021/6/3 14:51:28编辑过]
-- 发布时间：2021/6/3 14:55:00
--
g = New Subtotalgroup
Subtotalgroup是汇总，汇总没有VGroups.AddDef这种用法
-- 发布时间：2021/6/3 15:07:00
--
我知道没这种用法，才不知怎么做，它是交叉统计，怎样实现自定义汇总名称和汇总形式？即有本月发生额，也有本年累计，这才是会计真正要求的统计方式，帮助里的累计是一直累下去，不是一年归一年的。
[此贴子已经被作者于2021/6/3 15:37:16编辑过]
-- 发布时间：2021/6/3 15:16:00
--
一年归一年的累计之前已经给过类似的用法，自行翻一下
-- 发布时间：2021/6/3 15:31:00
--
早看了，就是上面的代码，它是分组统计，我是交叉统计，还要在添加分组汇总下还循环计算分组列数据，我已经弄了2天了，还是不行。
-- 发布时间：2021/6/3 15:34:00
--
原来分组汇总的代码：
Dim g As Subtotalgroup
a.SubtotalGroups.Clear()
Dim cmd As New SQLCommand
Dim dt As DataTable
cmd.CommandText = "Select 日期,字号,凭证号,摘要,借方数量,借方金额,贷方数量,贷方金额 fro m {财务数据源} Where [日期] >= #" & e.Form.Controls("StartDate").Value _
& "# And [日期] <= #" & e.Form.Controls("EndDate").Value & "# And [会计科目辅助列] =\'" & e.Form.Controls("DropBox1").Value & "\'"
dt = cmd.ExecuteReader()
\'对临时进行分组统计
Dim b As New GroupTableBuilder("统计表1",dt)
b.Groups.AddDef("日期",DateGroupEnum.Year,"年")
b.Groups.AddDef("日期","月")
b.Groups.AddDef("字号")
b.Groups.AddDef("凭证号")
b.Groups.AddDef("摘要")
b.Totals.AddDef("借方数量","借方_数量")
b.Totals.AddDef("借方金额","借方_金额")
b.Totals.AddDef("贷方数量","贷方_数量")
b.Totals.AddDef("贷方金额","贷方_金额")
b.Build(True)
Tables("三栏式数量金额账_Table1").DataSource = b.BuildDataSource
g = New Subtotalgroup
g.GroupOn = "月"
g.TotalOn = "借方_数量,借方_金额,贷方_数量,贷方_金额"
g.Caption = "本月发生额"
a.SubtotalGroups.Add(g)
g = New Subtotalgroup
g.GroupOn = "月"
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)
r("借方_金额") = a.Compute("Sum(借方_金额)",f)
r("贷方_数量") = a.Compute("Sum(贷方_数量)",f)
r("贷方_金额") = a.Compute("Sum(贷方_金额)",f)
End If
Next
交叉统计还要再循环计算分列数据。
[此贴子已经被作者于2021/6/3 16:13:38编辑过]
-- 发布时间：2021/6/3 15:35:00
--
请上传实例说明
-- 发布时间：2021/6/3 15:51:00
--
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:多栏式账.foxdb |
-- 发布时间：2021/6/3 16:52:00
--
下面代码是在命令窗口测试的，自己改回按钮的用法。可以打开生成的统计表1的表结构看看交叉统计生成的统计表列名是怎么样的，然后结合代码看看怎么处理这种列名
\'对临时进行分组统计
Dim b As New CrossTableBuilder("统计表1",DataTables("财务数据源"))
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()
Dim a As Table = Tables("统计表1")
Dim g As Subtotalgroup
a.SubtotalGroups.Clear()
Dim s As String = ""
For Each c As Col In a.Cols
If c.Name Like "借方金额_*"
s = s & "," & c.Name
End If
Next
g = New Subtotalgroup
g.GroupOn = "月"
g.TotalOn = s.Trim(",")
g.Caption = "本月发生额"
a.SubtotalGroups.Add(g)
g = New Subtotalgroup
g.GroupOn = "月"
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("年") = "本年累计"
For Each c As Col In a.Cols
If c.Name Like "借方金额_*"
r(c.Name) = a.Compute("Sum(" & c.Name & ")",f)
End If
Next
End If
Next
Dim ckb As DataTable = a.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
ckb.DataCols("借方合计").SetFormat("#,###.##")
ckb.DataCols("贷方合计").SetFormat("#,###.##")
a.SetHeaderRowHeight(45)
-- 发布时间：2021/6/3 17:18:00
--
其实这表要统计借方金额的数据，你看截图2统计出来的效果，它并没有出现借方金额名称，
Dim s As String = ""
For Each c As Col In a.Cols
If c.Name Like "借方金额_*"
s = s & "," & c.Name
End If
Next
所以这段才码把名称组成一个集合，下面又把它分开成各列的本月发生额，行不通，要分也是动态生成的二级科目列名，我想大概要用到双循环？具体什么写不态懂，绕！
[此贴子已经被作者于2021/6/3 17:19:46编辑过]