How to sum up a duration field

(0) Share

Report

Posted on by Community Member Microsoft Employee

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

All responses (11)

Answers (2)

Community Member Microsoft Employee on at

Like (0)

Report

RE: How to sum up a duration field

Hi guys

Configuring SLA Timers in Dynamics CRM

Is there a way to save the timer value in CRM so that i know how much time it took to resolve a Case.

Eg if a case come on friday eve at 4 30 and get resolved at Monday morning at 10 am. Assuming our office hours is from 9 to 5 pm. Can i get a time to count 90 mins and save that in crm for that case. I know we can assign business hours to SLA entities and start times depending upon its sucess condition but still couldn't figure out how to grab value of timer and save it.
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to sum up a duration field

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
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to sum up a duration field

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.
Verified answer

Royal King 27,686 on at

Like (0)

Report
RE: How to sum up a duration field

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
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to sum up a duration field

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
Verified answer

Aileen Gusni 44,522 on at

Like (0)

Report

RE: How to sum up a duration field

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.
Royal King 27,686 on at

Like (0)

Report

RE: How to sum up a duration field

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 :
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to sum up a duration field

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
Royal King 27,686 on at

Like (0)

Report

RE: How to sum up a duration field

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
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to sum up a duration field

Hi Guido,

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

Regards