Easy enough, right? The catch is that there can be more than one employee per case.
The solution was to put together a SQL function to return a comma separated list of Employees
CREATE function [dbo].[GetEmployeesByCaseId] (@caseid int)
--I return a comma separated list of Employees assigned to a case.
--USAGE select dbo.GetEmployeesByCaseId(18715)
--Created By Elizabeth Greene 8 October 2013
declare @EmployeeNames Varchar(1000)
select @EmployeeNames =
COALESCE(@EmployeeNames + ', ', '') +
COALESCE(employee_name_First,'') + ' ' +
inner join Employee
on EmployeeId= EmployeeAssignment_EmployeeId
employeeAssignment.EmployeeAssignment_caseid = @caseid
Then insert that function in the sql query that made the report
sql = "SELECT CaseID, Case_FileNumber, ClientID, SubjectID, Status_Name, client_name, Company_Name, Subject_State, Subject_FirstName, Subject_LastName, Subject_MiddleName, Case_DateStart, Case_DateEnd, Case_DateReopened ,dbo.GetEmployeesByCaseId(CaseId) as EmployeeName FROM [CaseListing] WHERE " ...
And finally add the column to the report
I kind of miss classic ASP; in the same way I miss my '72 Impala. It would be nice to have it back, but only if I didn't have to drive it. :)
This blog is kind of plain. I should fix that.