It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Guidelines | FAQ
Follow this question
Question details

×42,862
×481
×197

question asked: Jan 15 '16, 5:33 a.m.
question was seen: 2,711 times
last updated: May 08 '17, 3:42 p.m.

Related questions

how to convert time from second to Hours in report

0
Naveen Tyagi (197●78●152) | asked Jan 15 '16, 5:33 a.m.
edited Jan 15 '16, 5:41 a.m. by Ralph Schoon (63.4k●3●36●46)
I am getting time(Actual/Planned ) values in Seconds in report. is there any way to convert it into hours ? I'm using report builder. Thanks in Advance.

Accepted answer

0
permanent link
Rosa Naranjo (2.9k●1●17●23) | answered May 04 '17, 5:54 p.m.
FORUM MODERATOR / JAZZ DEVELOPER

How about like this?  DOUBLE(T1.ACTUAL_WORK/3600) and DOUBLE(T1.PLANNED_WORK/3600)

Reference:
https://jazz.net/forum/questions/238015/jrs-report-builder-v602-db2-sql-percentage-conversion-of-actual_work-divided-by-planned_work-does-not-work
https://jazz.net/forum/questions/235705/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

Rosa Naranjo selected this answer as the correct answer
Comments
Tarik Mahfoudi commented May 04 '17, 4:30 p.m. | edited May 08 '17, 3:42 p.m.

 I'm trying to do the same thing, and I've not been able to get the calculations to work properly.   Any help on this would be greatly appreciated.  The bold "actual work and planned work" is what I need to divide by 3600 to turn into hours.  


SELECT DISTINCT T1.REFERENCE_ID,
       T2.FULL_NAME,
       T1.NAME,
       T1.REQUEST_TYPE,
       T1.CREATION_DATE,
       T1.DUE_DATE,
       T1.REQUEST_CATEGORY_NAME,
       T1.ITERATION_NAME,
       T1.FOUND_IN_RELEASE_NAME,
       T1.ACTUAL_WORK,
       T1.PLANNED_WORK,
       T1.REQUEST_STATE,
       T1.REC_DATETIME
FROM RIDW.VW_REQUEST T1
INNER JOIN RIDW.VW_RESOURCE T2
ON T2.RESOURCE_ID=T1.OWNER_ID
WHERE T1.PROJECT_ID = 11  AND
(  T1.REQUEST_TYPE = 'Change Request' AND
  (T1.REQUEST_STATE = 'Analysis' OR T1.REQUEST_STATE = 'Approved' OR T1.REQUEST_STATE = 'COR Approval' OR T1.REQUEST_STATE = 'Closed' OR T1.REQUEST_STATE = 'Completed' OR T1.REQUEST_STATE = 'In Progress' OR T1.REQUEST_STATE = 'New' OR T1.REQUEST_STATE = 'Verified' ) AND
  DAYS(CURRENT_TIMESTAMP) - DAYS(T1.REC_DATETIME) <= 7 
) AND
(T1.ISSOFTDELETED = 0) ORDER BY T2.FULL_NAME asc

Rosa Naranjo commented May 04 '17, 4:37 p.m.
FORUM MODERATOR / JAZZ DEVELOPER

Hello
What version of ReportBuilder and CLM are we talking about here?

Tarik Mahfoudi commented May 04 '17, 5:01 p.m.

 JRS 5.0.2 and CLM 5.0.2

Tarik Mahfoudi commented May 05 '17, 12:23 p.m. | edited May 08 '17, 3:41 p.m.

This worked:

       INT(T1.ACTUAL_WORK/3600) as TimeSpent,
       INT(T1.PLANNED_WORK/3600) as Estimate,

One other answer

Most liked answers ↑|Newest answers|Oldest answers

0
permanent link
Rosa Naranjo (2.9k●1●17●23) | answered May 26 '16, 10:41 p.m.
FORUM MODERATOR / JAZZ DEVELOPER
For that you will have to drop down into the Advanced section of the report builder and edit the SQL for the report.

Your answer

Register or 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.