RDLC / sql returning a blank space instead of showing empty record... please see images

Question

I'm creating a RDLC report from a stored proc and grouping by the EmpNum field, but when sql returns no rows for that EmpNum it doesn't even show on report.

I have tried to use a LEFT OUTER JOIN with a table that contains the the full list of all EmpNum values but I still get the same result

Here is my sql proc

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

I want the report to still show the EmpNum even if there is no data:

[EDIT]

I have now I fixed the sql with a UNION with the list of EmpNum... Now I have to find a way to hide the empty row on the RDLC to get the above result...

Here is my attempt with the fixed sql :

And here is my curent 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

Answer 1

Thanks to @jdweng I figured out what I needed to do...

I made the sql UNION the List of EmpNum and then made the empty rows hide if certain value was an empty string...

Here is my final 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

and here is the row property :

and here is the result :

This Was The Link That Helped Me To Hide The Row : How to: Apply Conditional Visibility Controls