当ny=1的时候没有数据，当ny=2的时候就要数据 但是在数据集都可以显示 但在报表就无法显示 是啥问题啊数据集：SELECT a.分部简称,a.分部编码,a.区域简称,a.年,a.排序 ${if(ny='1',',(isnull(b.,0)+isnull(c./10000,0)+isnull(d./10000,0)+isnull(e./10000,0)+isnull(f./10000,0))月设计',',(isnull(b.,0)+isnull(c./10000,0)+isnull(d./10000,0)+isnull(e./10000,0)+isnull(f./10000,0)+isnull(g./10000,0)) as 年设计')} ${if(ny='1', ',(isnull(b.,0)+isnull(c./10000,0)+isnull(d./10000,0)+isnull(e./10000,0)+isnull(f./10000,0)) as 月工程',',(isnull(b.,0)+isnull(c./10000,0)+isnull(d./10000,0)+isnull(e./10000,0)+isnull(f./10000,0)+isnull(g./10000,0)) as 年工程')} from(SELECT DISTINCTb.fbjc2 as 分部简称,a.FNUMBER as 分部编码,b.qyjc as 区域简称,a.year as 年,b.first_no 排序from DW_MD_PerformanceTargetGC a LEFT JOIN dim_fb b on a.fnumber=b.fbbmWHERE a.FNUMBER<>'103') aLEFT JOIN (SELECT 分部简称,分部编码,年,sum(设计业绩) 设计业绩,sum(工程业绩) as 工程业绩 from (SELECT 分部简称,分部编码,年,sum(设计业绩) 设计业绩,sum(工程业绩) as 工程业绩 from (SELECT CASE WHEN fbjc2=N'中Y' AND month>=7 THEN N'大DY' ELSE fbjc2 END 分部简称,CASE WHEN ssfbbm='103.0302' AND month>=7 THEN '103.0304' ELSE ssfbbm end 分部编码,qyjc as 区域简称,year as 年,month as 月,sjyj as 设计业绩,gcyj as 工程业绩 FROM DM_YJ_QD_1 WHERE YEAR='${year(time)}' ${if(ny='1','and MONTH+0 ='+month(time),'and MONTH+0 <='+month(time))})a GROUP BY 分部简称,分部编码,区域简称,年 UNION ALLselect 分部简称,所属分部编码,年,sum(0) 设计业绩,sum(收款金额)/10000 收款金额 from (select 分部简称,所属分部编码,年,sum(收款金额) 收款金额 from (SELECT gdbm as 工地编码,gdmc as 工地名称,fbmc as 分部名称,fbjc2 as 分部简称,ssfbbm as 所属分部编码,skrq as 收款日期,xsbm as 销售部门,year as 年 ,month as 月 ,bz as 备注,skje as 收款金额from DM_YJ_ZC_1 WHERE year='${year(time)}' ${if(ny='1','and MONTH+0 ='+month(time),'and MONTH+0 <='+month(time))} and xsbm not like N'德拉尼%') aGROUP BY 分部简称,所属分部编码,年UNION all SELECT 分部简称,所属分部编码,年,sum(收款金额) 收款金额 from (SELECT gdbm as 工地代码,gdmc as 工地名称,fbmc as 分部名称,fbjc2 as 分部简称,ssfbbm as 所属分部编码,fbqygs as 分部所属区域,year as 年,month as 月,skje as 收款金额from DM_YJ_RZ_1where year='${year(time)}' ${if(ny='1','and MONTH+0 ='+month(time),'and MONTH+0 <='+month(time))} and fbqygs not in(N'深圳一区',N'深圳二区',N'深圳丹迪'))aGROUP BY 分部简称,所属分部编码,年)a WHERE 分部简称 =N'佰EB'GROUP BY 分部简称,所属分部编码,年UNION allselect 分部简称,分部编码,年,sum(0) as 设计业绩,sum(收款金额)/10000 as 收款金额 from(SELECT b.fbjc2 as 分部简称,b.fbbm as 分部编码,SUBSTRING(CONVERT(varchar(100), a.zjxrq, 23) , 1, 4) as 年 ,DATEPART(month,a.zjxrq) as 月 ,jsj as 收款金额 FROM DW_BEBCGYJ1_fr_1 a JOIN dim_fb b on a.fbbm=b.fbbm) a WHERE 年 ='${year(time)}' ${if(ny='1','and 月+0 ='+month(time),'and 月+0 <='+month(time))} GROUP BY 分部简称,分部编码,年UNION allselect 分部简称,所属分部编码,年,sum(0) as 设计业绩,sum(木作主材)/10000 木作主材 from(SELECT yclx as 异常类型 ,gdbm as 工地编码,gdmc as 工地名称,fbmc as 分部名称,CASE WHEN fbjc2=N'中Y' AND month>=7 THEN N'大DY' ELSE fbjc2 END 分部简称,CASE WHEN ssfbbm='103.0302' AND month>=7 THEN '103.0304' ELSE ssfbbm end 所属分部编码,qyjc as 区域简称,ycrq as 收款日期,year as 年,month as 月,mzzc as 木作主材FROM DM_YJ_YC_1 where year='${year(time)}' ${if(ny='1','and MONTH+0 ='+month(time),'and MONTH+0 <='+month(time))}and yclx=N'主材平分')a WHERE 分部简称=N'佰EB'GROUP BY 分部简称,所属分部编码,年)a GROUP BY 分部简称,分部编码,年)b on a.分部编码=b.分部编码 and a.=b.LEFT JOIN (SELECT 分部简称,分部编码,年,sum(折前设计费)工程重签折前设计费,sum(折前工程)工程重签折前工程 from (SELECT CASE WHEN fbjc2=N'中Y' AND month>=7 THEN N'大DY' ELSE fbjc2 END 分部简称,CASE WHEN ssfbbm='103.0302' AND month>=7 THEN '103.0304' ELSE ssfbbm end 分部编码,qyjc as 区域简称,year as 年,month as 月,yclx as 异常类型,zqsjf as 折前设计费,zqgc as 折前工程fromDM_YJ_YC_1 WHERE YEAR='${year(time)}'${if(ny='1','and MONTH+0 ='+month(time),'and MONTH+0 <='+month(time))} and yclx=N'工程重签') a GROUP BY 分部简称,分部编码,年) c on a.分部编码=c.分部编码 and a.=c.left JOIN(SELECT 分部简称,分部编码,年,sum(折前设计费)月工程退单折前设计费,sum(折前工程)月工程退单折前工程 from(SELECT CASE WHEN fbjc2=N'中Y' AND month>=7 THEN N'大DY' ELSE fbjc2 END 分部简称,CASE WHEN ssfbbm='103.0302' AND month>=7 THEN '103.0304' ELSE ssfbbm end 分部编码,qyjc as 区域简称,year as 年,month as 月,yclx as 异常类型,zqsjf as 折前设计费,zqgc as 折前工程fromDM_YJ_YC_1 WHERE YEAR ='${year(time)}' ${if(ny='1','and MONTH+0 ='+month(time),'and MONTH+0 <='+month(time))} and yclx=N'工程退单' and kjwd=N'月') a GROUP BY 分部简称,分部编码,年)d on a.分部编码=d.分部编码 and a.=d. LEFT JOIN (SELECT 分部简称,分部编码,年,sum(折前设计费)工程平分折前设计费,sum(折前工程)工程平分折前工程 from (SELECT CASE WHEN fbjc2=N'中Y' AND month>=7 THEN N'大DY' ELSE fbjc2 END 分部简称,CASE WHEN ssfbbm='103.0302' AND month>=7 THEN '103.0304' ELSE ssfbbm end 分部编码,qyjc as 区域简称,year as 年,month as 月,yclx as 异常类型,zqsjf as 折前设计费,zqgc as 折前工程fromDM_YJ_YC_1 WHERE YEAR='${year(time)}' ${if(ny='1','and MONTH+0 ='+month(time),'and MONTH+0 <='+month(time))} and yclx=N'工程平分')a GROUP BY 分部简称,分部编码,年)e on a.分部编码=e.分部编码 and a.=e. LEFT JOIN (SELECT 分部简称,分部编码,年,sum(折前设计费)工程补算折前设计费,sum(折前工程)工程补算折前工程 from( SELECT CASE WHEN fbjc2=N'中Y' AND month>=7 THEN N'大DY' ELSE fbjc2 END 分部简称,CASE WHEN ssfbbm='103.0302' AND month>=7 THEN '103.0304' ELSE ssfbbm end 分部编码,qyjc as 区域简称,year as 年,month as 月,yclx as 异常类型,zqsjf as 折前设计费,zqgc as 折前工程fromDM_YJ_YC_1 WHERE YEAR='${year(time)}' ${if(ny='1','and MONTH+0 ='+month(time),'and MONTH+0 <='+month(time))} and yclx in (N'工程补算',N'工程日期变更'))a GROUP BY 分部简称,分部编码,年)f on a.分部编码=f.分部编码 and a.=f. LEFT JOIN (SELECT 分部简称,分部编码,年,sum(折前设计费)年工程退单折前设计费,sum(折前工程)年工程退单折前工程 from(SELECT CASE WHEN fbjc2=N'中Y' AND month>=7 THEN N'大DY' ELSE fbjc2 END 分部简称,CASE WHEN ssfbbm='103.0302' AND month>=7 THEN '103.0304' ELSE ssfbbm end 分部编码,qyjc as 区域简称,year as 年,month as 月,yclx as 异常类型,zqsjf as 折前设计费,zqgc as 折前工程fromDM_YJ_YC_1 WHERE YEAR ='${year(time)}' ${if(ny='1','and MONTH+0 ='+month(time),'and MONTH+0 <='+month(time))} and yclx=N'工程退单' and kjwd=N'年') aGROUP BY 分部简称,分部编码,年) gon a.分部编码=g.分部编码 and a.=g.ORDER BY a.+0
