none
ms project 2010 building a visual report RRS feed

  • 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

    Sunday, April 7, 2013 10:14 PM

Answers

  • 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]

    Monday, April 8, 2013 12:36 PM
    Moderator

All replies

  • 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:
     
     
    Hope this helps.
     

    Dale A. Howard [MVP]

    Sunday, April 7, 2013 11:25 PM
    Moderator
  • 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.

    Monday, April 8, 2013 12:04 AM
  • 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]

    Monday, April 8, 2013 12:36 PM
    Moderator