I need the report to have the following layout: CHAIRPERSON VICE-CHAIRPERSON EMPLOYEEE REPRESENTATIVE EMPLOYER REPRESENTATIVE What I am getting out of the report. CHAIRPERSON EMPLOYEEE REPRESENTATIVE EMPLOYER REPRESENTATIVE VICE-CHAIRPERSON IS there away I can fix this? SELECT dbo.tblBoardMembers.FirstName, dbo.tblBoardMembers.MidName, dbo.tblBoardMembers.LastName, dbo.tblBoardMembers.BM_Current, dbo.tblBoardPositions.Position FROM dbo.tblBoardMembers LEFT OUTER JOIN dbo.tblBoardPositions ON dbo.tblBoardMembers.PositionID = dbo.tblBoardPositions.BrdPositionID where dbo.tblBoardPositions.Position <> 'N/A'Mathieu Alexandre Cupryk www.omegalove.com

Try to do custom sort. Something like this - CASE WHEN dbo.tblBoardPositions.Position = 'CHAIRPERSON' THEN 0 WHEN dbo.tblBoardPositions.Position = 'VICE-CHAIRPERSON' THEN 1 WHEN dbo.tblBoardPositions.Position = 'EMPLOYEEE REPRESENTATIVE' THEN 2 WHEN dbo.tblBoardPositions.Position = 'EMPLOYER REPRESENTATIVE' THEN 3 END AS SortBy and order the report on SortBy. Hope that helps.

do I do this in the query.Mathieu Alexandre Cupryk www.omegalove.com

SELECT dbo.tblBoardMembers.BrdMemberID, dbo.tblBoardMembers.FirstName, dbo.tblBoardMembers.MidName, dbo.tblBoardMembers.LastName, dbo.tblBoardMembers.TermLength, dbo.tblBoardMembers.Appointed, dbo.tblBoardMembers.ExpiryDate,dbo.tblBoardMembers.BM_Current , dbo.tblBoardPositions.Position FROM dbo.tblBoardMembers LEFT OUTER JOIN dbo.tblBoardPositions ON dbo.tblBoardMembers.PositionID = dbo.tblBoardPositions.BrdPositionID where dbo.tblBoardPositions.Position <> 'N/A' and dbo.tblBoardMembers.BM_Current <> 'PAST' order by CASE WHEN dbo.tblBoardPositions.Position = 'CHAIRPERSON' THEN 0 WHEN dbo.tblBoardPositions.Position = 'VICE-CHAIRPERSON' THEN 1 WHEN dbo.tblBoardPositions.Position = 'EMPLOYEEE REPRESENTATIVE' THEN 2 WHEN dbo.tblBoardPositions.Position = 'EMPLOYER REPRESENTATIVE' THEN 3 END AS SortBy I have a problem with the ASMathieu Alexandre Cupryk www.omegalove.com

SELECT dbo.tblBoardMembers.BrdMemberID, dbo.tblBoardMembers.FirstName, dbo.tblBoardMembers.MidName, dbo.tblBoardMembers.LastName, dbo.tblBoardMembers.TermLength, dbo.tblBoardMembers.Appointed, dbo.tblBoardMembers.ExpiryDate,dbo.tblBoardMembers.BM_Current , dbo.tblBoardPositions.Position FROM dbo.tblBoardMembers LEFT OUTER JOIN dbo.tblBoardPositions ON dbo.tblBoardMembers.PositionID = dbo.tblBoardPositions.BrdPositionID where dbo.tblBoardPositions.Position <> 'N/A' and dbo.tblBoardMembers.BM_Current <> 'PAST' order by dbo.tblBoardPositions.Position WHEN dbo.tblBoardPositions.Position = 'CHAIRPERSON' THEN 0 WHEN dbo.tblBoardPositions.Position = 'VICE-CHAIRPERSON' THEN 1 WHEN dbo.tblBoardPositions.Position = 'EMPLOYEEE REPRESENTATIVE' THEN 2 WHEN dbo.tblBoardPositions.Position = 'EMPLOYER REPRESENTATIVE' THEN 3 END This is not working.Mathieu Alexandre Cupryk www.omegalove.com

