It's all about the answers!

Ask a question

Employee Productivity calculation in JRS


hani zaidi (1125) | asked Sep 22 '16, 6:54 a.m.
 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 =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.

Thanks!



 

Be the first one to answer this question!


Register or to post your answer.