[求助]篡改汇总模式的结果 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

Dim g As Subtotalgroup

Dim cs As String = ""

For Each c As Col In Tables("统计分析_Table1").cols

If c.IsNumeric AndAlso c.Name <> "年份" AndAlso c.Name <> "月份" Then

cs &= c.name & ","

End If

t.SubtotalGroups.Clear()

g = New Subtotalgroup

g.Aggregate = AggregateEnum.Sum

g.GroupOn = "款式季度"

g.TotalOn = cs.trim(",")

g.Caption = "{0} 小计"

t.SubtotalGroups.Add(g)

g = New Subtotalgroup

g.Aggregate = AggregateEnum.Sum

g.GroupOn = "工厂"

g.TotalOn = cs.trim(",")

g.Caption = "{0} 合计"

t.SubtotalGroups.Add(g)

g = New Subtotalgroup

g.Aggregate = AggregateEnum.sum

g.GroupOn = "*"

g.TotalOn = cs.trim(",")

g.Caption = "总计"

t.SubtotalGroups.Add(g)

Dim groups As List(of String) = DataTables("统计分析_Table1").GetValues("款式季度")

For Each grp As String In groups

g = New Subtotalgroup

g.Aggregate = AggregateEnum.Sum

g.GroupOn = "*"

g.Caption = "总计"

t.SubtotalGroups.Add(g)

t.Subtotal()

Dim cnt As Integer = Tables("统计分析_Table1").Rows.Count(True) - groups.count

For Each group As String In groups

Tables("统计分析_Table1").Rows(cnt,True)("款式季度") = group

Tables("统计分析_Table1").Rows(cnt,True)(dic("" & nf1 & "_数量")) = Tables("统计分析_Table1").Compute("sum(dic(" & nf1 & "_数量))","款式季度 = '" &group & "'")

cnt = cnt +1

发现取到的是空值，会是什么原因

此主题相关图片如下：qq图片20200501091912.png

dim sum as double=Tables("统计分析_Table1").Compute("sum(dic(" & nf1 & "_数量))","款式季度 = '" &group & "'")

msgbox(sum)为0

Dim groups As List(of String) = DataTables("统计分析_Table1").GetValues("款式季度")

For Each grp As String In groups

g = New Subtotalgroup

g.Aggregate = AggregateEnum.Sum

g.GroupOn = "*"

g.Caption = "总计"

t.SubtotalGroups.Add(g)

你这里指定了分组列(GroupOn)，没有指定统计列(TotalOn)

发现是”" & nf1 & "_数量“这个列名的关系，如果用实际列名”A_1“这样是可以的，但这个列名在下面的统计中都有效，为什么到了这里就无效了呢

Dim dic As new Dictionary(of String, String)

For Each c As Col In Tables("统计分析_Table1").cols

dic.add(c.Caption, c.name)

If dic.ContainsKey("" & nf1 & "_数量") = False Then

DataTables("统计分析_Table1").dataCols.add("" & nf1 & "_数量", Gettype(Double))

dic.add("" & nf1 & "_数量", "" & nf1 & "_数量")

End If

Dim t As Table = Tables("统计分析_Table1")

For Each r1 As Row In t.Rows

If r1.IsNull(dic("" & nf1 & "_数量")) = False And r1(dic("" & nf1 & "_数量")) <> 0 Then

r1("" & nf1 & "_单位价格") = r1(dic("" & nf1 & "_金额"))/r1(dic("" & nf1 & "_数量"))

end if

一楼贴的代码不完整，也没有实际的数据测试，你当想帮你的人都是神仙了。

自己有数据，自己知道设计目的，自己多调试

Dim cnt As Integer = Tables("统计分析_Table1").Rows.Count(True) - groups.count

For Each group As String In groups

Messagebox.show(Group)

Messagebox.Show(Tables("统计分析_Table1").Compute("sum(dic(" & nf1 & "_数量))","款式季度 = '" &group & "'"))

MessageBos.Show(cnt)

Tables("统计分析_Table1").Rows(cnt,True)("款式季度") = group

Tables("统计分析_Table1").Rows(cnt,True)(dic("" & nf1 & "_数量")) = Tables("统计分析_Table1").Compute("sum(dic(" & nf1 & "_数量))","款式季度 = '" &group & "'")

cnt = cnt +1

[此贴子已经被作者于2020/5/2 8:24:59编辑过]

Tables("统计分析_Table1").Rows(cnt,True)(dic("" & nf1 & "_数量")) = Tables("统计分析_Table1").Compute("sum(dic(" & nf1 & "_数量))","款式季度 = '" &group & "'")

此主题相关图片如下：33333333.png

经过测试，是上面代码中标蓝色处的代码无效，前面黄色代码是有效的，sum(" & nf1 & "_数量)也不行，如果把蓝色代码处改成实际的列名“实际采购数量_1”是可以取到数的。

msgbox(dic("" & nf1 & "_数量"))显示的是列名“实际采购数量_1"，msgbox("" & nf1 & "_数量")显示的是标题名”2020_数量“

sum(dic(" & nf1 & "_数量))这里的列名要如何表示？

[此贴子已经被作者于2020/5/3 10:17:50编辑过]

"sum(dic(" & nf1 & "_数量))"

改为：
"sum(" & dic(nf1) & "_数量)"

可以自己Message.show("sum(" & dic(nf1) & "_数量)")

看看合成的是否是预期的。

实际上稍微复杂点的表达式，都很难一次合成，都需要用MessageBox.show显示一下合成的结果，看看是否正确。

主题：[求助]篡改汇总模式的结果