以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  跨表统计问题  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=142966)

--  作者:mojo
--  发布时间:2019/11/10 17:09:00
--  跨表统计问题

帮忙修改一下代码:

当跨表合计数 Sum()等于空值或0时计算就会出现转换类型错误。

 

 

If e.DataCol.Name  = "所属部门" OrElse e.DataCol.Name = "所属编制"  Then
    Dim Filter As String = "[所属部门] = \'" & e.DataRow("所属部门") & "\' and [所属编制] = \'" & e.DataRow("所属编制") & "\'"
    Dim Filter2 As String = "[所属编制] = \'" & e.DataRow("所属编制") & "\'"
    Dim Filter3 As String = "[体制] = \'" & e.DataRow("体制") & "\'"
    e.DataRow("人数") = DataTables("在职人员信息").Compute("Count(姓名)", Filter)
    e.DataRow("基本基数") = DataTables("在职人员信息").Compute("Sum(基本基数)", Filter)
    e.DataRow("补充基数") = DataTables("在职人员信息").Compute("Sum(补充基数)", Filter)
    e.DataRow("基养个人") = DataTables("在职人员信息").Compute("Sum(个人_养老保险)", Filter)
    e.DataRow("基医个人") = DataTables("在职人员信息").Compute("Sum(个人_医疗保险)", Filter)
    e.DataRow("失业个人") = DataTables("在职人员信息").Compute("Sum(个人_失业保险)", Filter)
    e.DataRow("年金个人") = DataTables("在职人员信息").Compute("Sum(个人_年金)", Filter)
    e.DataRow("年金企业") = DataTables("在职人员信息").Compute("Sum(企业_年金)", Filter)
    e.DataRow("基本公积金") = DataTables("在职人员信息").Compute("Sum(基本公积金企业)", Filter) + DataTables("在职人员信息").Compute("Sum(基本公积金个人)", Filter)
    e.DataRow("补充公积金") = DataTables("在职人员信息").Compute("Sum(补充公积金企业)", Filter) + DataTables("在职人员信息").Compute("Sum(补充公积金个人)", Filter)
    e.DataRow("统筹内") = format(DataTables("在职人员信息").Compute("Sum(基本基数)", Filter) / DataTables("在职人员信息").Compute("Sum(基本基数)", Filter2) * DataTables("社保下达").Compute("Sum(其中养统内)", Filter2 ) +0.001,"#")
    e.DataRow("统筹外") = format(DataTables("在职人员信息").Compute("Sum(基本基数)", Filter) / DataTables("在职人员信息").Compute("Sum(基本基数)", Filter2) * DataTables("社保下达").Compute("Sum(统筹外)", Filter2 ) +0.001,"#")
    e.DataRow("基养企业") = format(DataTables("在职人员信息").Compute("Sum(基本基数)", Filter) / DataTables("在职人员信息").Compute("Sum(基本基数)", Filter3) * DataTables("社保下达").Compute("Sum(基养企业)", Filter3 ) , "0.00")
    e.DataRow("基医企业") = format(DataTables("在职人员信息").Compute("Sum(基本基数)", Filter) / DataTables("在职人员信息").Compute("Sum(基本基数)", Filter3) * DataTables("社保下达").Compute("Sum(基医企业)", Filter3 ) , "0.00")
    e.DataRow("失保企业") = format(DataTables("在职人员信息").Compute("Sum(基本基数)", Filter) / DataTables("在职人员信息").Compute("Sum(基本基数)", Filter3) * DataTables("社保下达").Compute("Sum(失业企业)", Filter3 ) , "0.00")
    e.DataRow("工伤企业") = format(DataTables("在职人员信息").Compute("Sum(基本基数)", Filter) / DataTables("在职人员信息").Compute("Sum(基本基数)", Filter3) * DataTables("社保下达").Compute("Sum(工伤保险)", Filter3 ) , "0.00")
    e.DataRow("生育保险") = format(DataTables("在职人员信息").Compute("Sum(基本基数)", Filter) / DataTables("在职人员信息").Compute("Sum(基本基数)", Filter3) * DataTables("社保下达").Compute("Sum(生育保险)", Filter3 ) , "0.00")
    e.DataRow("补充医疗") = format(DataTables("在职人员信息").Compute("Sum(基本基数)", Filter) / DataTables("在职人员信息").Compute("Sum(基本基数)", Filter3) * DataTables("社保下达").Compute("Sum(补医合计)", Filter3 ) , "0.00")
End If

 

 


--  作者:程兴刚
--  发布时间:2019/11/10 20:02:00
--  
提示:0不能做除数,判断/后面的值为0时,结果为0,否则,源代码除数计算式不变!
--  作者:有点蓝
--  发布时间:2019/11/10 21:17:00
--  
比如

e.DataRow("统筹内") = format(DataTables("在职人员信息").Compute("Sum(基本基数)", Filter) / DataTables("在职人员信息").Compute("Sum(基本基数)", Filter2) * DataTables("社保下达").Compute("Sum(其中养统内)", Filter2 ) +0.001,"#")

改为

dim i1 as double = DataTables("在职人员信息").Compute("Sum(基本基数)", Filter2)
if i1 = 0 then 
  e.DataRow("统筹内") = 0
else
  e.DataRow("统筹内") = format(DataTables("在职人员信息").Compute("Sum(基本基数)", Filter) / i1 * DataTables("社保下达").Compute("Sum(其中养统内)", Filter2 ) +0.001,"#")
end if
[此贴子已经被作者于2019/11/10 21:18:40编辑过]