I have a need to create a report displaying a duration data field but only listing the number of days (instead of days, weeks, months, etc.) and rounding up any duration value less than one day to display as one day (so 1 day 1 hour would display as 2 days).  Does anyone have any insight on best way to go about this?  Does anyone know of any existing configuration examples I could reference in OOB TRIRIGA or even online somewhere where a duration data field is converted to report the number of days only?

P.S. If you are curious the specific duration field I need to convert for our business users is the triActualDU field in triTask / triScheduleTask.

------------------------------
Mike A.
------------------------------

#TRIRIGA
#AssetandFacilitiesManagement

You are going to need to create another field and use that field for the reporting instead of the duration field. I'd recommend adding a new number field, set a default value of 0, and set the round rule to up. Make the number field a formula field and set it to be an extended formula. Then it is just a matter of building a formula to calculate the number of days in the duration field. The duration is stored in the database as long value, so it is just a number which means we can use math against it.

Here is the basic format of how a duration field is calculated:

So, what you need to do is take the duration field which is stored in milliseconds and calculate the number of days based on that. The formula is going to get rather complicated as you pull out the various parts and convert them to days.

Good luck
--Mark


------------------------------
Mark Johnson
Senior Architect
Wipro
------------------------------

Mark Johnson, thanks so much for providing this information on how duration fields are calculated in TRIRIGA.  That seems so bizarre that TRIRIGA would add that 100,000,000,000,000 value into the equation only if the duration value is longer than 1 month.  And I'm not sure that you could remove it simply with a formula, I think you would need to use some if statement workflow logic to separate that out.  But I'm not going to mess with that because I think I've found an easier solution.  The triActualDU value that I'm after on the Schedule Task records is calculated by subtracting the Actual Start Date from the Actual End Date values.  What I found is if I create two new formula fields to convert those two date values into numbers, and then create a third formula field to subtract the Actual Start Date number from the Actual End Date number, I will then get a value representing the number of milliseconds without TRIRIGA adding that 100,000,000,000 value into the mix if the duration is longer than one month.  I can then divide by 86400000 to get the number of days value that I am looking for.  Then all I need to do is create a workflow to update the Actual Start Date and Actual End Date values on the existing Schedule Task records to get these formula fields populated with values for the report.

------------------------------
Mike A.
------------------------------

Original Message:
Sent: Tue September 20, 2022 08:42 AM
From: Mark Johnson
Subject: Question regarding converting duration data fields

You are going to need to create another field and use that field for the reporting instead of the duration field. I'd recommend adding a new number field, set a default value of 0, and set the round rule to up. Make the number field a formula field and set it to be an extended formula. Then it is just a matter of building a formula to calculate the number of days in the duration field. The duration is stored in the database as long value, so it is just a number which means we can use math against it.

Here is the basic format of how a duration field is calculated:

(100000000000000  *  ((years  *  12)  +  months))  + (weeks  *  604800000)  +  (days  *  86400000)  +  (hours * 3600000) + (minutes * 60000) + (seconds * 1000) + milliseconds

 A year is equal to 365 days in milliseconds. A month is equal to 365 days in milliseconds divided by 12.

So, what you need to do is take the duration field which is stored in milliseconds and calculate the number of days based on that. The formula is going to get rather complicated as you pull out the various parts and convert them to days.

Good luck
--Mark


------------------------------
Mark Johnson
Senior Architect
Wipro
------------------------------

Hi Mike,

Any chance you have the Kurve (aka 'TRIRIGA Reporting') reporting tool?

If so, this can be achieved using Custom Columns in the report. You are able to convert the duration field into other formats (i.e. seconds, minutes, days, weeks, months, years) using two functions. First, you would use the ConvertTRIDuration function which will convert the duration into milliseconds, and then you'd use the ConvertDuration function to convert it into your desired format, i.e. days.

Feel free to reach out to me (lauren.rochon@tjene.com) if you're interested in learning more about our reporting tool and how it can help your need.

Best,
Lauren

------------------------------
Lauren Rochon
Tjene
------------------------------