-1

I have the data table below:

--> Click here for the picture.

I want to add new column and count how times an employee is present in the office something like this:

--> Click here for the picture.

How can I count it in expression given that I have group in Employee.

Thank you.

dalday
  • 31
  • 1
  • 6

3 Answers3

0

You can do it via SQL script.

SELECT A.EMPLOYEE_NAME,A.DATE,A.ATTENDANCE,B.DAYS_WORKED 
FROM TABLE A
    LEFT JOIN 
    (SELECT EMPLOYEE_NAME,COUNT(ATTENDANCE) DAYS_WORKED
        FROM (
            SELECT EMPLOYEE_NAME,DATE,ATTENDANCE 
            FROM TABLE
            WHERE ATTENDANCE = 'IN-OFFICE')
            ) B
    ON A.EMPLOYEE_NAME = B.EMPLOYEE_NAME

You now have the Field DAYS_WORKED then just add it to your table.

Aldrin
  • 756
  • 6
  • 18
  • Thanks. but I am using fetch XML query as I am retrieving data in cloud server. – dalday Jun 03 '16 at 03:41
  • you should have added it to your post. and also to the tag. you included sql-server so I expected you use it as your back-end source. – Aldrin Jun 03 '16 at 07:04
0

You can use LookupSet() function to get the In-Office count. Add a tablix with the Employee Name group.

enter image description here

Note the Days Worked column is inside the Employee group scope but outside the details group scope.

Use this expression to get the count of Attendance In-Office per employee:

=LookupSet(Fields!Employee.Value & "-" & "In-Office",
  Fields!Employee.Value & "-" & Fields!Attendance.Value,
  Fields!Attendance.Value,"DataSetName"
 ).Length

Replace DataSetName by the actual name of your dataset. It will produce the below tablix:

enter image description here

UPDATE: Based on OP's comment.

Replace the LookupSet expression and use this instead to add multiple criteria to the filtered count.

=COUNT(IIF(Fields!Attendance.Value="In-Office" OR 
Fields!Attendance.Value="Out for Official Business",
Fields!Attendance.Value,Nothing))

It counts In-Office and Out for Official Business rows in the given group.

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Thank you alejandro zuleta. What if i gonna add new attendance status (out for official business) and i want it to be added in count, so the count will include "in-office" and "out for official business". would it possible? – dalday Jun 03 '16 at 04:26
  • it works. Thank you for your help.. it is very well appreciated. – dalday Jun 06 '16 at 03:49
0

=Count(Fields!EmployeeName.Value,"DataSet1")

suresh64
  • 1
  • ssrs report in employee count calculation on experession =Count(Fields!EmployeeName.Value,"DataSet1") – suresh64 Aug 11 '17 at 11:42