locked
CRM 2011 Case resolution update billedserviceunits RRS feed

  • Question

  • Hi All.

    Running CRM 2011 and have just built a BIDs report to show the details of a case and it's resolution.

    We need to show on the report how much time was spent on a case (we use tasks to keep track of time and total is calculated when the case is resolved).

    I believe I may need to use the 'billedserviceunits' field and have this field updated from the case closure activity however I cannot see how to do that through a workflow.

    Does anyone have any idea?

    Thanks


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk

    Saturday, May 12, 2012 11:05 PM

Answers

  • Hi David,

    The total time invested is being stored in the Case resolution and not in the Case. And there is a 1:N relation between case and case resolution.

    You can design this custom report and write a query to pick the total time from related case resolution and display it in your report. So basically you can get this field 'Billable Time' from your case resolution entity. If you go to your case resolution entity, you will find that field there.

    Another possible solution requiring development would be to add a custom field to the case and upon resolution have a workflow or a plugin copy it over to the case record. You can then include this column in the report. This custom field can be a new field you can create and put in on your case form.

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.


    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Sunday, May 13, 2012 10:55 AM
  • Hi David,

    That field is stored in the Case Resolution entity. So you will need to join the Case and Case Resolution entity.

    Since there is a 1:N relationship between Case and Case Resolution, you might have more than 1 case resolution (as case can be closed and reopened multiple times). It's up to your business rules to only pick up the latest one or all of them.

    If you only need the latest one, you can just join the Case and Case Resolution entity through the use of link-entity.

    To help you build your fetch xml query, you can use a tool called Stunnware Tools which can be downloaded from http://www.stunnware.com/products/tools4/downloads/SwTools4.5.32.zip (It's free)

    Hope this helps.


    Dimaz Pramudya - CRM Developer - CSG (Melbourne) www.xrmbits.com http://twitter.com/xrmbits

    Sunday, May 13, 2012 11:02 AM

