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
加好友 
发短信
Post By：2018/4/11 17:26:00 [只看该作者]
此主题相关图片如下：微信截图_20180411171903.png