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
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.
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
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.
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
Share
Report
All responses (
Answers (
SELECT actualdurationiinminutes,Sum( Category =
CASE YourFieldName
WHEN 1 THEN 30
WHEN 2 THEN 60
WHEN 3 THEN 75
ELSE 'N0
END),
subject
FROM FilteredPhoneCall
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
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.
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 :
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
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
Hi Guido,
Thanks a lot for the reply. Could you please tell me how to do that conversion in BI?
Regards
#1
André Arnaud de Cal...
294,261
Super User 2025 Season 1
#2
Martin Dráb
233,011
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
