【报表】帆软报表设计小技巧1—巧妙使用参数

DECLARE @today DATETIME,								-- 今天@month_bg DATETIME,@month_end DATETIMEset @today=${if(len(月份)=0," getdate()","'2020-"+月份+"-01'")}SELECT @month_bg= CONVERT(VARCHAR(8),@today,120)+'01'   -- 本月初日期
select @month_end=dateadd(day,-1,convert(varchar(8),dateadd(m,1,@month_bg),120)+'1 00:00:00') -- 本月末日期SELECT field0326 一级部门,field0327 二级部门,field0328 三级部门,field0329 四级部门,CASE WHEN field0327 IS NULL THEN field0326 WHEN field0328 IS NULL AND field0327 IS NOT null THEN field0326+field0327WHEN field0329 IS NULL AND field0328 IS NOT NULL AND field0327 IS NOT NULL THEN field0326+field0327+field0328ELSE field0326+field0327+field0328+field0329 END 部门,	COUNT(field0002) 人数
FROM formmain_10037 WHERE field0003<=@today AND (field0192 IS NULL OR field0192 >@today)
and field0327 in('技术中心' ,'郑州技术部','武汉技术部')--一级部门
AND  CASE  (SELECT COUNT(1) FROM formmain_62430 t  WHERE t.field0015 IN (1,2,3,4)  AND t.field0003 = '${fine_username}' ) WHEN 0 THEN 1 else  CASE WHEN  field0326 IN (SELECT distinct t.field0004  FROM formmain_62430 t  WHERE  t.field0015 IN (1,2,3,4)  AND t.field0003 = '${fine_username}'  ) THEN 1 endEND   =1        
--二级部门
AND  CASE  (SELECT COUNT(1) FROM formmain_62430 t  WHERE t.field0015 IN (2,3,4)  AND t.field0003 = '${fine_username}' ) WHEN 0 THEN 1 else  CASE WHEN  field0327 IN (SELECT distinct t.field0005  FROM formmain_62430 t  WHERE  t.field0015 IN (2,3,4)  AND t.field0003 = '${fine_username}'  ) THEN 1 endEND   =1        --三级部门
AND  CASE  (SELECT COUNT(1) FROM formmain_62430 t  WHERE t.field0015 IN (3,4)  AND t.field0003 = '${fine_username}' ) WHEN 0 THEN 1 else  CASE WHEN  field0328 IN (SELECT distinct t.field0006  FROM formmain_62430 t  WHERE  t.field0015 IN (3,4)  AND t.field0003 = '${fine_username}'  ) THEN 1 endEND   =1     --四级部门
AND  CASE  (SELECT COUNT(1) FROM formmain_62430 t  WHERE t.field0015 IN (4)  AND t.field0003 = '${fine_username}' ) WHEN 0 THEN 1 else  CASE WHEN  field0329 IN (SELECT distinct t.field0007  FROM formmain_62430 t  WHERE  t.field0015 IN (4)  AND t.field0003 = '${fine_username}'  ) THEN 1 endEND   =1 GROUP BY field0326,field0327,field0328,field0329

set @today=${if(len(月份)=0," getdate()","'year(getdate())"+"-"+月份+"-01'")}   --自由设置日期

sqlserver 根据权限表匹配数据权限

--一级部门
AND  CASE  (SELECT COUNT(1) FROM formmain_62430 t  WHERE t.field0015 IN (1,2,3,4)  AND t.field0003 = '${fine_username}' ) WHEN 0 THEN 1 
else  CASE WHEN  
field0326 IN (SELECT distinct t.field0004  FROM formmain_62430 t  WHERE  t.field0015 IN (1,2,3,4)  AND t.field0003 = '${fine_username}'  ) THEN 1 end
END   =1        
--二级部门
AND  CASE  (SELECT COUNT(1) FROM formmain_62430 t  WHERE t.field0015 IN (2,3,4)  AND t.field0003 = '${fine_username}' ) WHEN 0 THEN 1 
else  CASE WHEN  
field0327 IN (SELECT distinct t.field0005  FROM formmain_62430 t  WHERE  t.field0015 IN (2,3,4)  AND t.field0003 = '${fine_username}'  ) THEN 1 end
END   =1        

--三级部门
AND  CASE  (SELECT COUNT(1) FROM formmain_62430 t  WHERE t.field0015 IN (3,4)  AND t.field0003 = '${fine_username}' ) WHEN 0 THEN 1 
else  CASE WHEN  
field0328 IN (SELECT distinct t.field0006  FROM formmain_62430 t  WHERE  t.field0015 IN (3,4)  AND t.field0003 = '${fine_username}'  ) THEN 1 end
END   =1     

--四级部门
AND  CASE  (SELECT COUNT(1) FROM formmain_62430 t  WHERE t.field0015 IN (4)  AND t.field0003 = '${fine_username}' ) WHEN 0 THEN 1 
else  CASE WHEN  
field0329 IN (SELECT distinct t.field0007  FROM formmain_62430 t  WHERE  t.field0015 IN (4)  AND t.field0003 = '${fine_username}'  ) THEN 1 end
END   =1