[浏览完整版]
标题：[求助]统计问题
21楼
有点蓝 发表于：2024/6/6 21:06:00必须是sqlCrossTableBuilder，才能使用AddExp
Dim tbCj As Table = Tables(e.Form.Name & "_TabProjectSchedule_Cj")
Dim b As New sqlCrossTableBuilder("Cj1", "select 负责人,PlWeek as 年周,异常分类,ReYq as 达成,1 as sl from {ProjectSchedule_View} ","数据源名称")
b.HGroups.AddDef("负责人")
b.HGroups.AddDef("年周")
b.VGroups.AddDef("异常分类", "异常分类_{0}")
b.VGroups.AddExp("达成", "iif(达成 = 1,'达成','未达成')")
b.Totals.AddDef("sl")
b.VerticalTotal = True
tbcj.DataSource = b.BuildDataSource
22楼
2900819580 发表于：2024/6/6 21:52:00以下是引用有点蓝在2024/6/6 21:06:00的发言：
必须是sqlCrossTableBuilder，才能使用AddExp
必须是sqlCrossTableBuilder，才能使用AddExp
Dim tbCj As Table = Tables(e.Form.Name & "_TabProjectSchedule_Cj")
Dim b As New sqlCrossTableBuilder("Cj1", "select 负责人,PlWeek as 年周,异常分类,ReYq as 达成,1 as sl from {ProjectSchedule_View} ","数据源名称")
b.HGroups.AddDef("负责人")
b.HGroups.AddDef("年周")
b.VGroups.AddDef("异常分类", "异常分类_{0}")
b.VGroups.AddExp("达成", "iif(达成 = 1,'达成','未达成')")
b.Totals.AddDef("sl")
b.VerticalTotal = True
tbcj.DataSource = b.BuildDataSource
这样更改又会出现16楼的错误。
23楼
有点蓝 发表于：2024/6/6 22:11:00导出几个表的数据，和视图的sql发上来测试一下
24楼
2900819580 发表于：2024/6/7 11:09:00
25楼
有点蓝 发表于：2024/6/7 16:20:00创建一个新的视图，视图名称：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")