Employee Productivity calculation in JRS
Hi,
Client's query is to calculate employee productivity based on effort spent on assigned tasks (Actual vs Planned). Using the following formula:
Total no of hours completed hours/allocated *100 against owner
I am using CLM V6.0.2 with MSSQL DB. A similar progress bar can be viewed in 'Work Breakdown' in plan view but any plan view attribute is unavailable in JRS. Estimate and Time spent attribute(available in workitem-task and in RTC query) are also not available in JRS. Is there any workaround to create to report as view it as a dashboard widget?
Actual Work and Planned work attributes gives null result.
I tried finding the derived values of actual and planned by the following formula but JRS is not supporting this complex query
Actual Effort: current date – creation date where resolved date is null
Planned Effort: (current date – creation date where resolved date is null) + (resolved date – creation date where resolved date is not null)
JRS QUERY:
SELECT DISTINCT T1.PROJECT_NAME,
T2.FULL_NAME,
SUM(CAST(CASE WHEN (T1.RESOLVED_DATE IS NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,GETDATE()) ELSE NULL END AS INT)) as Actual_Effort,
SUM(CASE WHEN (T1.RESOLVED_DATE IS NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,GETDATE()) ELSE NULL END) + SUM(CASE WHEN (T1.RESOLVED_DATE IS NOT NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,T1.RESOLVED_DATE) ELSE NULL END) AS PLANNED_EFFORT,
(CAST(SUM(CASE WHEN (T1.RESOLVED_DATE IS NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,GETDATE()) ELSE NULL END) AS DECIMAL(4,2)) / CAST(SUM(CASE WHEN (T1.RESOLVED_DATE IS NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,GETDATE()) ELSE NULL END) + SUM(CASE WHEN (T1.RESOLVED_DATE IS NOT NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,T1.RESOLVED_DATE) ELSE NULL END) AS DECIMAL(4,2)))*100 AS EMPLOYEE_PRODUCTIVITY
FROM RIDW.VW_REQUEST T1
LEFT OUTER JOIN RIDW.VW_RESOURCE T2
ON T2.RESOURCE_ID=T1.OWNER_ID
WHERE T1.PROJECT_ID = 0 AND
( (T1.REQUEST_STATE = 'Accepted' OR T1.REQUEST_STATE = 'Approved' OR T1.REQUEST_STATE = 'Deferred' OR T1.REQUEST_STATE = 'Deployed' OR T1.REQUEST_STATE = 'Design' OR T1.REQUEST_STATE = 'Development' OR T1.REQUEST_STATE = 'Duplicated' OR T1.REQUEST_STATE = 'In Progress' OR T1.REQUEST_STATE = 'Mitigated' OR T1.REQUEST_STATE = 'New' OR T1.REQUEST_STATE = 'Open' OR T1.REQUEST_STATE = 'Planning' OR T1.REQUEST_STATE = 'Proposed' OR T1.REQUEST_STATE = 'Ready For Deployment' OR T1.REQUEST_STATE = 'Rejected' OR T1.REQUEST_STATE = 'Reopened' OR T1.REQUEST_STATE = 'Requirement Analysis' OR T1.REQUEST_STATE = 'Requirement Gathering' OR T1.REQUEST_STATE = 'Review' OR T1.REQUEST_STATE = 'SQA Review 1' OR T1.REQUEST_STATE = 'SQA Review 2' OR T1.REQUEST_STATE = 'SQA Review 3' OR T1.REQUEST_STATE = 'SQA Review 4' OR T1.REQUEST_STATE = 'SQA Review 5' OR T1.REQUEST_STATE = 'Testing' OR T1.REQUEST_STATE = 'To Be Validated' OR T1.REQUEST_STATE = 'Triaged' OR T1.REQUEST_STATE = 'Unconfirmed' OR T1.REQUEST_STATE = 'Verified' )
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) GROUP BY T1.PROJECT_NAME,
T2.FULL_NAME
P.S Error Snapshot is also attached for reference.
|
Be the first one to answer this question!
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.