I tired the following: SELECT dbo.tblBoardMembers.BrdMemberID, dbo.tblBoardMembers.FirstName, dbo.tblBoardMembers.MidName, dbo.tblBoardMembers.LastName, dbo.tblBoardMembers.TermLength, dbo.tblBoardMembers.Appointed, dbo.tblBoardMembers.ExpiryDate,dbo.tblBoardMembers.BM_Current , dbo.tblBoardPositions.Position FROM dbo.tblBoardMembers LEFT OUTER JOIN dbo.tblBoardPositions ON dbo.tblBoardMembers.PositionID = dbo.tblBoardPositions.BrdPositionID where ( dbo.tblBoardPositions.Position <> 'N/A' and dbo.tblBoardMembers.BM_Current <> 'PAST') order by case dbo.tblBoardPositions.Position WHEN 'CHAIRPERSON' THEN 0 WHEN 'VICE-CHAIRPERSON' THEN 1 WHEN 'EMPLOYEEE REPRESENTATIVE' THEN 2 WHEN 'EMPLOYER REPRESENTATIVE' THEN 3 END Still not working.,Mathieu Alexandre Cupryk www.omegalove.com

Where is it not working, in the report or in management studio? If in the report, please be sure that you are "not" sorting the report by any other column.

it works in query manager but when I do it in SSRS I get only the chairperson outputed. SELECT dbo.tblBoardMembers.BrdMemberID, dbo.tblBoardMembers.FirstName, dbo.tblBoardMembers.MidName, dbo.tblBoardMembers.LastName, dbo.tblBoardMembers.BM_Current, dbo.tblBoardPositions.Position as [PositionBy] FROM dbo.tblBoardMembers LEFT OUTER JOIN dbo.tblBoardPositions ON dbo.tblBoardMembers.PositionID = dbo.tblBoardPositions.BrdPositionID where ( dbo.tblBoardPositions.Position <> 'N/A' and dbo.tblBoardMembers.BM_Current <> 'PAST') order by case dbo.tblBoardPositions.Position WHEN 'Chairperson' THEN 0 WHEN 'Vice-Chairperson' THEN 1 WHEN 'Employee Representative' THEN 2 WHEN 'Employer Representative' THEN 3 ENDMathieu Alexandre Cupryk www.omegalove.com

Are you sorting in the report?

now the query in the report repeats the Position for each person.Mathieu Alexandre Cupryk www.omegalove.com

how do I add the sorting ORDER BY CASE dbo.tblBoardPositions.Position WHEN 'Chairperson' THEN 0 WHEN 'Vice-Chairperson' THEN 1 WHEN 'Employee Representative' THEN 2 WHEN 'Employer Representative' THEN 3 ENDMathieu Alexandre Cupryk www.omegalove.com

Switch( Fields!PositionBy.Value= 1,1, Fields!PositionBy.value= 2,2, Fields!PositionBy.value= 3,3, Fields!PositionBy.value= 4,4) does not workMathieu Alexandre Cupryk www.omegalove.com

how do I add the sorting ORDER BY CASE dbo.tblBoardPositions.Position WHEN 'Chairperson' THEN 0 WHEN 'Vice-Chairperson' THEN 1 WHEN 'Employee Representative' THEN 2 WHEN 'Employer Representative' THEN 3 END Mathieu Alexandre Cupryk www.omegalove.com Where i the report? Use the case statement in the SELECT and give it an alias like SortBy and use it to sort in the report.

ok, I added the following in query builder: SELECT dbo.tblBoardMembers.BrdMemberID, dbo.tblBoardMembers.FirstName, dbo.tblBoardMembers.MidName, dbo.tblBoardMembers.LastName, dbo.tblBoardMembers.BM_Current, dbo.tblBoardPositions.Position AS PositionBy, CASE dbo.tblBoardPositions.Position WHEN 'Chairperson' THEN 0 WHEN 'Vice-Chairperson' THEN 1 WHEN 'Employee Representative' THEN 2 WHEN 'Employer Representative' THEN 3 END AS SortBy FROM dbo.tblBoardMembers LEFT OUTER JOIN dbo.tblBoardPositions ON dbo.tblBoardMembers.PositionID = dbo.tblBoardPositions.BrdPositionID WHERE (dbo.tblBoardPositions.Position <> 'N/A') AND (dbo.tblBoardMembers.BM_Current <> 'PAST') ORDER BY CASE dbo.tblBoardPositions.Position WHEN 'Chairperson' THEN 0 WHEN 'Vice-Chairperson' THEN 1 WHEN 'Employee Representative' THEN 2 WHEN 'Employer Representative' THEN 3 END I right click the row for position and set sort to SortBy But it repeates the position for each person.Mathieu Alexandre Cupryk www.omegalove.com