How would I add a Column to my Report Builder Report that gives me the percentage of Time Spent (Actual Work) to my Estimate (Planned Work)

Richard Kissel (30●6●17) | asked Feb 22 '17, 9:34 p.m.

I am using Report Builder version 6.0.2

My SQL Query is below.

Sorry Just learning SQL...Thanks For Your Help

How would I add to my SQL Query to add a Percentage Column in my Report?

SELECT DISTINCT T2_1.LITERAL_NAME,
       T1.REFERENCE_ID,
       T1.NAME AS URL1_title,
       T1.URL AS URL1,
       T1.ACTUAL_WORK,
       T1.PLANNED_WORK
FROM RIDW.VW_REQUEST T1
LEFT OUTER JOIN RICALM.VW_RQST_STRING_M_EXT T2
ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='complexity'
LEFT OUTER JOIN RICALM.REQUEST_ATTRDEF T2_2
ON T2_2.REQUEST_TYPE_ID=T1.REQUEST_TYPE_ID AND T2_2.NAME=T2.NAME
    LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T2_1
    ON T2.VAL LIKE CONCAT(CONCAT('%|',T2_1.EXTERNAL_ID), '|%') AND T2_1.PROJECT_ID=T1.PROJECT_ID AND T2_1.ENUMERATION_NAME=T2_2.DATATYPE
WHERE T1.PROJECT_ID = 52 AND
( T1.REQUEST_TYPE = 'CLM Migration Inventory'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) ORDER BY T2_1.LITERAL_NAME asc

Accepted answer

permanent link

Lily Wang (4.9k●7●14) | answered Feb 23 '17, 9:56 p.m.

You can add a new column "((T1.ACTUAL_WORK100)/T1.PLANNED_WORK) AS PERCENTAGE". To make sure the aggregation works, you also need to add a condition that "Plan Work" is not zero.

The SQL query in my environment looks like:

---------------------------------------------------------

SELECT DISTINCT T1.PROJECT_NAME,

T1.REFERENCE_ID,

T1.NAME AS URL1_title,

T1.URL AS URL1,

T1.PLANNED_WORK,

T1.ACTUAL_WORK,

((T1.ACTUAL_WORK100)/T1.PLANNED_WORK) AS PERCENTAGE

FROM RIDW.VW_REQUEST T1

WHERE T1.PROJECT_ID = 8 AND

( T1.PLANNED_WORK <> 0

) AND

(T1.ISSOFTDELETED = 0) AND

(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)

Richard Kissel selected this answer as the correct answer

Comments

Richard Kissel commented Feb 24 '17, 11:39 a.m.

Thank You Lilly. After much Web Searching I actually worked something out for myself but I did not post my answer. Your answer appears Spot on and for That I thank You Gratefully and mark your answer as the Accepted Answer. This is what I came up with before I left work. Do you see anything Wrong? I'm not sure why you added ( T1.PLANNED_WORK <> 0).

SELECT DISTINCT
       T2_1.LITERAL_NAME,
       T1.REFERENCE_ID,
       T1.NAME AS URL1_title,
       T1.URL AS URL1,
       INT(T1.ACTUAL_WORK/3600) AS TimeSpent,
       INT(T1.PLANNED_WORK/3600) AS Estimate,
       CASE WHEN INT(T1.ACTUAL_WORK) > 0
            THEN (INT(T1.PLANNED_WORK)/INT(T1.ACTUAL_WORK))
            ELSE 0 END as Percentage
FROM RIDW.VW_REQUEST T1

Lily Wang commented Feb 26 '17, 8:34 p.m.

I'm glad to know you have find solution yourself.

As in my sql query I used "T1.planned_work" as divisor so I added “T1.PLANNED_WORK <> 0" to avoid error.

Your answer

Register or log in to post your answer.

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.

It's all about the answers!

Related questions

How would I add a Column to my Report Builder Report that gives me the percentage of Time Spent (Actual Work) to my Estimate (Planned Work)

Accepted answer

Your answer