相关的代码：
数据载入_AfterLoad
'报表年月
If _UserGroup = "开发" Then
Dim dmd As New SQLCommand
Dim dt As DataTable
Dim dmb As WinForm.ComboBox
dmd.C '外部数据源
dmd.CommandText = "SELECT * FROM (SELECT DISTINCT 报表年月 From {日报源表} ORDER BY 报表年月 DESC)"
dt = dmd.ExecuteReader()
dmb = e.Form.Controls("ComboBox1")
dmb.ComboList= dt.GetComboListString("报表年月","","报表年月 Desc")
Else
Dim dmd As New SQLCommand
Dim dt As DataTable
Dim dmb As WinForm.ComboBox
dmd.C '外部数据源
dmd.CommandText = "SELECT * FROM (SELECT DISTINCT 报表年月 From {日报源表} ORDER BY 报表年月 DESC)"
'dmd.CommandText = "SELECT TOP 2 * FROM (SELECT DISTINCT 报表年月 From {日报源表} ORDER BY 报表年月 DESC)"
dt = dmd.ExecuteReader()
dmb = e.Form.Controls("ComboBox1")
dmb.ComboList= dt.GetComboListString("报表年月","","报表年月 Desc")
End If'生成年报
Dim cmd As New SQLCommand
Dim ct As DataTable
Dim cmb As WinForm.ComboBox
cmd.C '外部数据源
cmd.CommandText = "SELECT DISTINCT Left(报表年月,4) As 报表年月 From {日报源表}"
ct = cmd.ExecuteReader()
cmb = e.Form.Controls("ComboBox2")
cmb.ComboList= ct.GetComboListString("报表年月","","报表年月 Desc")
'cmb.Value = cmb.ComboList.split("|")(0) '在组合框内显示
Dim d As Date = Date.Today
Dim xq As String
Select Case d.DayOfWeek
Case 1
xq = "一"
Case 2
xq = "二"
Case 3
xq = "三"
Case 4
xq = "四"
Case 5
xq = "五"
Case 6
xq = "六"
Case 0
xq = "天"
Case Else
xq = "无效值"
End Select
Dim lbl As WinForm.Label = e.Form.Controls("Label3")
lbl.Text = "今天是 " & Date.Today() & " 星期" & xq
数据载入_ComboBox1_ValueChanged
'载入当月数据
If e.Form.Controls("ComboBox1").Value Is Nothing Then
MessageBox.Show("请在右侧选择报表年月","提示")
Return
End If
Dim ym As String = e.Form.Controls("ComboBox1").Value
Dim Filter As String = "报表年月 = '" & ym & "'"
If _userGroup = "填报" Then
filter = filter & " And 单位 = '" & _username & "'"
End If
DataTables("日报源表").LoadFilter = filter
DataTables("日报源表").Load
'打开表返回最末行
With Tables("日报源表")
.Position = .Rows.Count - 1
End With
'排序
Tables("日报源表").Sort = "单位,日期,报表年月"
Tables("日报源表").AutoSizeCols()
数据载入_ComboBox2_ValueChanged
'读取ComboBox2中的年份
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 投工,1 as 人均加工费,1 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 t As Table = Tables("年度汇总")
Dim i As Integer =(Tables("年度汇总").Cols.Count-5)/4
t.DataTable.DataCols.Add("合计_年均人数", Gettype(Double),"[合计_投工]/" & i)
t.DataTable.DataCols.Add("年人均加工费", Gettype(Double),"合计_加工费/合计_年均人数")
t.DataTable.DataCols.Add("名次", Gettype(Integer))
t.Cols.Remove("环比_1","合计_投工","合计_人均加工费","合计_环比")
For Each r As Row In T.Rows
For Each dc As DataCol In t.DataTable.DataCols
If dc.name.IndexOf("人均加工费_")> -1 Then
r(dc.name) =r(dc.name.Replace("人均",""))/r(dc.name.Replace("人均加工费","投工"))
If dc.name <> "人均加工费_1" Then
Dim n1 As Integer = Val(dc.name.split("_")(1))
r("环比_" & n1)=(r(dc.name)-r("人均加工费_" & n1-1))/r("人均加工费_" & n1-1)
End If
End If
r("年人均加工费") = r("合计_加工费") /r("合计_年均人数")
Next
Next
Dim drs As List(Of DataRow) = t.DataTable.Select("[单位] <> '合计'","年人均加工费 DESC")
For n As Integer = 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()
MainTable =t
Case "按月分析"
Dim sql As String
If _UserGroup = "填报" Then '定义用户组
sql = "Select 报表年月,日期,单位,Sum(加工费) As 加工费,Sum(投工) As 投工 FROM {日报源表} WHERE 单位 = '" & _UserName & "' Group By 报表年月,日期,单位 ORDER BY 单位,日期,报表年月"
Else
sql = "Select 报表年月,日期,单位,Sum(加工费) As 加工费,Sum(投工) As 投工 FROM {日报源表} Group By 报表年月,日期,单位 ORDER BY 单位,日期,报表年月"
End If
Dim b As New GroupTableBuilder("按月分析",sql)
b.Groups.AddDef("日期", DateGroupEnum.Year, "年")
b.Groups.AddDef("日期", "月")
b.Groups.AddDef("单位")
b.Totals.AddDef("加工费")
b.Totals.AddDef("投工",AggregateEnum.Average,"月均人数")
b.SamePeriodGrowth = True
b.CircleGrowth = True
b.Build()
MainTable = Tables("按月分析")
Dim r As Table = Tables("按月分析")
r.DataTable.DataCols.Add("月人均加工费", Gettype(Double),"iif(月均人数=0,null,加工费/月均人数)")
DataTables("按月分析").DataCols("加工费").SetFormat("0.00")
DataTables("按月分析").DataCols("月均人数").SetFormat("0.00")
DataTables("按月分析").DataCols("月人均加工费").SetFormat("0.00")
Tables("按月分析").Cols("年").TextAlign = TextAlignEnum.Center
Tables("按月分析").Cols("月").TextAlign = TextAlignEnum.Center
Tables("按月分析").Cols("单位").TextAlign = TextAlignEnum.Center
Tables("按月分析").AutoSizeCols()
Case "按年分析"
Dim sql As String
If _UserGroup = "填报" Then '定义用户组
sql = "Select 报表年月,日期,单位,Sum(加工费) As 加工费,Sum(投工) As 投工 FROM {日报源表} WHERE 单位 = '" & _UserName & "' Group By 报表年月,日期,单位 ORDER BY 单位,日期,报表年月"
Else
sql = "Select 报表年月,日期,单位,Sum(加工费) As 加工费,Sum(投工) As 投工 FROM {日报源表} Group By 报表年月,日期,单位 ORDER BY 单位,日期,报表年月"
End If
Dim b As New GroupTableBuilder("按年分析",sql)
b.Groups.AddDef("日期", DateGroupEnum.Year, "年")
b.Groups.AddDef("单位")
b.Totals.AddDef("加工费")
b.Totals.AddDef("投工",AggregateEnum.Average,"年均人数")
b.CircleGrowth = True
b.Build()
MainTable = Tables("按年分析")
Dim r As Table = Tables("按年分析")
r.DataTable.DataCols.Add("年人均加工费", Gettype(Double),"iif(年均人数=0,null,加工费/年均人数)")
DataTables("按年分析").DataCols("加工费").SetFormat("0.00")
DataTables("按年分析").DataCols("年均人数").SetFormat("0.00")
DataTables("按年分析").DataCols("年人均加工费").SetFormat("0.00")
Tables("按年分析").Cols("年").TextAlign = TextAlignEnum.Center
Tables("按年分析").Cols("单位").TextAlign = TextAlignEnum.Center
Tables("按年分析").AutoSizeCols()
加好友 
发短信
Post By：2011/7/15 15:14:00 [只看该作者]
此主题相关图片如下：c.png
