Dim nf,s,sql As String
nf = e.Form.Controls("ComboBox2").Value
If nf Is Nothing Then
MessageBox.Show("请在右侧选择生成报表的年份","提示")
Return
End If
'根据年份生成临时表
sql = "Select 报表年月,日期,单位,Sum(加工费) As 加工费,Sum(投工) As 投工,Null as 人均加工费,Null As 环比 FROM {日报源表} WHERE Year(日期) = " & nf & " Group By 报表年月,日期,单位"
'根据临时表生成年度汇总表
Dim b As New CrossTableBuilder("年度汇总",sql)
b.HGroups.AddDef("单位")
b.VGroups.AddDef("报表年月")
b.Totals.AddDef("加工费")
b.Totals.AddDef("投工",AggregateEnum.Average,"月均人数")
b.Totals.AddDef("人均加工费",AggregateEnum.Average)
b.Totals.AddDef("环比",AggregateEnum.Average)
b.HorizontalTotal = True
b.VerticalTotal = True
b.Build()
Dim i,n As Integer
Dim t As Table = Tables("年度汇总")
i =(Tables("年度汇总").Cols.Count-5)/4
t.StopRedraw '停止绘制表格
With t.DataTable.DataCols
.Add("合计_年均人数", Gettype(Double),"[合计_投工]/" & i)
.Add("年人均加工费", Gettype(Double),"iif(合计_年均人数 =0,Null,合计_加工费/合计_年均人数)")
.Add("名次", Gettype(Integer))
End With
t.Cols.Remove("环比_1","合计_投工","合计_人均加工费","合计_环比")
For Each r As Row In T.Rows
For n = 1 To i
If r("投工_" & n) <> 0 Then
r("人均加工费_" & n) = r("加工费_" & n)/r("投工_" & n)
End If
If n> 1 AndAlso r("人均加工费_" & n-1) <> 0 Then
r("环比_" & n)=r("人均加工费_" & n)/r("人均加工费_" & n-1)-1
End If
Next
Next
Dim drs As List(Of DataRow) = t.DataTable.Select("[单位] <> '合计'","年人均加工费 DESC")
For n = 0 To drs.Count - 1 '遍历所有行
If n > 0 AndAlso drs(n)("年人均加工费") = drs(n-1)("年人均加工费") Then '如果年人均加工费和上一行相同
drs(n)("名次") = drs(n-1)("名次") '则排名等于上一行
Else
drs(n)("名次") = n + 1 '设置名次
End If
Next
For Each dc As DataCol In t.DataTable.DataCols
If dc.name <> "单位" AndAlso dc.name <> "名次" Then
s="0.00" & iif(dc.name.Indexof("环比")>-1,"%","")
t.DataTable.DataCols(dc.Name).SetFormat(s)
End If
Next
t.Cols("单位").TextAlign = TextAlignEnum.Center
t.Cols("名次").TextAlign = TextAlignEnum.Center
t.AutoSizeCols()
t.ResumeRedraw '恢复绘制表格
MainTable =t
Tables("年度汇总").Cols.Frozen = 1 '冻结一列
[此贴子已经被作者于2011-7-21 17:43:47编辑过]
加好友 
发短信
Post By：2011/7/21 17:16:00 [只看该作者]
