Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

how to convert time from second to Hours in report

Questions
Tags
Users
Badges
Naveen Tyagi
(197●78●152) edited
Jan 15 '16, 5:41 a.m.
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.

0 votes

2 answers
2,952 views
0 votes

Accepted answer

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

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

0 votes

Comments
Tarik Mahfoudi
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
FORUM MODERATOR / JAZZ DEVELOPER May 04 '17, 4:37 p.m.

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

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

 JRS 5.0.2 and CLM 5.0.2

Tarik Mahfoudi
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

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

0 votes

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.

Ask a question
Guidelines
FAQ
Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details
× 481

Question asked: Jan 15 '16, 5:33 a.m.

Question was seen: 2,952 times

Last updated: May 08 '17, 3:42 p.m.

Confirmation Cancel Confirm