开发公司请假系统中给ＨＲ的报表遇到的问题

１　查看报表时有三个参数，Ｙｅａｒ，Ｄｅｐａｒｔｍｅｎｔ，Ｅｍｐｌｏｙｅｅ。要将ｄｅｐａｒｔｍｅｎｔ与ｅｍｐｌｏｙｅｅ实现级联，做下面操作：

对ｅｍｐｌｏｙｅｅ这个ｄａｔａｓｅｔ加一个参数DepName　

完整的：

SELECT     'ALL' AS UserName
UNION ALL
SELECT DISTINCT UserName
FROM         dbo.Step7_HR_Report
WHERE     ((UserName <> 'System Account') AND (DepartmentName=@DepName))

 

２　ｈｏｗ　ｔｏ　ｕｎｉｏｎ　ａｌｌ　ｔｏ　ｐａｒａｍｅｔｅｒ

需要对每个含有ａｌｌ　的参数的ｄａｔａｓｅｔ做修改，参数的属性里面的Ｌａｂｅｌ　ｆｉｅｌｄ选ＲｅｃｏｒｄＹｅａｒ．ｖａｌｕｅ　ｆｉｅｌｄ选ＲＹ．

原ＳＱＬ语句：

SELECT     'ALL' AS RecordYear
UNION ALL
SELECT DISTINCT RTRIM(RecordYear) AS Expr1
FROM         dbo.Step4_HR_Report

 

修改后的：

SELECT NULL AS RY,  'All' AS RecordYear
UNION ALL
SELECT DISTINCT RTRIM(RecordYear) AS RY,Convert(varchar,RecordYear) AS RecordYear
   
FROM
     dbo.Step4_HR_Report

参考资料：

１　http://www.sqldev.org/sql-server-reporting-services/question-on-parameters-46705.shtml

２　http://stackoverflow.com/questions/1505784/sql-reporting-services-parameter-question

３　http://blogs.msdn.com/chrishays/