主题：自动汇总

查询表代码

Dim jb As new SQLJoinTableBuilder("查询表1","表a")
jb.AddCols("作业日期", "客户","fa piao号码")
jb.AddExp("应收款","iif(代付费用_打单费 Is null,0,代付费用_打单费) + iif(代付费用_理货费 Is null,0,代付费用_理货费))
jb.Filter = "状态 <> '已付费'"

Dim cb As new CrossTableBuilder("客户欠款",jb.BuildSQL)
cb.HGroups.AddDef("客户")
cb.HGroups.AddDef("fa piao号码")
cb.VGroups.AddDef("作业日期",DateGroupEnum.month,"{0}月")
cb.Totals.AddDef("应收款")
cb.HorizontalTotal = True
cb.Build()
DataTables("客户欠款").SysStyles("EmptyArea").BackColor = Color.DarkTurquoise
MainTable = Tables("客户欠款")

 

汇总模式代码

im t As Table = Tables("客户欠款")
Dim g As SubtotalGroup
t.SubtotalGroups.Clear()
t.GroupAboveData = False
t.TreeVisible = False
t.SpillNode = True

g = New SubtotalGroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "客户"
g.TotalOn = "应收款_1,应收款_2,应收款_3,应收款_4,应收款_5,合计"
g.Caption = "{0} 小计"
t.SubtotalGroups.Add(g)

g = New SubtotalGroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "*"
g.TotalOn = "应收款_1,应收款_2,应收款_3,应收款_4,应收款_5,合计"
g.Caption = "总计"
t.SubtotalGroups.Add(g)

这个怎么才自动汇总。

此主题相关图片如下：截图50.jpg

用代码生成的查询表需要自动汇总

查询表代码见一楼

此主题相关图片如下：截图54.jpg

希望打开为汇总模式

此主题相关图片如下：截图55.jpg

Dim jb As new SQLJoinTableBuilder("查询表1","表a")
jb.AddCols("作业日期", "客户","fa piao号码")
jb.AddExp("应收款","iif(代付费用_打单费 Is null,0,代付费用_打单费) + iif(代付费用_理货费 Is null,0,代付费用_理货费))
jb.Filter = "状态 <> '已付费'"
Dim cb As new CrossTableBuilder("客户欠款",jb.BuildSQL)
cb.HGroups.AddDef("客户")
cb.HGroups.AddDef("fa piao号码")
cb.VGroups.AddDef("作业日期",DateGroupEnum.month,"{0}月")
cb.Totals.AddDef("应收款")
cb.HorizontalTotal = True
cb.Build()
DataTables("客户欠款").SysStyles("EmptyArea").BackColor = Color.DarkTurquoise
m t As Table = Tables("客户欠款")
Dim g As SubtotalGroup
t.SubtotalGroups.Clear()
t.GroupAboveData = False
t.TreeVisible = False
t.SpillNode = True
g = New SubtotalGroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "客户"
g.TotalOn = "应收款_1,应收款_2,应收款_3,应收款_4,应收款_5,合计"
g.Caption = "{0} 小计"
t.SubtotalGroups.Add(g)
g = New SubtotalGroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "*"
g.TotalOn = "应收款_1,应收款_2,应收款_3,应收款_4,应收款_5,合计"
g.Caption = "总计"
t.SubtotalGroups.Add(g)
t.Subtotal
MainTable = t

狐狸爸爸关键在于”作业日期“产生的”月份“不确定。可能是1个月。也可能是12个月。

Dim t As Table = Tables("客户欠款")
Dim g As Subtotalgroup
Dim fds As String
For Each c As Col In t.Cols
      If c.Name.EndsWith("月") Then '如果是月结尾的列
          fds = fds & "," & c.name
      End If
Next

t.SubtotalGroups.Clear()
t.GroupAboveData = False
t.TreeVisible = False
t.SpillNode = True

g = New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "客户"
g.TotalOn = "fds,合计"
g.Caption = "{0} 小计"
t.SubtotalGroups.Add(g)

g = New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "*"
g.TotalOn = "fds,合计"
g.Caption = "总计"
t.SubtotalGroups.Add(g)

t.Subtotal()

 

狐狸爸爸这个实例太难做了。帮忙看看吧

太麻烦你了。感谢。

还有一个问题。增加黄颜色的代码后。红颜色部分代码”自动横向汇总“产生的例。

Dim jb As new SQLJoinTableBuilder("查询表1","业务大表")
jb.AddCols("作业日期", "客户","fa piao号码")
jb.AddExp("应收款","iif(代付费用_打单费 Is null,0,代付费用_打单费) + iif(代付费用_理货费 Is null,0,代付费用_理货费)"
jb.Filter = "状态 <> '已付费'"

Dim cb As new CrossTableBuilder("客户欠款",jb.BuildSQL)
cb.HGroups.AddDef("客户")
cb.HGroups.AddDef("fa piao号码")
cb.VGroups.AddDef("作业日期",DateGroupEnum.month,"{0}月")
cb.Totals.AddDef("应收款")
cb.Subtotal = True
cb.HorizontalTotal = True
cb.Build

结果是

此主题相关图片如下：截图56.jpg

希望可以这样

此主题相关图片如下：截图57.jpg

自己顶一下。

期待