- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 统计代码扩展 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=42085)
-- 发布时间：2013/11/4 7:54:00
-- 统计代码扩展
请看例子窗口的要求:
-- 发布时间：2013/11/4 8:47:00
--
你这个我还真没看懂? 单价又从哪里来?
-- 发布时间：2013/11/4 9:12:00
--
统计表4是统计来源表. 统计后的格式同主表的格式.
[此贴子已经被作者于2013-11-4 9:13:40编辑过]
-- 发布时间：2013/11/4 9:25:00
--
这个统计来源表的结构是不能变的.所以不要说设计的不合理,因为不合理也只有这样的.
-- 发布时间：2013/11/4 9:26:00
--
我们的交叉统计只有行变列的方法 如果还有列变行的方法,就处理的简单的多了.
[此贴子已经被作者于2013-11-4 9:27:19编辑过]
-- 发布时间：2013/11/4 10:34:00
--
这样写 是不是太傻了?
Dim g As New CrossTableBuilder("统计表1", DataTables("统计表4"))
g.HGroups.AddDef("统计部门")
g.HGroups.AddDef("项目")
g.HGroups.AddDef("报表名称")
g.HGroups.AddDef("统计类型")
g.HGroups.AddDef("日期", DateGroupEnum.None)
g.VGroups.AddDef("数据统计项目")
g.Totals.AddDef("数据", "数据")
g.HorizontalTotal = True
g.Build()
MainTable = Tables("统计表1")
Dim str As String \'产值列
Dim rs As String \'人数列
Dim dj As String
For Each cl As Col In Tables("统计表1").Cols
Select Case cl.Caption
Case "总产值"
str = cl.Name
Case "人数"
rs = cl.Name
Case "单价"
dj = cl.Name
End Select
Next
DataTables("统计表1").DataCols(str).Expression=dj & "*" & rs
Dim Fliter As String
Dim Arys As List(Of String())
Arys = DataTables("统计表1").GetValues("日期|统计部门|项目|报表名称|统计类型")
For Each Ary As String() In Arys
For Each cl As Col In Tables("统计表1").Cols
Select Case cl.Caption
Case "总产值"
Fliter = "日期=\'" & Ary(0) & "\'And 统计部门=\'" & Ary(1) & "\'And 项目=\'" & Ary(2) & "\'And 报表名称=\'" & Ary(3) & "\' And 统计类型 =\'" & Ary(4) & "\' And 数据统计项目=\'" & cl.Caption & "\'"
For Each dr As DataRow In DataTables("统计表4").Select(Fliter)
If dr IsNot Nothing Then
dr("数据") = DataTables("统计表1").Compute("Sum(" & cl.Name & ")","日期=\'" & Ary(0) & "\'And 统计部门=\'" & Ary(1) & "\'And 项目=\'" & Ary(2) & "\'And 报表名称=\'" & Ary(3) & "\' And 统计类型 =\'" & Ary(4) & "\'")
End If
Next
End Select
Next
Next
Dim g1 As New CrossTableBuilder("统计表11", DataTables("统计表4"))
g1.HGroups.AddDef("统计部门")
g1.HGroups.AddDef("项目")
g1.HGroups.AddDef("报表名称")
g1.HGroups.AddDef("统计类型")
g1.HGroups.AddDef("数据统计项目")
g1.VGroups.AddDef("日期", "{0}月")
g1.Totals.AddDef("数据", "数据")
g1.HorizontalTotal = True
g1.Build
Tables("统计表11").StopRedraw
Dim ps As Integer = 5
For i As Integer = 1 To 12
Dim nm As String = ""
For Each dc As DataCol In DataTables("统计表11").DataCols
If dc.Caption = i & "月" Then
nm = dc.name
End If
Next
If nm = "" Then
nm = i & "月"
DataTables("统计表11").DataCols.Add(nm,Gettype(Double))
End If
Tables("统计表11").Cols(nm).Move(ps)
ps = ps + 1
Next
Tables("统计表11").ResumeRedraw