| 以文本方式查看主题 - Foxtable(狐表) (http://foxtable.com/bbs/index.asp) -- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2) ---- [求助]交叉统计不显示数据(手工编写,如果某条件列有空值则统计数据也为空) (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=154611) |
| -- 作者：绿火柴 -- 发布时间：2020/9/20 12:54:00 -- [求助]交叉统计不显示数据(手工编写,如果某条件列有空值则统计数据也为空) 如题: 交叉统计不显示数据(手工编写,如果某条件列有空值,则对应的行统计数据也显示为空) 比如,用气明细 表中,类别列里,个别行字段为空值,则在 气量统计 表中,统计各日期下的数据都为空.....同样方式在EXCEL透视表中,是可以显示数据的. 代码如下: Dim dtb As New DataTableBuilder("气量统计") dtb.AddDef("排序", Gettype(String), 50) dtb.AddDef("位置", Gettype(String), 50) dtb.AddDef("回路名称", Gettype(String), 50) dtb.AddDef("类别", Gettype(String), 50) \'dtb.AddDef("合并字符",Gettype(String),100) Dim prds As List(of String) = DataTables("用气明细").GetValues("日期") For Each prd As Date In prds dtb.AddDef(prd, Gettype(Double)) Next dtb.Build() For Each cus() As String In dt.GetValues("排序|位置|回路名称|类别") Dim dtbr As DataRow = DataTables("气量统计").AddNew() dtbr("排序") = cus(0) dtbr("位置") = cus(1) dtbr("回路名称") = cus(2) dtbr("类别") = cus(3) \'dtbr("合并字符") = cus(0) & cus(1) & cus(2) & cus(3) For Each prd As Date In prds dtbr(prd) = dt.Compute("Sum(实际用气量)","排序 = \'" & cus(0) & "\'and 位置 = \'" & cus(1) & "\' and 回路名称 = \'" & cus(2) & "\' and 类别 =\'" & cus(3) & "\' And [日期] = \'" & prd.Date & "\'") Next Next MainTable = Tables("气量统计") |
| -- 作者：有点蓝 -- 发布时间：2020/9/20 21:03:00 -- dtb.Build() dim filter as string For Each cus() As String In dt.GetValues("排序|位置|回路名称|类别") Dim dtbr As DataRow = DataTables("气量统计").AddNew() dtbr("排序") = cus(0) dtbr("位置") = cus(1) dtbr("回路名称") = cus(2) dtbr("类别") = cus(3) \'dtbr("合并字符") = cus(0) & cus(1) & cus(2) & cus(3) filter = "排序 = \'" & cus(0) & "\'and 位置 = \'" & cus(1) & "\' and 回路名称 = \'" & cus(2) & "\' and 类别 " & IIF(cus(3) > ""," = \'" & cus(3) & ""\'," is null") For Each prd As Date In prds filter = filter & " and [日期] = \'" & prd.Date & "\'" dtbr(prd) = dt.Compute("Sum(实际用气量)",filter ) Next Next MainTable = Tables("气量统计") |
| -- 作者：客人 -- 发布时间：2020/9/21 8:40:00 -- 多谢蓝版本,,,已经解决.思路挺好的. 有两处小错误,自己调整好了.
|