- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 关于自定义汇总 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=78522)
-- 发布时间：2015/12/10 17:00:00
-- 关于自定义汇总
如图，我要在筛选过程中进行自定义汇总，如何使得“年份”不用汇总，同时只有选择科目的时候才进行总计，科目不选就没有总计
此主题相关图片如下：3333.jpg
Dim Filter As String \'设置筛选条件
With e.Form.Controls("DW")
If .Value IsNot Nothing Then
Filter = "所属单位 = \'" & .Value & "\'"
End If
End With
With e.Form.Controls("NF")
If .Value IsNot Nothing Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & "年 = \'" & .Value & "\'"
End If
End With
With e.Form.Controls("KM")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "一级科目 = \'" & .Value & "\'"
End If
End With
If Filter > "" Then
Tables("损益进度(总体)").Filter = Filter
End If
Dim t As Table = Tables("损益进度(总体)")
Dim g As Subtotalgroup
Dim cs As String = ""
For Each c As Col In Tables("损益进度(总体)").cols
If c.Visible AndAlso c.IsNumeric Then
cs &= c.name & ","
End If
Next
t.SubtotalGroups.Clear()
t.GroupAboveData = False
t.TreeVisible = False
t.SpillNode = False
g = New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "*"
g.TotalOn = cs.trim(",")
g.Caption = "总计"
t.SubtotalGroups.Add(g)
t.Subtotal()
-- 发布时间：2015/12/10 17:02:00
--
For Each c As Col In Tables("损益进度(总体)").cols
If c.Visible AndAlso c.IsNumeric AndAlso c.Name <> "年份" Then
cs &= c.name & ","
End If
Next
-- 发布时间：2015/12/10 17:03:00
--
If e.Form.Controls("KM").Text > "" Then
\'汇总代码
End If
-- 发布时间：2015/12/10 17:06:00
--
这两个看明白了，谢谢
-- 发布时间：2015/12/11 11:42:00
--
在自定义汇总中，计算汇总行的比率时，有这个错误提示
Dim r As Row
For i As Integer = 0 To t.Rows.Count(True) - 1
r = t.Rows(i,True)
If r.IsGroup And r(dic("主营业务收入_本月")) = 0 Then
r("毛利率_本月") = 0
Else
r("毛利率_本月") = r("利润_本月")/r(dic("主营业务收入_本月"))
End If
Next
此主题相关图片如下：1122.jpg
此主题相关图片如下：1122.jpg-- 发布时间：2015/12/11 11:52:00
--
你的 【毛利率_本月】 是表达式列，不要创建表达式列
-- 发布时间：2015/12/11 12:00:00
--
哦，明白了
-- 发布时间：2015/12/11 20:31:00
--
With Tables("损益总体比率") \'隐藏部分列
.Cols(dic("主营业务收入_本月")).Visible = False
.Cols(dic("主营业务收入_累计")).Visible = False
.Cols(dic("主营业务成本_本月")).Visible = False
.Cols(dic("主营业务成本_累计")).Visible = False
.Cols("营业费用_本月").Visible = False
.Cols("营业费用_累计").Visible = False
.Cols(dic("资产减值损失_本月")).Visible = False
.Cols(dic("资产减值损失_累计")).Visible = False
.Cols("其他支出_本月").Visible = False
.Cols("其他支出_累计").Visible = False
.Cols("利润_本月").Visible = False
.Cols("利润_累计").Visible = False
End With
Dim t As Table = Tables("损益总体比率")
Dim g As Subtotalgroup \'以下为设置自定义汇总模式,b.Subtotal = True 不适用通过表达式生成的列
Dim cs As String = ""
For Each c As Col In Tables("损益总体比率").cols
If c.Visible AndAlso c.IsNumeric Then
cs &= c.name & ","
End If
Next
t.SubtotalGroups.Clear()
t.GroupAboveData = False
t.TreeVisible = False
t.SpillNode = False
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)
t.Subtotal()
Dim r As Row
For i As Integer = 0 To t.Rows.Count(True) - 1
r = t.Rows(i,True)
If r.IsGroup And (r(dic("主营业务收入_本月")) = 0 Or r(dic("主营业务收入_本月")) Is Nothing) Then
r("利润率_本月") = 0
r("利润率_累计") = 0
r("毛利率_本月") = 0
r("毛利率_累计") = 0
r("费用率_本月") = 0
r("费用率_累计") = 0
r("打折率_本月") = 0
r("打折率_累计") = 0
Else
r("利润率_本月") = r("利润_本月")/r(dic("主营业务收入_本月"))
r("利润率_累计") = r("利润_累计")/r(dic("主营业务收入_累计"))
r("毛利率_本月") = (r(dic("主营业务收入_本月"))-r(dic("主营业务成本_本月")))/r(dic("主营业务收入_本月"))
r("毛利率_累计") = (r(dic("主营业务收入_累计"))-r(dic("主营业务成本_累计")))/r(dic("主营业务收入_累计"))
r("费用率_本月") = r("营业费用_本月")/r(dic("主营业务收入_本月"))
r("费用率_累计") = r("营业费用_累计")/r(dic("主营业务收入_累计"))
r("打折率_本月") = r(dic("资产减值损失_本月"))/r(dic("主营业务收入_本月"))
r("打折率_累计") = r(dic("资产减值损失_累计"))/r(dic("主营业务收入_累计"))
End If
Next
上面代码有什么问题吗，我隐藏了各相关比率计算公式里的相关列后，汇总里的小计和总计都变成0了，如图
此主题相关图片如下：123.jpg
此主题相关图片如下：123.jpg-- 发布时间：2015/12/11 20:49:00
--
代码没问题，上传例子测试。
-- 发布时间：2015/12/11 21:20:00
--
好的
[此贴子已经被作者于2015/12/12 14:15:17编辑过]