- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- 组合统计后列的位置调整、列组合及合计问题 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=116159)
-- 发布时间：2018/3/20 17:13:00
-- 组合统计后列的位置调整、列组合及合计问题
此主题相关图片如下：2.png
问题如下：1，生成的表的“微信验证率”列的位置可以放到“微信验证人数”列后面吗？
2、由于有两个交叉统计表同时生成“2018年02”，“2018年03”……，能否合并在一起？
3、生成的tables窗口表合计栏无合计
此主题相关图片如下：1.png
数据
此主题相关图片如下：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
-- 发布时间：2018/3/20 19:02:00
--
问题比较复杂，做一个具体实例上来测试吧。
-- 发布时间：2018/3/20 21:08:00
--
我也需要