locked
Billing problems from the ticketing system RRS feed

  • Question

  • I am VERY new to CRM administration and got this job tasking by not being fast enough to say, "not it."  That being the case, I am trying to get the existing ticketing system to report our billable hours associated with each ticket. 

    Here is what I have thus far:
    I have created a report in Reporting Services using the following entities:
    IncidentResolution
    IncidentBase
    AccountBase

    I am using the "ActualDurationMinutes" as the "Billable Time" that we invoice off of.  Now here is my quandry.....   All the Tasks and Service Activities must tie into a "Billable Time" somewhere.  Here is what is killing me here:

    Is this the actual total amount of time considered billable once a case is resolved? 
    If it is, then why is the total "Billable Time" that comes up as a ticket is resolved the time in the History does not seem to match the time that comes up as the "Billable Time?"
    Lastly, am I using the right attribute and entity to pull the sum total of billable time associated with each ticket to include all phone calls, tasks, service activites, etc?

    I really appreciate any help on this.  I have 3 books and no SQL Reporting Services experience prior to this and have at least got 3 reports that seem like they are giving me what I want, but then again I can't prove whether it is all inclusive or not.

    wayne@inflowsys.com
    Thursday, November 8, 2007 7:32 PM

Answers

  • There are no particularly good resource for this. The Entity Model section of the CRM SDK (http://msdn2.microsoft.com/en-us/library/aa682857.aspx) is fairly good for the relatioships, and does at least give a line about each field (drill down the the class pages), but takes a bit of wading through. Once you know the entity structures I find it easiest to look at the data directly in SQL Server

     

    Friday, November 9, 2007 4:54 PM
    Moderator

All replies

  • When a case (ticket) is closed, the value put into the Billable Time field goes into the TimeSpent field of the IncidentResolution, not the ActualDurationMinutes.

     

    ActualDurationMinutes is used to total the time for all activities; you get this from ActivityPointer

     

    The following query is based on what we use, and shows the fields you need:

     

    select i.incidentid, i.title, ir.actualend
    , max(timespent) as BillableTime
    , isnull(sum(a.actualdurationminutes), 0) as TotalActivityTime
    from filteredactivitypointer a
    join filteredincident i
    on a.regardingobjectid = i.incidentid
    left outer join filteredincidentresolution ir
    on i.incidentid = ir.incidentid
    and ir.statecode = 1   -- Resolved incident resolutions
    and ir.actualend = (select max(actualend) from filteredincidentresolution ir2 where ir2.incidentid = ir.incidentid and ir2.statecode = 1) -- Most recent ir activity
    where i.statecode = 1   -- Resolved cases
    group by i.incidentid, i.title, ir.actualend

    Note I'm using the filtered views (e.g. filteredincident) as this is the supported way to access CRM data via SQL

    Friday, November 9, 2007 9:43 AM
    Moderator
  • Thank you very much for this help....

    Where is a good resource for finding which entities and attributes equal what information?  Is there such a thing?
    Friday, November 9, 2007 4:34 PM
  • There are no particularly good resource for this. The Entity Model section of the CRM SDK (http://msdn2.microsoft.com/en-us/library/aa682857.aspx) is fairly good for the relatioships, and does at least give a line about each field (drill down the the class pages), but takes a bit of wading through. Once you know the entity structures I find it easiest to look at the data directly in SQL Server

     

    Friday, November 9, 2007 4:54 PM
    Moderator
  • That is what I needed to know...... (for now)

    Thank you again....
    Friday, November 9, 2007 4:59 PM