How to sum up a duration field

(0) Share

Report

Posted on by Community Member

Hi,

I'm trying to create a report to get the sum of a duration field. This is a custom field I added in the PhoneCall entity and when checking the FilteredPhoneCall table I noticed there are 2 columns related to this duration field. One column holds the value of the duration field(ex: 100000006) and the other columns holds the actual duration(ex: 45 minutes). How can I sum up these values to get the total duration? Please help.

Thank you.

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (2)

Suggested answer

Guido Preite 54,086 Moderator on at

Like (2)

Report

Duration is a format for the Whole Number type, so you deal with Int32 values (for Duration are non negative)

The value is always considered in minutes, so if you want to put 3 hours you need to set the field value to 180 (60 minutes x 3 hours), 1 day is 1440 (60 minutes x 24 hours) and so on.

By interface you can set using decimals, but it's always a representation of an integer value (for example 1.5 hours equals 90 minutes)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Guido,

Thanks a lot for the reply. Could you please tell me how to do that conversion in BI?

Regards

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report

If you are using the fetchxml to generate report you will see 2 fields for duration, one with formated value like 45 minutes other one with raw value where it will give you value in integer format. Take ActualDurationMinutesValue field to sum the values.

Check the below post it gives more info on the same. t shows example for currency field

www.powerobjects.com/.../crm-2011-currency-fields-fetchxml-reports

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Chitra,

Thanks a lot for the reply. I'm using SQL Server to generate the report & looks like the only column I might be able to use is the one with the formatted value, like 45 minutes. I'm not sure if the other column which holds the value of the selected option(ex:100000006) would be of any help. It would be great if you can help me to figure this out.

Regards

Lavanya

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report

I just did the quick report based on the phone call entity using Fetchxml as well sql query, in both the actualdurationminutes field always gives integer value like 120 for 2 hours. Can you verify whether you are referring right field name in the query?

Here is my results

select subject,actualdurationminutes,scheduledstart from FilteredPhoneCall

Fetchxml results :

Was this reply helpful? Yes No
Verified answer

Aileen Gusni 44,524 on at

Like (0)

Report

Hi Lavanya,

I don't see any OptionSet of duration field through database, and by its value: 100000006, it seems a custom Option Set field, a new field?

You can use this query:

select actualdurationminutes from filteredPhoneCall

Then for SSRS report in BIDS

You can use this expression:

=Sum(Fields!actualdurationminutes.Value, "DataSet1")

Or you can have this to get the minutes

=Sum(Fields!actualdurationminutes.Value, "DataSet1") & " minutes"

Hope this helps!

Thanks.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

This is a custom duration field I added to the PhoneCall entity. I have to get the total duration based on this custom field not the actualdurationminutes I'm afraid. Is there anyway to achieve this?

Thanks a lot for trying to help.

Regards

Was this reply helpful? Yes No
Verified answer

Royal King 27,686 on at

Like (0)

Report
If it's a picklist value , you need to use case statement in your query to map each optionset value into corresponding integer value.Something like below.I mapped optionset values 1,2 and 3 to 30,60 and 75 minutes

SELECT actualdurationiinminutes,Sum( Category = CASE YourFieldName WHEN 1 THEN 30 WHEN 2 THEN 60 WHEN 3 THEN 75 ELSE 'N0 END), subject FROM FilteredPhoneCall

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks a lot Chitra & Aileen for your answers. Actually, I have overlooked this duration field issue. I did create a whole number "duration" field but another user has added a picklist to this entity and that confused me.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi

I am dealing with alike issue

In the report i use the duration field with sum aggregration but i need the total not in minutes but in hours .. 90 minutes i want to see 1.5 ..

anyway to do so ?

Thanks in advance

Maya

Was this reply helpful? Yes No