组合统计后列的位置调整、列组合及合计问题 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

此主题相关图片如下：2.png

问题如下：1，生成的表的“微信验证率”列的位置可以放到“微信验证人数”列后面吗？

2、由于有两个交叉统计表同时生成“2018年02”，“2018年03”……，能否合并在一起？

3、生成的tables窗口表合计栏无合计
图片点击可在新窗口打开查看

此主题相关图片如下：1.png

数据

请老师明示，谢谢

代码如下：

Dim fxlb As WinForm.ComboBox = e.Form.Controls("分校列表")

Dim Filter0 As String

Dim Filter1 As String

If fxlb.value="所有校区" Then'

Filter0="分校名称 Not Like '%测试分校%'"

Filter1="所属分校 Not Like '%测试分校%'"

Else

Filter0="分校名称 ='" & fxlb.value & "'"

Filter1="所属分校 ='" & fxlb.value & "'"

End If

Dim bd0 As New SQLJoinTableBuilder("主要运营指标","分校")

bd0.C

Dim dt0 As fxDataSource

bd0.AddCols("分校编号","分校名称","分校主管","成立时间")

bd0.Filter= Filter0

dt0 = bd0.BuildDataSource()

Dim bd1 As New SQLGroupTableBuilder("统计表1","学员信息")

bd1.C

Dim dt1 As fxDataSource

bd1.Groups.AddDef("所属分校")

bd1.Totals.Adddef("性别",AggregateEnum.Count,"在校人数")

bd1.Totals.AddExp("其中_男生","Case When 性别='男' Then 1 Else 0 End") '对金额进行统计

bd1.Totals.AddExp("其中_女生","Case When 性别='女' Then 1 Else 0 End") '对金额进行统计

bd1.Totals.AddExp("微信验证人数","Case When openid > '' Then 1 Else 0 End") '对进行统计

bd1.Filter=Filter1 & "and 是否在校='在校'"

dt1 = bd1.BuildDataSource()

Dim bd2 As New SQLGroupTableBuilder("统计表2","报名缴费")

bd2.C

Dim dt2 As fxDataSource

bd2.Groups.AddDef("所属分校")

bd2.Totals.Adddef("缴费编号",AggregateEnum.Count,"当前课次人数")

bd2.Totals.AddExp("打印次数","Case When 是否打印 = '已打印' Then 1 Else 0 End") '进行统计

bd2.Filter=Filter1 & "And 剩余课时 > 0"

dt2 = bd2.BuildDataSource()

Dim bd3 As New sqlCrossTableBuilder("统计表3", "报名缴费")

bd3.C

Dim dt3 As fxDataSource

bd3.HGroups.AddDef("所属分校")

bd3.VGroups.AddExp("缴费年月","CONVERT(varchar,year(缴费日期)) + '年' + Right(100+Month(缴费日期),2) + '月'")

bd3.Vgroups.AddExp("半月","Case When day(缴费日期) <= 15 Then '上' Else '下' End", "{0}半月份长期班招生" )

bd3.Totals.AddDef("缴费编号", AggregateEnum.Count, "长期班(人)")

bd3.Totals.AddExp("续报(人)","Case When 是否续报='续报' Then 1 Else 0 End") '对续报进行统计

bd3.Totals.AddExp("收费合计", "收费合计 - 材料费")

bd3.Filter = Filter1 & "And 班别='长期班' And year(缴费日期)>=2018 "

dt3 = bd3.BuildDataSource()

Dim bd4 As New sqlCrossTableBuilder("统计表4", "报名缴费")

bd4.C

Dim dt4 As fxDataSource

bd4.HGroups.AddDef("所属分校")

bd4.VGroups.AddExp("补缴年月","CONVERT(varchar,year(补缴时间)) + '年' + Right(100+Month(补缴时间),2) + '月'")

bd4.Vgroups.AddExp("半月","Case When day(补缴时间) <= 15 Then '上' Else '下' End", "{0}半月份长期班招生" )

bd4.Totals.AddExp("补缴(人数)", "Case When [补缴金额] > 0 Then 1 Else 0 End")

bd4.Totals.AddDef("补缴金额","补缴合计")

bd4.Filter = Filter1 & "And year(补缴时间)>=2018 "

dt4 = bd4.BuildDataSource()

dt0.Combine("分校名称",dt1,"所属分校")

dt0.Combine("分校名称",dt2,"所属分校")

dt0.Combine("分校名称",dt3,"所属分校")

dt0.Combine("分校名称",dt4,"所属分校")

Tables(e.Form.name & "_table1").DataSource =dt0

With DataTables(e.Form.name & "_table1").DataCols '用于增加表达式列计算需要数值

.Add("微信验证率",Gettype(Double), "[微信验证人数] /[在校人数]")

End With

DataTables(e.Form.name & "_table1").dataCols("微信验证率").setFormat("#0.00%")

DataTables(e.Form.name & "_table1").dataCols("成立时间").SetDateTimeFormat(DateTimeFormatEnum.Date) '格式化成立时间列格式

Tables(e.Form.name & "_table1").AllowDragColumn=True '允许拖动列

Tables(e.Form.name & "_table1").AllowFreezeColumn=True '允许拖动冻结列

'Tables(e.Form.name & "_table1").AllowClickSort=True '单击排序

Tables(e.Form.name & "_table1").Sort="成立时间"

Tables(e.Form.name & "_table1").GrandTotal=True

Tables(e.Form.name & "_table1").AutoSizeCols()

Tables(e.Form.name & "_table1").Cols.Frozen= 4

主题：组合统计后列的位置调整、列组合及合计问题