专家坐堂-如何组合多个交叉统计结果表Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

MainTable = Tables("xszxztb")

Dim fil As String

If User.IsRole("学校资助专干") Then

fil="[gzxx]=\'" & user.group & "\' and "

Else

If User.IsRole("县区资助管理") Then

fil="[县区]=\'" & user.group & "\'and "

End If

Dim fil1 As String =e.Form.Controls("ComboBox2").text

If fil1="" Then

msgbox("你必须选择需要统计的学期")

Return

End If

Dim dr As DataRow

dr=DataTables("项目标准表").find("[xq]=\'" & fil1 & "\'")

Dim cssfgzbz As Integer=dr("cssfgzmxfbz")

Dim ncsfgzbz As Integer=dr("ncsfgzmxfbz")

Dim csfsfgzbz As Integer=dr("csfsfgzmxfbz")

Dim ncfsfgzbz As Integer=dr("ncfsfgzmxfbz")

Dim cssfgzzfbz As Integer=dr("cssfgzmzfbz")

Dim ncsfgzzfbz As Integer=dr("ncsfgzmzfbz")

Dim csfsfgzzfbz As Integer=dr("csfsfgzmzfbz")

Dim ncfsfgzzfbz As Integer=dr("ncfsfgzmzfbz")

Dim ydbz As Integer=dr("ydzxjbz")

Dim edbz As Integer=dr("edzxjbz")

Dim sdbz As Integer=dr("sdzxjbz")

Dim g1 As New sqlCrossTableBuilder("统计表1", "xszxztb")

Dim dt1 As fxDataSource

g1.C

g1.Caption =fil1 & "资助项目统计表"

g1.HGroups.AddDef("gzxx","学校名称")

g1.VGroups.AddDef("mxfxm")

g1.Totals.AddDef("sfzhm", AggregateEnum.Count, "人数")

g1.Totals.AddExp("免学费金额","(case when [xxsx]=\'城市示范高中 \' then " & cssfgzbz/2 & " when [xxsx]=\'农村示范高中\' then " & ncsfgzbz/2 & " when [xxsx]=\'城市非示范高中\' then " & csfsfgzbz/2 & " when [xxsx]=\'农村非示范高中\' then " & ncfsfgzbz/2 & " end)")

g1.HorizontalTotal = True

g1.VerticalTotal = True

g1.FromServer = True

g1.Filter =fil & "[xq]=\'" & fil1 & "\'and [mxfxm] is not null"

g1.Build

dt1 = g1.BuildDataSource()

Dim g2 As New sqlCrossTableBuilder("统计表2", "xszxztb")

Dim dt2 As fxDataSource

g2.HGroups.AddDef("gzxx","学校名称")

g2.VGroups.AddDef("mzfxm")

g2.Totals.AddDef("sfzhm", AggregateEnum.Count, "人数")

g2.Totals.AddExp("免杂费金额","(case when [xxsx]=\'城市示范高中 \' then " & cssfgzzfbz/2 & " when [xxsx]=\'农村示范高中\' then " & ncsfgzzfbz/2 & " when [xxsx]=\'城市非示范高中\' then " & csfsfgzzfbz/2 & " when [xxsx]=\'农村非示范高中\' then " & ncfsfgzzfbz/2 & " end)")

g2.HorizontalTotal = True

g2.VerticalTotal = True

g2.FromServer = True

g2.Filter =fil & "[xq]=\'" & fil1 & "\' and [mzfxm] is not null"

g2.Build

dt2 = g2.BuildDataSource()

Dim g3 As New SQLCrossTableBuilder("统计表3", "xszxztb")

Dim dt3 As fxDataSource

g3.C

g3.Caption =fil1 & "助学金统计表"

g3.HGroups.AddDef("gzxx","学校名称")

g3.VGroups.AddDef("zxjxm")

g3.Totals.AddDef("sfzhm", AggregateEnum.Count, "人数")

g3.Totals.AddExp("助学金额","(case when [zxjxm]=\'国家普通高中一等助学金\' then " & ydbz/2 & " when [zxjxm]=\'国家普通高中二等助学金\' then " & edbz/2 & " when [zxjxm]=\'国家普通高中三等助学金\' then " & sdbz/2 & " end)")

g3.Filter =fil & "[xq]=\'" & fil1 & "\' And [zxjxm] Is not null "

g3.HorizontalTotal = True

g3.VerticalTotal = True

g3.Decimals = 2

g3.FromServer = True

g3.Build

dt3 = g3.BuildDataSource()

dt1.Combine("gzxx",dt2,"gzxx") \'将免杂费统计数据组合到免学费统计数据

dt1.Combine("gzxx",dt3,"gzxx")

MainTable = Tables("统计表1")

e.Form.close()