How to: Generate TR Cabin Crew Group Roster Report based on Department/Rank/Fleet

Generate Employee Roster Report separated by ranks (all 3 - CIC/CL/CC) and department (all 3 - pure 320/pure 787/dual fleet).

Due to the merger of TZ and TR, they have a mixed fleet of 320s and 787s. The cabin crew team has been qualified to fly on both these type of Aircraft. So, a new department for Dual Fleet (DF) was created to segregate those crews with the pure 787 and 320 crew qualified. Whenever the group roster report is requested, it is required to generate the extracts based on separate ranks and departments. Since the standard group roster report does not have a department filter a workaround has to be done to generate the extracts as per their request.

The workaround implemented is to modify the stored procedure of the CrewPortal reports.[GetEmployeeGroupRosterDetails] and add the EmployeeDepartment filter in the query. Once the report is generated, roll back the query to the original stored procedure.

Step-by-step guide

1. Log in to the SQL database with a admin access as you need to execute the stored procedure.
2. Make the change to this part of the query by adding ED.DepartmentId = 26 after E.Active = 1

3. Run the query against the database

4. Generate the report for CC, CIC, CL using MS Report Builder

   ................
   
   INSERT INTO #EmployeeList
   
   SELECT DISTINCT
   
       E.Id,
   
       E.EmpCode,
   
       CASE WHEN @ShowEmployeeName = 1 THEN E.Name4 + ', ' + E.Name1 ELSE '' END,
   
       EB.BaseId,
   
       R.Id,
   
       R.Code,
   
       R.Name,
   
       Port.Code
   
   FROM
   
       dbo.Employee E
   
       INNER JOIN dbo.EmployeeBase EB ON EB.EmployeeId = E.Id
   
       INNER JOIN dbo.EmployeeRating ER ON ER.EmployeeId = E.Id
   
       INNER JOIN dbo.[Rank] R ON R.Id = ER.RankId
   
       INNER JOIN dbo.Port ON Port.Id = EB.BaseId
   
       INNER JOIN dbo.EmployeeDepartment ED ON ED.EmployeeId = E.Id
   
       INNER JOIN dbo.DepartmentOperator DO ON DO.DepartmentId = ED.DepartmentId
   
       LEFT JOIN dbo.EquipmentGroup EG ON EG.Id = ER.EquipmentGroupId
   
       LEFT JOIN dbo.EquipmentType ET ON ET.EquipmentGroupId = EG.Id
   
   WHERE
   
       E.Active = 1  --AND ED.DepartmentId = 26
   
       AND EB.[From] <= @EndDate AND @StartDate <= EB.[To]
   
       AND ER.[From] <= @EndDate AND @StartDate <= ER.[To]
   
   ......................

Do the same steps for the other departments and generate the extract per rank

Related articles