All replies

  • Hi David,

    "Billed Service Units" is out of the box field. It is not added to the case form by default and hence not available for use in workflow.

    Please go to CRM Settings and then Case customization, open the Case form. Then add that field "Billed Service Units" on the form. That field can be seen in the Field Explorer list of fields on the right hand side. Add it to the form.

    Save and publish your Case form changes. You can now go to your workflow and use that field (that field will now be available in the workflow).

    I have also added a screenshot for your convenience (adding that field on the Case form).


    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.



    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Saturday, May 12, 2012 11:45 PM
  • Hi Ashish,

    Not quite on this one, I understand that it is a field not added to the form out-the-box and how to go about adding it.

    This question ties in with the last one you answered about creating a BIDs report, we need to run the report after the case has been closed so that the report shows the amount of time spent on the case (this is calculated from the tasks we create on the case resolution activity).

    I need to know how to get the value from the 'Billable Time' field on the 'Case Resolution' either on to the report that I am building directly from that entity or to populate the 'BilledServiceUnits' field on the case form and then I can add it to the report.

    Hoping someone can help with this.

    Thanks


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk

    Sunday, May 13, 2012 8:47 AM
  • Hi David,

    I am assuming from this and your last post, you are trying to create a report in BIDS and using Fecth XML. If that is the case, then you should be able to add the field 'BilledServiceUnits' on the report as part of the Fetch XML.

    Can you please let me know your question again. If possible can you please post some screenshots. This will help myself or other understand and answer your issue.

    Thanks.


    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Sunday, May 13, 2012 9:40 AM
  • Hi Ashish,

    Yes you are correct I am building the report using BIDS and Fetch XML.

    The issue I have is populating the 'BilledServiceUnits' field, currently as you know when you add activities you input the time spent and when finished 'Mark as Complete'. When you click the 'Resolve Case' button you get a box to enter the resolution and details. There is a field on that form called 'Billable Time', see screen shot below.

    I need that 'Billable Time' field to show on the BIDS report or to populate the 'BillableServiceUnits' from that field. Alternative update the 'BillableServiceUnits' by adding up the total time spent on each task in the same way the 'Billable Time' field is populated.

    Thanks


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk

    Sunday, May 13, 2012 10:42 AM
  • Hi David,

    The field you are after is called "timespent". This is a duration field with a data type of integer.

    CRM store the value of this field in minutes. So in the example that you have above, CRM will store the value as 15.

    Hope this helps.


    Dimaz Pramudya - CRM Developer - CSG (Melbourne) www.xrmbits.com http://twitter.com/xrmbits

    Sunday, May 13, 2012 10:51 AM
  • Hi David,

    The total time invested is being stored in the Case resolution and not in the Case. And there is a 1:N relation between case and case resolution.

    You can design this custom report and write a query to pick the total time from related case resolution and display it in your report. So basically you can get this field 'Billable Time' from your case resolution entity. If you go to your case resolution entity, you will find that field there.

    Another possible solution requiring development would be to add a custom field to the case and upon resolution have a workflow or a plugin copy it over to the case record. You can then include this column in the report. This custom field can be a new field you can create and put in on your case form.

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.


    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Sunday, May 13, 2012 10:55 AM
  • Hi Dimaz,

    Thanks for the input, do you know how to get that field to appear in a report designed in BIDS using Fetch XML?

    If that is not possible how to I create a field on the Case Form that does what the 'TimeSpent' field does on the 'Case Resolution' entity?

    Thanks


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk

    Sunday, May 13, 2012 10:57 AM
  • Hi David,

    That field is stored in the Case Resolution entity. So you will need to join the Case and Case Resolution entity.

    Since there is a 1:N relationship between Case and Case Resolution, you might have more than 1 case resolution (as case can be closed and reopened multiple times). It's up to your business rules to only pick up the latest one or all of them.

    If you only need the latest one, you can just join the Case and Case Resolution entity through the use of link-entity.

    To help you build your fetch xml query, you can use a tool called Stunnware Tools which can be downloaded from http://www.stunnware.com/products/tools4/downloads/SwTools4.5.32.zip (It's free)

    Hope this helps.


    Dimaz Pramudya - CRM Developer - CSG (Melbourne) www.xrmbits.com http://twitter.com/xrmbits

    Sunday, May 13, 2012 11:02 AM
  • Hi and thanks again,

    A little high level for my CRM experience, can you break it down any more?

    Regards


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk

    Sunday, May 13, 2012 11:06 AM
  • Hi,

    I have managed to work out the link entity stuff and used the program Dimaz recommended. Below is the Fetch XML created...

    <fetch mapping="logical" version="1.0">
    	<entity name="incident">
    		<attribute name="ticketnumber" />
    		<attribute name="incidentid" />
    		<attribute name="customerid" />
    		<attribute name="new_reportedby" />
    		<attribute name="ownerid" />
    		<attribute name="contractdetailid" />
    		<attribute name="contractid" />
    		<attribute name="createdon" />
    		<attribute name="billedserviceunits" />
    		<attribute name="actualserviceunits" />
    		<attribute name="new_casedetails" />
    		<attribute name="title" />
    		<attribute name="subjectid" />
    		<attribute name="new_subcategory" />
    		<attribute name="statuscode" />
    		<attribute name="statecode" />
    		<attribute name="severitycode" />
    		<attribute name="contractservicelevelcode" />
    		<attribute name="productserialnumber" />
    		<attribute name="customersatisfactioncode" />
    		<attribute name="new_sagenumber" />
    		<attribute name="responsiblecontactid" />
    		<attribute name="overriddencreatedon" />
    		<attribute name="productid" />
    		<attribute name="prioritycode" />
    		<attribute name="modifiedon" />
    		<attribute name="modifiedonbehalfby" />
    		<attribute name="modifiedby" />
    		<attribute name="followupby" />
    		<attribute name="exchangerate" />
    		<attribute name="description" />
    		<attribute name="isdecrementing" />
    		<attribute name="transactioncurrencyid" />
    		<attribute name="createdonbehalfby" />
    		<attribute name="createdby" />
    		<attribute name="new_contact" />
    		<attribute name="new_configurationitem" />
    		<attribute name="new_category" />
    		<attribute name="casetypecode" />
    		<attribute name="incidentstagecode" />
    		<attribute name="caseorigincode" />
    		<attribute name="new_assetid" />
    		<attribute name="new_2ndlinesupport" />
    		<link-entity name="account" from="accountid" to="customerid" alias="a_4b5945b8a4a64613afc1ae1d5e6828c7" link-type="outer">
    			<attribute name="address1_city" />
    			<attribute name="address1_county" />
    			<attribute name="address1_line1" />
    			<attribute name="address1_line2" />
    			<attribute name="address1_line3" />
    			<attribute name="address1_postalcode" />
    			<attribute name="address1_stateorprovince" />
    			<attribute name="address1_telephone1" />
    			<attribute name="telephone1" />
    		</link-entity>
    		<link-entity name="incidentresolution" from="incidentid" to="incidentid">
    			<all-attributes />
    		</link-entity>
    	</entity>
    </fetch>

    When I run the query, I dont see the 'timespent' in the results.

    Any ideas?


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk

    Sunday, May 13, 2012 11:58 AM
  • Hi David,

    What if you add the timespent in your query. Please run the below and see whether you get timespent?

    <fetch mapping="logical" version="1.0">
    	<entity name="incident">
    		<attribute name="ticketnumber" />
    		<attribute name="incidentid" />
    		<attribute name="customerid" />
    		<attribute name="new_reportedby" />
    		<attribute name="ownerid" />
    		<attribute name="contractdetailid" />
    		<attribute name="contractid" />
    		<attribute name="createdon" />
    		<attribute name="billedserviceunits" />
    		<attribute name="actualserviceunits" />
    		<attribute name="new_casedetails" />
    		<attribute name="title" />
    		<attribute name="subjectid" />
    		<attribute name="new_subcategory" />
    		<attribute name="statuscode" />
    		<attribute name="statecode" />
    		<attribute name="severitycode" />
    		<attribute name="contractservicelevelcode" />
    		<attribute name="productserialnumber" />
    		<attribute name="customersatisfactioncode" />
    		<attribute name="new_sagenumber" />
    		<attribute name="responsiblecontactid" />
    		<attribute name="overriddencreatedon" />
    		<attribute name="productid" />
    		<attribute name="prioritycode" />
    		<attribute name="modifiedon" />
    		<attribute name="modifiedonbehalfby" />
    		<attribute name="modifiedby" />
    		<attribute name="followupby" />
    		<attribute name="exchangerate" />
    		<attribute name="description" />
    		<attribute name="isdecrementing" />
    		<attribute name="transactioncurrencyid" />
    		<attribute name="createdonbehalfby" />
    		<attribute name="createdby" />
    		<attribute name="new_contact" />
    		<attribute name="new_configurationitem" />
    		<attribute name="new_category" />
    		<attribute name="casetypecode" />
    		<attribute name="incidentstagecode" />
    		<attribute name="caseorigincode" />
    		<attribute name="new_assetid" />
    		<attribute name="new_2ndlinesupport" />
    		<link-entity name="account" from="accountid" to="customerid" alias="a_4b5945b8a4a64613afc1ae1d5e6828c7" link-type="outer">
    			<attribute name="address1_city" />
    			<attribute name="address1_county" />
    			<attribute name="address1_line1" />
    			<attribute name="address1_line2" />
    			<attribute name="address1_line3" />
    			<attribute name="address1_postalcode" />
    			<attribute name="address1_stateorprovince" />
    			<attribute name="address1_telephone1" />
    			<attribute name="telephone1" />
    		</link-entity>
    		<link-entity name="incidentresolution" from="incidentid" to="incidentid">
    			<attribute name="timespent" />
    		</link-entity>
    	</entity>
    </fetch>
    I hope this helps. If my responses answered your question, please mark the responses as an answer and also vote them as helpful.


    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Sunday, May 13, 2012 12:07 PM
  • Hi David,

    What did you see on the result?

    Did you see the case resolution record appear on the Fetch XML Result? If the case resolution appeared, but the timespent value isn't there, it means the value for timespent field is NULL. By default, CRM will not include the NULL value in the Fetch XML Result.


    Dimaz Pramudya - CRM Developer - CSG (Melbourne) www.xrmbits.com http://twitter.com/xrmbits

    Sunday, May 13, 2012 12:12 PM
  • Hi,

    I tried adding just the attribute required to the XML and the field does show up to add to the BIDS report.

    When I run the XML query and view the result on the records that have a 'TimeSpent' entry I can see the data however when I run the report the data is not populated in the field!

    Regards


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk

    Sunday, May 13, 2012 12:47 PM
  • All,

    I fixed this by adding an alias to the link entity line in the XML code, as there was no fixed alias every time the query ran the alias changed resulting in no results being shown.

    Thanks for all your help, I have marked answers and submitted votes.


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk

    Sunday, May 13, 2012 7:13 PM