c# - RDLC / sql 返回一个空格而不是显示空记录...请看图片

Question

我正在从存储的过程中创建一个 RDLC 报告并按 EmpNum 字段分组，但是当 sql 没有返回该 EmpNum 的行时，它甚至不会显示在报告中。

我尝试将 LEFT OUTER JOIN 与包含所有 EmpNum 值的完整列表的表一起使用，但我仍然得到相同的结果

这是我的 sql 过程

declare  @FromD as date = '2019-01-01',
           @ToD as date = '2019-01-05'

Select EmployeeData.EmpNum, EmployeeData.PayrollNum, EmployeeData.Name, EmployeeData.Button, 
                       (SELECT Top 1 Description FROM CompanyCodes WHERE Code = EmployeeData.CompanyCode ) AS CompanyCode, 
                       (SELECT Top 1 Description FROM Departments WHERE Code = EmployeeData.Department ) AS Department, 
                       (SELECT Top 1 Description FROM ReportCentres WHERE Code = EmployeeData.ReportCentre ) AS ReportCentre,
                        ClockDate.CalcDate, ClockDate.Day,ClockDate.ID,
                       (SELECT COUNT(*) From Clocking Where IO='In' AND EmpNum = EmployeeData.EmpNum AND CalcDate = ClockDate.CalcDate) as INS,
                       (SELECT COUNT(*) From Clocking Where IO='Out' AND EmpNum = EmployeeData.EmpNum AND CalcDate = ClockDate.CalcDate)as OUTS
                       ,@FromD AS FD, @ToD AS TD
FROM EmployeeData LEFT OUTER JOIN ClockDate 
    ON EmployeeData.EmpNum = ClockDate.EmpNum
WHERE ClockDate.CalcDate BETWEEN @FromD AND @ToD
order by EmployeeData.EmpNum, ClockDate.CalcDate

即使没有数据，我希望报告仍显示 EmpNum：

[编辑]

我现在已经用带有 EmpNum 列表的 UNION 修复了 sql... 现在我必须找到一种方法来隐藏 RDLC 上的空行以获得上述结果...

这是我对固定 sql 的尝试：

这是我当前的 sql ：

declare  @FromD as date = '2019-01-01',
           @ToD as date = '2019-01-05'

Select EmployeeData.EmpNum, EmployeeData.PayrollNum, EmployeeData.Name, EmployeeData.Button, 
                       (SELECT Top 1 Description FROM CompanyCodes WHERE Code = EmployeeData.CompanyCode ) AS CompanyCode, 
                       (SELECT Top 1 Description FROM Departments WHERE Code = EmployeeData.Department ) AS Department, 
                       (SELECT Top 1 Description FROM ReportCentres WHERE Code = EmployeeData.ReportCentre ) AS ReportCentre,
                        ClockDate.CalcDate, ClockDate.Day,ClockDate.ID,
                       (SELECT COUNT(*) From Clocking Where IO='In' AND EmpNum = EmployeeData.EmpNum AND CalcDate = ClockDate.CalcDate) as INS,
                       (SELECT COUNT(*) From Clocking Where IO='Out' AND EmpNum = EmployeeData.EmpNum AND CalcDate = ClockDate.CalcDate)as OUTS
                       ,@FromD AS FD, @ToD AS TD
FROM EmployeeData LEFT OUTER JOIN ClockDate 
    ON EmployeeData.EmpNum = ClockDate.EmpNum
WHERE ClockDate.CalcDate BETWEEN @FromD AND @ToD
--order by EmployeeData.EmpNum, ClockDate.CalcDate
Union
Select EmployeeData.EmpNum, EmployeeData.PayrollNum, EmployeeData.Name, EmployeeData.Button,
                       ('') AS CompanyCode, 
                       ('') AS Department, 
                       ('') AS ReportCentre,
                       ('')as CalcDate, ('')as Day,('')as ID,
                       ('') as INS,
                       ('')as OUTS
                       ,@FromD AS FD, @ToD AS TD
From EmployeeData
order by EmployeeData.EmpNum, ClockDate.CalcDate

标签： c#sqlrdlc

Answer 1

感谢@jdweng，我知道我需要做什么......

我将 sql UNION 设为 EmpNum 列表，然后如果某个值是空字符串，则隐藏空行...

这是我的最终 sql：

declare  @FromD as date = '2019-01-01',
           @ToD as date = '2019-01-05'

Select EmployeeData.EmpNum, EmployeeData.PayrollNum, EmployeeData.Name, EmployeeData.Button, 
                       (SELECT Top 1 Description FROM CompanyCodes WHERE Code = EmployeeData.CompanyCode ) AS CompanyCode, 
                       (SELECT Top 1 Description FROM Departments WHERE Code = EmployeeData.Department ) AS Department, 
                       (SELECT Top 1 Description FROM ReportCentres WHERE Code = EmployeeData.ReportCentre ) AS ReportCentre,
                        ClockDate.CalcDate, ClockDate.Day,ClockDate.ID,
                       (SELECT COUNT(*) From Clocking Where IO='In' AND EmpNum = EmployeeData.EmpNum AND CalcDate = ClockDate.CalcDate) as INS,
                       (SELECT COUNT(*) From Clocking Where IO='Out' AND EmpNum = EmployeeData.EmpNum AND CalcDate = ClockDate.CalcDate)as OUTS
                       ,@FromD AS FD, @ToD AS TD
FROM EmployeeData LEFT OUTER JOIN ClockDate 
    ON EmployeeData.EmpNum = ClockDate.EmpNum
WHERE ClockDate.CalcDate BETWEEN @FromD AND @ToD
--order by EmployeeData.EmpNum, ClockDate.CalcDate
Union
Select EmployeeData.EmpNum, EmployeeData.PayrollNum, EmployeeData.Name, EmployeeData.Button,
                       ('') AS CompanyCode, 
                       ('') AS Department, 
                       ('') AS ReportCentre,
                       ('')as CalcDate, ('')as Day,('')as ID,
                       ('') as INS,
                       ('')as OUTS
                       ,@FromD AS FD, @ToD AS TD
From EmployeeData
Where EmployeeData.EmpNum not in (Select EmployeeData.EmpNum FROM EmployeeData LEFT OUTER JOIN ClockDate 
                                    ON EmployeeData.EmpNum = ClockDate.EmpNum
                                    WHERE ClockDate.CalcDate BETWEEN @FromD AND @ToD)
order by EmployeeData.EmpNum, ClockDate.CalcDate

这是行属性：

结果如下：

这是帮助我隐藏行的链接： 如何：应用条件可见性控件