SQL Daily Totals

Question

Ok so what I am trying to make is a ssrs report that gets daily totals of clients seen per employee and list that day by day. So the top row would show every day in a month e.g 1st ,2nd,3rd. The first column should show the names of the employee.

I'm pretty sure I nee to use a matrix to do this but am unsure how to go about this with listing out the days. If anyone can help me or point me in the right direction that would be great.

To add more info I have a table called Employees with all the employees I want to list and a table appointments with all the clients that the employees have seen. I want to total the clients seen for each employee for every day of the month with the days listed across the top of the table.

for example

      | 1st | 2nd | 3rd | 4th | 5th | 6th | 7th .....
emp1     2     3     5     4     5     5     6
emp2     3     4     5     9     1     3     5
emp3     1     0     0     4     5     9     2
emp4     8     3     7     2     9     3     4

Answer 1

If you want your rows to be columns, you need to use pivot like so:

Example:

SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;

If you want your columns to be rows, you would use UNPIVOT.

Answer 2

You are correct to use a matrix.
Assuming you already have a query selecting the necessary information and joining properly:
Create a Column Group, grouping by the appointment date. Format your column header using Day(Fields!AppointmentDateTime.Value) to get the value from 1 to 31.
Create a Row Group, grouping by Employee. In the data cell, total the visits by using =Count(AppointmentDateTime.Value).
If this report will span several months, include a parent column group where you group by Month(Fields!AppointmentDateTime.Value). You can also add totals by right-clicking on a textbox in the row group and selecting "Add Total".