创建一个新的视图,视图名称:ProjectSchedule_View2,sql为
SELECT 负责人, PlWeek AS 年周, 异常分类, (CASE WHEN ReYq = 1 THEN '达成' ELSE '未达成' END) AS 达成,
(CASE WHEN ReYq = 1 THEN 1 ELSE NULL END) AS sl
FROM dbo.ProjectSchedule_View
union
SELECT distinct NUll as 负责人, null as 年周, 异常分类, (CASE WHEN ReYq = 0 THEN '达成' ELSE '未达成' END) AS 达成,
NULL AS sl
FROM dbo.ProjectSchedule_View
然后生成统计表参考:
Dim b As New CrossTableBuilder("Cj2", "select * from {ProjectSchedule_View2}", "t")
b.HGroups.AddDef("负责人")
b.HGroups.AddDef("年周")
b.VGroups.AddDef("异常分类", "异常分类_{0}")
b.VGroups.AddDef("达成")
b.Totals.AddDef("sl")
b.VerticalTotal = True
b.Build
DataTables("Cj2").DeleteFor("负责人 is null")
或者
Dim b As New sqlCrossTableBuilder("Cj1", "ProjectSchedule_View2")
b.ConnectionName = "t"
b.HGroups.AddDef("负责人")
b.HGroups.AddDef("年周")
b.VGroups.AddDef("异常分类", "异常分类_{0}")
b.VGroups.AddDef("达成")
b.Totals.AddDef("sl")
b.VerticalTotal = True
b.Build
DataTables("Cj1").DeleteFor("负责人 is null")