查询表代码

Dim jb As new SQLJoinTableBuilder("查询表1","表a")
jb.AddCols("作业日期", "客户","发票号码")
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("发票号码")
cb.VGroups.AddDef("作业日期",DateGroupEnum.month,"{0}月")
cb.Totals.AddDef("应收款")
cb.HorizontalTotal = True
cb.Build()
DataTables("客户欠款").SysStyles("EmptyArea").BackColor = Color.DarkTurquoise
MainTable = Tables("客户欠款")

汇总模式代码

问题似乎没有问到点子上，不明白你的意思。

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

查询表代码见一楼

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

希望打开为汇总模式

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

Dim jb As new SQLJoinTableBuilder("查询表1","表a")
jb.AddCols("作业日期", "客户","发票号码")
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("发票号码")
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

Dim jb As new SQLJoinTableBuilder("查询表1","表a")
jb.AddCols("作业日期", "客户","发票号码")
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("发票号码")
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个月。

问题让人费解，你的代码和图片不一致。

大概： 动态合成TotalOn：

....

Dim fds as string

For Each c as Col in t.Cols

      if c.Name.EndsWith("月") Then \'如果是月结尾的列

          fds = fds & "," & c.name

      enif

Next

fds = fds.Trim(",")

.....

g.TotalOn = fds

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()

千言万语，不如一个简单实例。