vw_task_list

Overview

This view sums various data for each task.

It can be used to analyse*;-

▪the total amount of time booked against a task

▪the cost of this time (as a resource cost)

▪Current WIP and invoiced values (also split by Fees, Time and Expenses)

* this list is not exclusive.

 

Column Definitions

 

Columns

Comments

projecttaskid

This is a unqiue identifier for a combination of the project and taskuniqueid that can be joined directly to from any other views with the same projecttaskid column.

projectid

The unique project identifier. Join to the Projects table (or other views that have both the projectid and taskuniqueid columns)

taskuniqueid

The unique task identifier. Join to the Tasks table (or other views that have both the projectid and taskuniqueid columns)

billing_taskuniqueid

This is used for tasks that are set up to be billed under a different task. In these scenarios, the task taskuniqueid of the billing task is used.

task_id

This is the sort number of the task as it appears in the Task List. i.e. 1 = first task, 2 = second task etc.

Task Code

as name suggests

Taskname

as name suggests

Charge Method

The charge method of the task. This can be used to determine which element of work the time was booked against;-

 

T

Time based

T1

Timecharged under a different task

F

Fee Based

N

Non-chargeable

 

Service

as name suggests

Resource Group

as name suggests

Task Leader

as name suggests

Start Date

as name suggests

Finish Date

as name suggests

Actual Start Date

as name suggests

Actual Finish Date

as name suggests

Forecast This Year

These figures are only relevant if you run the Period End Routine.

The forecast is based on;-

 

Timecharges

If the charge of timecharges booked to the fee is greater than the estimate then the charge value is used, otherwise the estimate is used.

Fixed Fee (Scheduled)

Scheduled - the amount billed from the schedule.

Fixed Fee (%age Complete)

the %age is based on how far through the duration of the task you are (based on the task dates and the Effective Date).

 

Forecast Next Year

Forecast Year 2

Forecast Year 3

Forecast Year 4

Client Fee

This is the task's client fee in the base currency.

Currency Fee

This is the client fee in the task's currency.

Target Fee

as name suggests

Percentage Complete

as name suggests

Calc Percent Complete

This value is determined on how your system is configured to enter %age completions;-

 

By Hours Calc Percent Complete

100    *   Hours currently spent on the task    /    ( Hours currently spent on the task    +     Hours to complete the task  )

By Costs

100    *   Timesheet costs currently spent on the task    /    ( Timesheet costs currently spent on the task    +     Timesheet costs to complete the task  )

 

Percentage Fee Earned

If the task does not have a schedule this is the client fee based on the tasks %age completion.

Scheduled Fee Earned

If the task has a fee schedule, this is the value of the schedules up to the Last Costing Date.

Fee Discounts WIP

The discounted value of Fees currently in WIP for the task.

Fees Earned

If the task has a fee schedule, this is the value of the schedules up to the Last Costing Date.

Otherwise it is the client fee based on the tasks %age completion.

Hours To Date

The total number of hours booked to the task.

Hours This Year

The total number of hours booked to the task this financial year.

Hours In Progress

The total number of hours booked to the task that are still marked as 'In-Progress'.

Time Recharges To Date

The recharge value of all hours booked to the task.

Time Recharges This Year

The recharge value of all hours booked to the task this financial year.

Time Recharges In Progress

The recharge value of all hours booked to the task that are still marked as 'In-Progress'.

Time Costs To Date

The resource costs of all hours booked to the task.

Time Costs This Year

The resource costs of all hours booked to the task this financial year.

Time Costs In Progress

The resource costs of all hours booked to the task that are still marked as 'In-Progress'.

Expense Costs To Date

The expense costs of all expenses recorded to the task.

Expense Costs This_Year

The expense costs of all expenses recorded to the task this financial year.

Expense Costs In Progress

The expense costs of all expenses recorded to the task are still marked as 'Entered' (not approved).

Outsourced Costs To Date

The cost of all outsourced expenses recorded to the task up to the Last Costing Date.

Outsourced Costs This Year

The cost of all outsourced expenses recorded to the task this financial year.

Outsourced Costs In Progress

The cost of all outsourced expenses recorded to the task that are still marked as 'Entered' (not approved).

Timecharges Earned

The recharge value of all hours booked to the task.

Timecharges Earned This Year

The resource costs of all hours booked to the task this financial year.

Expenses Earned

