The first thing I did was create a new report called EmployeeList that listed all of the employees from the [Employees] table with SQL like this:
SELECT e.ID
,e.FName
,e.LName
,re.ID AS ReportEmployeeID
FROM [dbo].[Employees] e
LEFT JOIN [dbo].[ReportEmployees] re ON e.ID = re.ID
The output of the report was a list of all the employees in the system, from [dbo].[Employees], along with a second copy of their ID from the [dbo].[ReportEmployees] if they were currently in that table.
I used conditional formatting in the Tablix as follows:
If the re.ID (ReportEmployeeID) was NULL, the first column showed the Employee’s Name in shaded text, indicating that they were NOT currently in the report. The second column showed a clickable “+” with the action of passing e.ID to a second report that I will describe below.
If the re.ID (ReportEmployeeID) was NOT NULL, the first column showed the Employee’s Name in bold text, indicating that they WERE currently in the report, and the second column showed a clickable “-” with the action of passing the e.ID to the same second report.
