Query Utilization

Query the planned, available and actual utilization and cost of resources in a given time period.

Businesses have their own way of organizing resource utilization data and this function provides a flexible way to achive that. Although XML could have been used to organize the information hierarchically thereby reducing the size of the data by avoiding duplication, we have deliberately returned the data as 'rows'. We think that the best way to use this information is to create temporary SQL tables on your client side, insert the data returned into those tables and then use the power of SQL to query to generate suitable output.

Function name

query-utilization

Parameters

from. Date from which utilization is required.
to. Date till which utilization is required.
project_phase_id (Optional). Only include data for projects with these phase ids.
zoom (Optional). Groups utilization data by day, week or month (the default). Valid values: 5 for Day, 3 for Week and 2 for Month.
user_id (Optional). You can mention the user ID, email address or name here.

Even if a task is not planned in that duration, but the resource has filled time-entry or expense for the task in that period, then the task will appear in the return values.
If to and from values are not specified, then the return values are fetched for the current month.

Return values

In case of XML data format, you will be returned an XML string with two elements: count (the number of rows) and rows (this will contain multiple row elements). For instance,

<result>
  <rows>
    <row>
      <f>
        <v>62237</v><!--ID of the resource-->
        <n>resource_id</n>
      </f>
      <f>
        <v>Martha Young</v> <!-- Name of the resource-->
        <n>resource_name</n>
      </f>
      <f>
        <v>3502553</v><!-- ID of the task-->
        <n>task_id</n>
      </f>
      <f>
        <v>Start production of commercial product quantities</v> <!-- Name of the task-->
        <n>task_name</n>
     </f>
     <f>
       <v>35</v> <!-- %Complete of the task-->
       <n>task_pc</n>
     </f>
     <f>
       <v>86780</v> <!-- ID of the project-->
       <n>project_id</n>
     </f>
     <f>
       <v>Build v89.986 Register</v> <!-- Name of the project-->
       <n>project_name</n>
     </f>
     <f>
       <v>0</v> <!-- % Complete of the project-->
       <n>project_pc</n>
     </f>
     <f>
       <v>In Progress</v> <!-- Phase of the project-->
       <n>project_phase_name</n>
     </f>
     <f>
       <v>Risk Management</v> <!-- Category of the project-->
       <n>project_category_name</n>
     </f>
     <f>
       <v>DAGS</v> <!-- Client of the Project-->
       <n>client_root_name</n>
     </f>
     <f>
       <v>12690</v> <!-- Available minutes for this resource in this period taking into account holidays and vacations-->
       <n>availability</n>
     </f>
     <f>
       <v>480</v> <!-- Planned minutes for this task for this period for this resource -->
       <n>planned</n>
     </f>
     <f>
       <v>2000</v><!--Estimated billing for this task for this period for this resource-->
       <n>estimated_labour_billing</n>
     </f>
     <f>
       <v>600</v><!--Estimated labor cost for this task for this period for this resource-->
       <n>estimated_labour_cost</n>
     </f>
     <f>
       <v>2013-01-01T00:00:00</v> <!--start date of the period--><--This is important when the Zoom is day so that you see the planned minutes on the task for that date-->
       <n>period_start</n>
     </f>
     <f>
       <v>65</v> <!-- Actual minutes for this task for this period for this resource-->
       <n>actual</n>
     </f>
     <f>
       <v>650</v> <!-- Actual cost for this task for this period for this resource-->
       <n>actual_time_cost</n>
     </f>
     <f>
       <v>0</v> <!-- Actual billable amount for this task for this period for this resource-->
       <n>actual_time_billable_amount</n>
     </f>
     <f>
       <v>0</v><!-- Actual expense amount for this task for this period for this resource-->
       <n>actual_expense_cost</n>
     </f>
     <f>
       <v>0</v><!-- Actual billable expense amount for this task for this period for this resource-->
       <n>actual_expense_billable_amount</n>
     </f>
     </row>
  </rows>
</result>

In case of JSON, it will return an object will have the property rows (which will be an array of objects with n and v properties respectively)

{
 "rows":
   [
    {
     "row":[{"f":{"v":"91199","n":"resource_id"}}
            {"f":{"v":"Manasi (Mike)","n":"resource_name"}},
            {"f":{"v":"3793302","n":"task_id"}},
            {"f":{"v":"1","n":"task_name"}},
            {"f":{"v":"100","n":"task_pc"}},
            {"f":{"v":"134008","n":"project_id"}},
            {"f":{"v":"Build v89.986 Register","n":"project_name"}},
            {"f":{"v":"3","n":"project_pc"}},
            {"f":{"v":"In Progress","n":"project_phase_name"}},
            {"f":{"v":"Risk Managment","n":"project_category_name"}},
            {"f":{"v":"DAGS","n":"client_root_name"}},
            {"f":{"v":"14880","n":"availability"}},
            {"f":{"v":"0","n":"planned"}},
            {"f":{"v":"2013-10-01T00:00:00","n":"period_start"}},
            {"f":{"v":"0","n":"actual"}},
            {"f":{"v":"0","n":"actual_time_cost"}},
            {"f":{"v":"0","n":"actual_time_billable_amount"}},
            {"f":{"v":"0","n":"actual_expense_cost"}},
            {"f":{"v":"0","n":"actual_expense_billable_amount"}}  
   
           ]
    }
    ....
   ] 
}