- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- 组合多个统计，Subtotal，第二个统计表无效 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=117387)
-- 发布时间：2018/4/11 17:26:00
-- 组合多个统计，Subtotal，第二个统计表无效
Subtotal对交叉统计结果进行二次统计，生成汇总模式。
组合多个统计中第二个统计表无效。。
此主题相关图片如下：微信截图_20180411171903.png
代码：
Dim b As New SQLCrossTableBuilder("统计表1","consume_report")
b.C
Dim dt1 As fxDataSource
If Forms("窗口1").Controls("DropBox3").value = "整体" Then
ElseIf Forms("窗口1").Controls("DropBox3").value = "门店" Then
b.HGroups.AddDef("mall_name","门店名称") \'添加客户列用于水平分组
s001= "门店名称"
End If
If Forms("窗口1").Controls("DropBox4").value = "年报" Then
b.hGroups.AddDef("image_time",DateGroupEnum.Year,"年度") \'添加日期列用于垂直分组,按年分组
s002 = "年度"
End If
b.VGroups.Addexp("is_turnover", "case when (is_turnover = 1 or is_turnover = 0) then \'总客流\' end ") \'
If Forms("窗口1").Controls("CheckBox1").Checked = True Then
b.VGroups.Addexp("sex","case when sex = \'男\' then \'男\' when sex = \'女\' then \'女\' end ") \'
End If
If Forms("窗口1").Controls("CheckBox2").Checked = True Then
b.VGroups.AddExp("age"," Case when age <= 25 Then \'25岁以下\' when age >= 26 and age <= 30 then \'26-30岁\' when age >= 31 and age <= 35 then \'31-35岁\' when age >= 36 and age <= 45 then \'36-45岁\' else \'46岁以上\' end ")
End If
b.Totals.AddDef("imgage_id",AggregateEnum.count,"人数") \'添加数量列用于统计
\'b.VerticalTotal = True
b.Filter = filter
b.Subtotal = True \'生成汇总模式
b.SubtotalLevel = 0 \'只生成总计行
dt1 = b.BuildDataSource()
Dim b2 As New SQLCrossTableBuilder("统计表2","consume_report")
b2.C
Dim dt2 As fxDataSource
If Forms("窗口1").Controls("DropBox3").value = "整体" Then
ElseIf Forms("窗口1").Controls("DropBox3").value = "门店" Then
b2.HGroups.AddDef("mall_name","门店名称") \'添加客户列用于水平分组
End If
If Forms("窗口1").Controls("DropBox4").value = "年报" Then
b2.hGroups.AddDef("image_time",DateGroupEnum.Year,"年度") \'添加日期列用于垂直分组,按年分组
End If
b2.VGroups.Addexp("is_turnover", "case when is_turnover = 1 then \'成交数\' end ") \'
If Forms("窗口1").Controls("CheckBox1").Checked = True Then
b2.VGroups.Addexp("sex","case when sex = \'男\' then \'男\' when sex = \'女\' then \'女\' end ") \'添加产品列用于垂直分组,并设置了Pattern参数
End If
If Forms("窗口1").Controls("CheckBox2").Checked = True Then
b2.VGroups.AddExp("age"," Case when age <= 25 Then \'25岁以下\' when age >= 26 and age <= 30 then \'26-30岁\' when age >= 31 and age <= 35 then \'31-35岁\' when age >= 36 and age <= 45 then \'36-45岁\' else \'46岁以上\' end ")
End If
b2.Totals.AddDef("imgage_id",AggregateEnum.count,"人数") \'添加数量列用于统计
\'b2.VerticalTotal = True
b2.Filter = filter & " and is_turnover = 1"
b2.Subtotal = True \'生成汇总模式
b2.SubtotalLevel = 0 \'只生成总计行
dt2 = b2.BuildDataSource()
Dim nms() As String
s003 = s001 & "," & s002
output.show(s003)
nms = s003.split(",")
dt1.Combine(nms,dt2,nms) \'将销售统计数据组合到进货统计数据
Forms("窗口1").Controls("table1").Table.DataSource = dt1
-- 发布时间：2018/4/11 17:34:00
--
得到组合表格以后，再设置一次汇总模式
http://www.foxtable.com/webhelp/scr/1372.htm