ms project 2010 building a visual report

Question

Hello,

i'm having difficulty understanding how flexible modifying a visual report is. i did some research online and tried project help files but unfortunately, i wasn't able to find my answer. i've been trying the built in reports but i'm not finding all the fields i needed (and yes i tried the field pickers, there's always one element that's missing).

Here's what i'm trying to accomplish. i would like a timescale excel visual report that shows me the following:

- baseline work

-work

- actual work

-remaining work

-resource name

-DATE OF WORK AND ACTUAL WORK PERFORMED (It would be really helpful if i can get the dates on when this work happened, rather than breaking it down by quarters and then week 1, week 2, week 3. if i can get the dates of the weeks instead of week 1, week 2, week 3...it will be very helpful).

i'm assuming these fields should be available but i can't find them because they're already calculated in the task usage and resource usage views.

thank you very much for your help and if you think i should use a VBA(i already tried using the following:

Sub PhasedResourceData()

 ' Sub will export timephased resource data (work, cost) into Microsoft Excel worksheet
 'The output is data in Excel spreadsheet, which you can then use to create a pivot table.
 
' Define time interval for timephased data

 Dim Start, Finish As String
 Start = "1.1.2013"
 Finish = "31.12.2013"
 
' Define timescale unit. Can be one of the following PjTimescaleUnit constants:
 '   pjTimescaleYears, pjTimescaleQuarters, pjTimescaleMonths, pjTimescaleWeeks,
 ' pjTimescaleDays, pjTimescaleHours, pjTimescaleMinutes
 Dim TimescaleUnit As PjTimescaleUnit
 TimescaleUnit = pjTimescaleMonths
 
Dim Pj As Project
 Dim PjRes As Resources
 Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

 Dim IdSheet As Integer
 
 
Set Pj = ActiveProject
 Set PjRes = Pj.Resources
 
Set xlApp = New Excel.Application
 xlApp.Visible = False

 Set xlBook = xlApp.Workbooks.Add
 xlBook.Title = Pj.Title
 Set xlSheet = xlBook.ActiveSheet
 
Dim TSVWork As TimeScaleValues
 Dim TSVCost As TimeScaleValues
 Dim T As Long
 Dim R As Long
 Dim Row As Integer
 
 
' Choose work unit divisor depending on the Tools | Options | Schedule | Work.
 ' Work is stored in minutes in MS Project.
 Select Case Pj.DefaultWorkUnits
 Case pjMinute
 d = 1
 Case pjHour
 d = 60
 Case pjDay
 d = Pj.HoursPerDay * 60
 Case pjWeek
 d = Pj.HoursPerWeek * 60
 Case pjMonthUnit
 d = Pj.DaysPerMonth * Pj.HoursPerDay * 60
 Case Else
 d = 1
 End Select
 
' Set up currency format for Excel
 CurrencyFormat = SetCurrencyFormat(Pj)
 
If Pj.Resources.Count > 0 Then
 
xlSheet.Cells(1, 1) = "Group"
 xlSheet.Cells(1, 2) = "Resource"
 xlSheet.Cells(1, 3) = "Date"
 xlSheet.Cells(1, 4) = "Work"
 xlSheet.Cells(1, 5) = "Cost"
 
Row = 2
 
For R = 1 To Pj.Resources.Count
 Set TSVWork = PjRes(R).TimeScaleData(Start, Finish, _
 Type:=pjResourceTimescaledWork, TimescaleUnit:=TimescaleUnit)
 Set TSVCost = PjRes(R).TimeScaleData(Start, Finish, _
 Type:=pjResourceTimescaledCost, TimescaleUnit:=TimescaleUnit)
 
For T = 1 To TSVWork.Count
 
If Not TSVWork(T).Value = "" And Not TSVCost(T).Value = "" Then
 xlSheet.Cells(Row, 2) = PjRes(R).Name
 xlSheet.Cells(Row, 3) = TSVWork(T).StartDate
 Select Case TimeUnits
 Case pjTimescaleMonths
 xlSheet.Cells(Row, 3).NumberFormat = "Mmm Yy"""
 End Select
 
If Not TSVWork(T).Value = "" Then
 xlSheet.Cells(Row, 4) = TSVWork(T).Value / d
 xlSheet.Cells(Row, 4).NumberFormat = "#,##0"
 End If
 If Not TSVCost(T).Value = "" Then
 xlSheet.Cells(Row, 5) = TSVCost(T).Value
 xlSheet.Cells(Row, 5).NumberFormat = CurrencyFormat
 End If
 
Row = Row + 1
 
End If
 
Next T
 Next R
 
End If
 
xlApp.ScreenUpdating = True
 MSProject.ScreenUpdating = True
 'and finally display a message that we are finished
 AppActivate "Microsoft Project"
 
xlApp.Visible = True
 AppActivate "Microsoft Excel"
 End Sub
 
Function SetCurrencyFormat(Pj As Project)
 
' Set currency number format
 CurrencyFormat = “”
 
Select Case Pj.CurrencySymbolPosition
 Case pjBefore
 CurrencyFormat = """" & Pj.CurrencySymbol & """"
 Case pjBeforeWithSpace
 CurrencyFormat = """" & Pj.CurrencySymbol & """" & " "
 End Select
 
CurrencyFormat = CurrencyFormat & "#,##0"
 
If ActiveProject.CurrencyDigits > 0 Then
 CurrencyFormat = CurrencyFormat & "."
 For i = 1 To Pj.CurrencyDigits
 CurrencyFormat = CurrencyFormat & "0"
 Next i
 End If
 
Select Case Pj.CurrencySymbolPosition
 Case pjAfter
 CurrencyFormat = CurrencyFormat & """" & Pj.CurrencySymbol & """"
 Case pjAfterWithSpace
 CurrencyFormat = CurrencyFormat & " " & """" & Pj.CurrencySymbol & """"
 End Select
 
SetCurrencyFormat = CurrencyFormat
 
End Function

this worked fine but it's not breaking up the hours by weeks with their dates like i wanted.

thank you again for your help

Answer 1

eagle_one34 --

 

There is no default method to create the Visual Report in Excel according to your exact specifications.  The closest that you can probably get is to create the report according to the following specifications:

 

1.  In the Visual Reports �?? Create Report dialog, select Days in the pick list at the bottom of the dialog.

2.  Click the New Template button, select the Excel option and the Resource Usage option, and then click the OK button.B

3.  In the blank PivotTable Field List sidepane, select the Resources option.

4.  Select the Weekly option in the Time section.

5.  In the Values section at the top of the sidepane, select the Baseline Work, Work, and Actual Work options.

6.  In the bottom of the PivotTable Field List sidepane, drag the Values option from the Rows section to the Columns section, if necessary.

7.  In the PivotTable, expand the Year data until you see days.

 

What you cannot do is to add the Remaining Work data as this is not available in the Resource Usage cube.  You cannot label the columns with real dates either.

 

Regarding your VBA code, I would suggest you repost that part of your question in the Project Customization and Programming user forum at:

 

http://social.technet.microsoft.com/Forums/en-US/project2010custprog/threads

 

Hope this helps.

 

---

Dale A. Howard [MVP]

Answer 2

Thanks Dale.

i'll keep this post open to see if there is a way we can get this data and especially the dates instead of labels.

Thanks for everybody's help.

Answer 3

eagle_one34 --

 

If you are waiting for someone to tell you how to display dates instead of labels such as Week 1, Week 2, etc., you are in for a long wait, as there is no way to force a Visual Report to show dates and not labels.  Therefore, I truly believe that VBA would be your best route and would suggest that you repost the VBA part of your original question in the Project Customization and Programming user forum.  Hope this helps.

 

---

Dale A. Howard [MVP]