The value of all chargeable expenses booked to the task.

Expenses Earned This Year

The value of all chargeable expenses booked to the task this financial year.

NC Expense Costs To Date

The cost of all non chargeable expenses recorded to the task up to the Last Costing Date.

NC Expense Costs This Year

The cost of all non chargeable expenses recorded to the task this financial year.

NC Expense Costs In Progress

The cost of all non chargeable expenses recorded to the task that are still marked as 'Entered' (not approved).

Fees Invoiced

The value of Fees stored against the task that are recorded against approved invoices.

Fees Invoiced This Year

The value of Fees stored against the task that are recorded against approved invoices this financial year.

Time Invoiced

The value of Timecharges stored against the task that are recorded against approved invoices.

Time Invoiced This Year

The value of Timecharges stored against the task that are recorded against approved invoices this financial year

Expenses Invoiced

The value of Expenses stored against the task that are recorded against approved invoices.

Expenses Invoiced This Year

The value of Expenses stored against the task that are recorded against approved invoices this financial year.

Fee Discounts Invoiced

The discounted value of Fees invoiced against the task.

Time Discounts Invoiced

The discounted value of Time invoiced against the task.

Time Discounts WIP

The discounted value of Time currently in WIP for the task.

Exps Discounts Invoiced

The discounted value of Expenses invoiced against the task.

Fee Discounts Inv This Year

The discounted value of Time invoiced against the task in the current financial year.

Time Discounts Inv This Year

The discounted value of Fees invoiced against the task in the current financial year.

Exps Discounts Inv This Year

The discounted value of Expenses invoiced against the task in the current financial year.

Fees WIP

The value of Fees in WIP stored against the task (based on the Effective Date set currently set in the system).

Timecharges WIP

The value of Timecharged in WIP stored against the task (based on the Effective Date set currently set in the system).

Expenses WIP

The value of Expenses in WIP stored against the task (based on the Effective Date currently set in the system).

Cost To Complete

Refer to Cost to Complete (explained)

Exps To Complete

The %age of the Cost To Complete (above) allocated to no chargeable expenses (as defines against the service) plus the cost of any project expenses after the Effective Date set currently set in the system.

Draft Fees Invoiced

The value of Fees stored against the task that are recorded against draft invoices.

Draft Time Invoiced

The value of Timecharges stored against the task that are recorded against draft invoices.

Draft Exps invoiced

The value of Expenses stored against the task that are recorded against draft invoices.

Invoiced Time Writeoffs

The value of Time written off after invoicing - NOTE: this time cannot be recovered.

WIP Time Writeoffs

The value of Time written off during WIP preperation - NOTE: this time can be recovered until the next time task in invoiced. At this stage it will be added to Invoiced Time Writeoffs

Fees Deferred

The value of Fees currently deferred against the task.

Timecharges Deferred

The value of Timecharges currently deferred against the task.

Expenses Deferred

The value of Expenses currently deferred against the task.

Outsourced Amount

The cost of all outsourced project purchases booked to the task.

Billingtask Time Costs To Date

The cost of all time booked to this task (or any other tasks that are to be billed under it (up to the Last Costing Date))

Billingtask Hours To Date

The number of hours booked to this task (or any other tasks that are to be billed under it (up to the Last Costing Date))

Whatif Fee Invoices

The value of Fees stored against the task that are recorded against Whatif invoices.

Whatif Time Invoices

The value of Timecharges stored against the task that are recorded against Whatif invoices.

Whatif Exp Invoices

The value of Expenses stored against the task that are recorded against Whatif invoices.

Resource Plan Cost To Complete

The total resource costs of all resources assigned to the task on the resource plan (after the Effective Date currently set in the system).

Resource Plan Hours To Complete

The total resourced hours of all resources assigned to the task on the resource plan (after the Effective Date currently set in the system).

Resource Plan Cost

The total resource costs of all resources assigned to the task on the resource plan.

Resource Plan Hours

The total resourced hours of all resources assigned to the task on the resource plan.

 

 

How to use this view

This view can be executed through any third party software you're using to connect to the Timemaster database (i.e. Microsoft Excel**, Microsoft PowerBI**, Microsoft SQL Management Studio etc.) providing it support the use of T-SQL.

 

It can also be executed directly from within Timemaster - Please refer to Executing SQL Scripts if you're unsure how to execute scripts from within Timemaster.

 

