主题：[求助]能否在交叉表的垂直分组列中增加列（已解决）

If e.Form.Controls("ComboBox2").Value Is Nothing Then

    MessageBox.Show("请在右侧选择生成报表的年份","提示")

    Return

End If

'根据年份生成临时表

Dim nf,sql1,sql2,sql3,s1,s2,jgf,rjs,rjjgf,wb,Bs As String

nf = e.Form.Controls("ComboBox2").Value

sql1 = " From(Select 报表年月,日期,单位,Sum(加工费) As 加工费,Sum(投工) As 投工 FROM {日报源表} WHERE Year(日期) = "

sql1+= nf & " Group By 报表年月,日期,单位 ORDER BY 单位,日期,报表年月) Group By 单位"

sql2="Select DISTINCT 报表年月 FROM {日报源表} WHERE Year(日期) = " & nf

Dim cmd As New SQLCommand

Dim dt As DataTable

cmd.CommandText = sql2

dt = cmd.ExecuteReader()

Dim Ls As List(Of String)

Ls =dt.GetUniqueValues("","报表年月")

For n As Integer = 0 To ls.Count-1

    s1 = "sum(iif(报表年月 = " & Ls(n) & ",加工费,Null))"

    jgf = s1 & " as " &  Ls(n) & "_加工费"

    s2 = "avg(iif(报表年月 = " & Ls(n) & ",投工,Null))"

    rjs = s2 & " as " &  Ls(n) & "_月均人数"

    rjjgf= s1 & "/" & s2 & " as " &  Ls(n) & "_人均加工费"

    If n >0 Then

        wb = s1 & "/" & s2 & "/(sum(iif(报表年月 = " & Ls(n-1) & ",加工费,Null))/ avg(iif(报表年月 = " & Ls(n-1) & ",投工,Null))" & ") as " &  Ls(n) & "_环比"

        If n =  ls.Count-1 Then

            wb = wb & ",Sum(加工费) As 合计_加工费,sum(投工) as 合计_年均人数,sum(加工费) as 年人均加工费,Sum(加工费) As 名次"

        End If

    End If

    sql3+ = "," & jgf &  "," & rjs & "," & rjjgf & ","  & wb

Next

sql3="select 单位" & sql3.Replace(",,",",") & sql1

Dim b As New SQLGroupTableBuilder("年度汇总","日报源表")

b.Groups.AddDef("单位") '根据产品分组

b.Totals.AddDef("加工费") '对数量进行统计

b.Build '生成统计表

DataTables("年度汇总").Fill(sql3,True)

For Each dc As DataCol In  DataTables("年度汇总").DataCols

    If dc.name <> "单位" Then

        DataTables("年度汇总").DataCols(dc.Name).SetFormat("0.00")

        bs+="," & dc.name

    End  If

Next

DataTables("年度汇总").DataCols("名次").SetFormat("0")

Dim sum As Double

For Each r As Row In Tables("年度汇总").Rows

    sum =0

    For Each dc As DataCol In DataTables("年度汇总").DataCols

        If dc.name.IndexOf("_月均人数")> -1 Then

            sum+= r(dc.name)

        End If

        If dc.name = "合计_年均人数" Then

            r("合计_年均人数") = sum/ls.Count

            r("年人均加工费") = ls.Count*r("合计_加工费")/sum

            Exit For

        End If

    Next

Next

Dim drs As List(Of DataRow) = DataTables("年度汇总").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

Dim t As Table = Tables("年度汇总")

Dim g As SubtotalGroup

t.SubtotalGroups.Clear()

t.GroupAboveData = False

t.TreeVisible = False

t.SpillNode = True

g = New Subtotalgroup

g.Aggregate = AggregateEnum.Sum

g.GroupOn = "*"

g.TotalOn = bs.Trim(",").Replace(",名次","")

g.Caption = "合计"

t.SubtotalGroups.Add(g)

t.Subtotal()

Dim r1 As Row

With Tables("年度汇总")

    For i As Integer = 0 To .Rows.Count(True) - 1 'Count加上参数True

        r1 = .Rows(i,True) 'Rows也需要加上参数True

        If r1.IsGroup Then '如果是分组行

            For n As Integer = 0 To .Cols.Count -1

                If .cols(n).name.Indexof("人均加工费")> -1 Then

                    r1(.cols(n).name) = r1(.cols(n-2).name)/r1(.cols(n-1).name)

                End If

                

            Next

        End If

    Next

End With

Tables("年度汇总").Cols("单位").TextAlign = TextAlignEnum.Center

Tables("年度汇总").Cols("名次").TextAlign = TextAlignEnum.Center

Tables("年度汇总").AutoSizeCols()

MainTable = Tables("年度汇总") '