Breakdown Hours

[Ashish kanoongo]

Hello

I want to create following view/reports

- What is the breakdown of the billable resources vs. non billable resources
?
- Out of the billable resources -
- How many are working on non billable tasks?
- What are those tasks?
Do I needd create CUBE or using existing cibe is this possible?

Ashish

 

[Jonathan Sofer]

The "Timesheet" cube has resource, task and project lists and work billable
and work non-billable but does not have any way of displaying which tasks or
resources are considered billable. How do you classify a task or a resource
as being billable or non-billable? If it is a custom field then it will not
be included in the "Timesheet" cube, only in the "MSP_Portfolio_Analyzer"
cube in which timesheets are not accessible. A custom report out of the
database would be an option using SQL Reporting Services for example.

Jonathan

 

[Ashish kanoongo]

Hello Jonathan

Thanks for the response. If I will go for reporting services, can you tell
which tables I need for this report?

Ashish

 

[Jonathan Sofer]

You will probably want to use the resource and project olap user views in
the reporting database and join them with the timesheet tables.

Try something like this:
SELECT MSP_EpmResource.ResourceName,
MSP_EpmProject_UserView.ProjectName, MSP_EpmResource.ResourceNTAccount,
MSP_EpmResource_UserView.RBS,
MSP_EpmResource.ResourceType, MSP_TimesheetPeriod.PeriodName,
MSP_TimesheetPeriodStatus.Description AS PeriodStatus,
MSP_TimesheetStatus.Description AS TimesheetStatus,
SUM(MSP_TimesheetActual.ActualWorkBillable) AS
Billable, SUM(MSP_TimesheetActual.ActualWorkNonBillable) AS NonBillable,
SUM(MSP_TimesheetActual.ActualOvertimeWorkBillable) AS
OvertimeBillable, SUM(MSP_TimesheetActual.ActualOvertimeWorkNonBillable)
AS OvertimeNonBillable, MSP_TimesheetPeriod.StartDate,
MSP_TimesheetPeriod.EndDate, SUM(MSP_TimesheetActual.PlannedWork) AS
Planned,
MSP_TimesheetStatus.TimesheetStatusID,
MSP_TimesheetPeriod.PeriodStatusID, MSP_Timesheet.TimesheetUID
FROM MSP_EpmResource_UserView INNER JOIN
MSP_EpmResource ON
MSP_EpmResource_UserView.ResourceUID = MSP_EpmResource.ResourceUID LEFT
OUTER JOIN
MSP_TimesheetStatus INNER JOIN
MSP_Timesheet ON MSP_TimesheetStatus.TimesheetStatusID
= MSP_Timesheet.TimesheetStatusID RIGHT OUTER JOIN
MSP_TimesheetResource ON
MSP_Timesheet.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID
ON
MSP_EpmResource.ResourceUID =
MSP_TimesheetResource.ResourceUID FULL OUTER JOIN
MSP_EpmProject_UserView INNER JOIN
MSP_TimesheetActual INNER JOIN
MSP_TimesheetTask INNER JOIN
MSP_TimesheetLine ON MSP_TimesheetTask.TaskNameUID =
MSP_TimesheetLine.TaskNameUID INNER JOIN
MSP_TimesheetProject ON
MSP_TimesheetLine.ProjectNameUID = MSP_TimesheetProject.ProjectNameUID ON
MSP_TimesheetActual.TimesheetLineUID =
MSP_TimesheetLine.TimesheetLineUID ON
MSP_EpmProject_UserView.ProjectUID =
MSP_TimesheetProject.ProjectUID ON
MSP_TimesheetResource.ResourceNameUID =
MSP_TimesheetActual.LastChangedResourceNameUID AND
MSP_Timesheet.TimesheetUID =
MSP_TimesheetLine.TimesheetUID FULL OUTER JOIN
MSP_TimesheetPeriod ON MSP_Timesheet.PeriodUID =
MSP_TimesheetPeriod.PeriodUID FULL OUTER JOIN
MSP_TimesheetPeriodStatus ON
MSP_TimesheetPeriod.PeriodStatusID =
MSP_TimesheetPeriodStatus.PeriodStatusID
WHERE (MSP_EpmResource.ResourceType = 2) AND
(MSP_EpmResource.ResourceIsActive = 1) AND
(MSP_EpmResource.ResourceIsGeneric = 0)
GROUP BY MSP_TimesheetPeriod.PeriodName, MSP_TimesheetPeriod.StartDate,
MSP_TimesheetPeriod.EndDate, MSP_TimesheetPeriodStatus.Description,
MSP_TimesheetStatus.Description,
MSP_TimesheetStatus.TimesheetStatusID, MSP_TimesheetPeriod.PeriodStatusID,
MSP_EpmResource.ResourceNTAccount,
MSP_Timesheet.TimesheetUID, MSP_EpmResource.ResourceName,
MSP_EpmResource.ResourceType,
MSP_EpmResource_UserView.RBS,
MSP_EpmProject_UserView.ProjectName
HAVING (MSP_TimesheetPeriod.StartDate >= @StartDate OR
MSP_TimesheetPeriod.StartDate IS NULL) AND
(MSP_TimesheetPeriod.EndDate <= @EndDate OR
MSP_TimesheetPeriod.EndDate IS NULL)
ORDER BY MSP_EpmResource.ResourceName, MSP_TimesheetPeriod.StartDate

If you want hours broken down to the assignment level if you are reporting
timesheets down to that level then you will need to also join the assignment
and task tables.

You also need ot specify a value for @StartDate and @EndDate in this queiry.