** These options can be used to create a live feed where the data can be refreshed within the software. Other options will create a static snapshot of the current data.

 

Click here for example syntax.

Basic Example;-

select

 projecttaskid,

 projectid,

 taskuniqueid,

 billing_taskuniqueid,

 task_id,

 [Task Code],

 Taskname,

 [Charge Method],

 Service,

 [Resource Group],

 [Task Leader],

 [Start Date],

 [Finish Date],

 [Actual Start Date],

 [Actual Finish Date],

 [Forecast This Year],

 [Forecast Next Year],

 [Forecast Year 2],

 [Forecast Year 3],

 [Forecast Year 4],

 [Client Fee],

 [Currency Fee],

 [Target Fee],

 [Percentage Complete],

 [Calc Percent Complete],

 [Percentage Fee Earned],

 [Scheduled Fee Earned],

 [Fee Discounts WIP],

 [Fees Earned],

 [Hours To Date],

 [Hours This Year],

 [Hours In Progress],

 [Time Recharges To Date],

 [Time Recharges This Year],

 [Time Recharges In Progress],

 [Time Costs To Date],

 [Time Costs This Year],

 [Time Costs In Progress],

 [Expense Costs To Date],

 [Expense Costs This_Year],

 [Expense Costs In Progress],

 [Outsourced Costs To Date],

 [Outsourced Costs This Year],

 [Outsourced Costs In Progress],

 [Timecharges Earned],

 [Timecharges Earned This Year],

 [Expenses Earned],

 [Expenses Earned This Year],

 [NC Expense Costs To Date],

 [NC Expense Costs This Year],

 [NC Expense Costs In Progress],

 [Fees Invoiced],

 [Fees Invoiced This Year],

 [Time Invoiced],

 [Time Invoiced This Year],

 [Expenses Invoiced],

 [Expenses Invoiced This Year],

 [Fee Discounts Invoiced],

 [Time Discounts Invoiced],

 [Time Discounts WIP],

 [Exps Discounts Invoiced],

 [Fee Discounts Inv This Year],

 [Time Discounts Inv This Year],

 [Exps Discounts Inv This Year],

 [Fees WIP],

 [Timecharges WIP],

 [Expenses WIP],

 [Cost To Complete],

 [Exps To Complete],

 [Draft Fees Invoiced],

 [Draft Time Invoiced],

 [Draft Exps invoiced],

 [Invoiced Time Writeoffs],

 [WIP Time Writeoffs],

 [Fees Deferred],

 [Timecharges Deferred],

 [Expenses Deferred],

 [Outsourced Amount],

 [Billingtask Time Costs To Date],

 [Billingtask Hours To Date],

 [Whatif Fee Invoices],

 [Whatif Time Invoices],

 [Whatif Exp Invoices],

 [Resource Plan Cost To Complete],

 [Resource Plan Hours To Complete],

 [Resource Plan Cost],

 [Resource Plan Hours] 

from 

 vw_tasks_list

 

 

This lists all columns in the view. Columns can be removed as necessary. The last column name should not have a comma separator at the end of it.

 

 

Other Examples;-

The following are some examples how the data can be manipulated;-

 

Summarise the task values at a project level.

 

select

 projectid,

 Sum([Forecast This Year])                as [Forecast This Year],        

 Sum([Forecast Next Year])                as [Forecast Next Year],

 Sum([Forecast Year 2])                        as [Forecast Year 2],

 Sum([Forecast Year 3])                        as [Forecast Year 3],

 Sum([Forecast Year 4])                        as [Forecast Year 4],

 Sum([Client Fee])                        as [Client Fee],

 Sum([Currency Fee])                        as [Currency Fee],

 Sum([Target Fee])                        as [Target Fee],

 Sum([Percentage Fee Earned])                as [Percentage Fee Earned],

 Sum([Scheduled Fee Earned])                as [Scheduled Fee Earned],

 Sum([Fee Discounts WIP])                as [Fee Discounts WIP],

 Sum([Fees Earned])                        as [Fees Earned],

 Sum([Hours To Date])                        as [Hours To Date],

 Sum([Hours This Year])                        as [Hours This Year],

 Sum([Hours In Progress])                as [Hours In Progress],

 Sum([Time Recharges To Date])                as [Time Recharges To Date],

 Sum([Time Recharges This Year])        as [Time Recharges This Year],

 Sum([Time Recharges In Progress])        as [Time Recharges In Progress],

 Sum([Time Costs To Date])                as [Time Costs To Date],

 Sum([Time Costs This Year])                as [Time Costs This Year],

 Sum([Time Costs In Progress])                as [Time Costs In Progress],

 Sum([Expense Costs To Date])                as [Expense Costs In Progress],

 Sum([Expense Costs This_Year])                as [Expense Costs This_Year],

 Sum([Expense Costs In Progress])        as [Expense Costs In Progress],

 Sum([Outsourced Costs To Date])        as [Outsourced Costs To Date],

 Sum([Outsourced Costs This Year])        as [Outsourced Costs This Year],

 Sum([Outsourced Costs In Progress])        as [Outsourced Costs In Progress],

 Sum([Timecharges Earned])                as [Timecharges Earned],

 Sum([Timecharges Earned This Year])        as [Timecharges Earned This Year],

 Sum([Expenses Earned])                        as [Expenses Earned],

 Sum([Expenses Earned This Year])        as [Expenses Earned This Year],

 Sum([NC Expense Costs To Date])        as [NC Expense Costs To Date],

 Sum([NC Expense Costs This Year])        as [NC Expense Costs This Year],

 Sum([NC Expense Costs In Progress])        as [NC Expense Costs In Progress],

 Sum([Fees Invoiced])                        as [Fees Invoiced],

 Sum([Fees Invoiced This Year])                as [Fees Invoiced This Year],

 Sum([Time Invoiced])                        as [Time Invoiced],

 Sum([Time Invoiced This Year])                as [Time Invoiced This Year],

 Sum([Expenses Invoiced])                as [Expenses Invoiced],

 Sum([Expenses Invoiced This Year])        as [Expenses Invoiced This Year],

 Sum([Fee Discounts Invoiced])                as [Fee Discounts Invoiced],

 Sum([Time Discounts Invoiced])                as [Time Discounts Invoiced],

 Sum([Time Discounts WIP])                as [Time Discounts WIP],

 Sum([Exps Discounts Invoiced])                as [Exps Discounts Invoiced],

 Sum([Fee Discounts Inv This Year])        as [Fee Discounts Inv This Year],

 Sum([Time Discounts Inv This Year])        as [Time Discounts Inv This Year],

 Sum([Exps Discounts Inv This Year])        as [Exps Discounts Inv This Year],

 Sum([Fees WIP])                                as [Fees WIP],

 Sum([Timecharges WIP])                        as [Timecharges WIP],

 Sum([Expenses WIP])                        as [Expenses WIP],

 Sum([Cost To Complete])                as [Cost To Complete],

 Sum([Exps To Complete])                as [Exps To Complete],

 Sum([Draft Fees Invoiced])                as [Draft Fees Invoiced],

 Sum([Draft Time Invoiced])                as [Draft Time Invoiced],

 Sum([Draft Exps invoiced])                as [Draft Exps invoiced],

 Sum([Invoiced Time Writeoffs])                as [Invoiced Time Writeoffs],

 Sum([WIP Time Writeoffs])                as [WIP Time Writeoffs],

 Sum([Fees Deferred])                        as [Fees Deferred],

 Sum([Timecharges Deferred])                as [Timecharges Deferred],

 Sum([Expenses Deferred])                as [Expenses Deferred],

 Sum([Outsourced Amount])                as [Outsourced Amount],

 Sum([Billingtask Time Costs To Date])        as [Billingtask Time Costs To Date],

 Sum([Billingtask Hours To Date])        as [Billingtask Hours To Date],

 Sum([Whatif Fee Invoices])                as [Whatif Fee Invoices],

 Sum([Whatif Time Invoices])                as [Whatif Time Invoices],

 Sum([Whatif Exp Invoices])                as [Whatif Exp Invoices],

 Sum([Resource Plan Cost To Complete])        as [Resource Plan Cost To Complete],

 Sum([Resource Plan Hours To Complete]) as [Resource Plan Hours To Complete],

 Sum([Resource Plan Cost])                as [Resource Plan Cost],

 Sum([Resource Plan Hours])                as [Resource Plan Hours]

from 

 vw_tasks_list

group by

 projectid

 

Any images shown may be representative of the previous version of Timemaster

Report a problem

Please report any errors on this page to timemastersupport@equisys.com