strange report: what does a person on different days and in whathours

[Benedykt]

Hello,

in one project I have some tasks, lets call them "task 1", "task 2"
and "task 3". One person is assigned to it, let's call him "Andrew".
Andrew has strange and complicated resource calendar. I want to
generate such report to Andrew:

2010-07-01:
07:00-15:00 - task 1

2010-07-02:
09:00-17:00 - task 1

2010-07-04:
08:00-16:00 - task 1

2010-07-06:
07:00-15:00 - task 2

....et caetera. I mean: this report should show on what days in what
hours Andrew does what.

Do you have any idea how to achieve it? As far as I know there isn't
any report like that. But maybe I could export "resource usage" to
Excel and then prepare such report in Excel? But I couldn't find a way
to export "resource usage" to Excel. Is it at all possible?

Thanks a lot for any hint!

 

[Benedykt]

(...) But maybe I could export "resource usage" to
Excel and then prepare such report in Excel? But I couldn't find a way
to export "resource usage" to Excel. Is it at all possible?

Seems I'll be the first to answer my own question.

As far as I googled, it seems that the only way is to use VBA. I
managed to do it using some hints and snippets from the Internet. Here
it goes:

Sub exportResourceUsage()
'first define our variables
Dim r As Resource
Dim a As Assignment
Dim rs As Resources
Dim TSV As TimeScaleValues
Dim pTSV As TimeScaleValues
Dim i As Long, j As Long
'define excel variables
Dim xlRange As Excel.Range
Dim xlCol As Excel.Range
Dim xlRow As Excel.Range
Dim xlApp As Excel.Application

'open excel and set the cursor at the upper left cell
Set xlApp = New Excel.Application
xlApp.Visible = True
' Czy ponizsza linijka jest potrzebna?
'AppActivate "Microsoft Excel"
Set xlBook = xlApp.Workbooks.Add
Set xlsheet = xlBook.Worksheets.Add
xlsheet.Name = ActiveProject.Name
row_ = 1
Set xlRange = xlApp.ActiveSheet.Cells(row_, 1)

'start writing column headers
xlRange.Value = "data"
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = "pracownik"
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = "zadanie"
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = "godzin"
row_ = row_ + 1
Set xlRange = xlApp.ActiveSheet.Cells(row_, 1)

'use the dates from the project summary task TSV to set column
headings
start_ = ActiveProject.ProjectStart
end_ = ActiveProject.ProjectFinish
'Set pTSV = ActiveProject.ProjectSummaryTask.TimeScaleData(start_,
end_, TimescaleUnit:=pjTimescaleDays)
'For j = 1 To pTSV.Count
'Set xlRange = xlRange.Offset(0, 1)
'xlRange.Value = pTSV.Item(j).StartDate
'Next j

'loop through all resources and write out values
Set rs = ActiveProject.Resources
For Each r In rs
For Each a In r.Assignments
Set TSV = a.TimeScaleData(start_, end_,
TimescaleUnit:=pjTimescaleDays)
For i = 1 To TSV.Count
If Not TSV(i).Value = "" Then
xlRange.Value = TSV(i).StartDate
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = r.Name
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = a.TaskName
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = TSV(i).Value / (60)
row_ = row_ + 1
Set xlRange = xlApp.ActiveSheet.Cells(row_, 1)
End If
Next i
Next a
Next r

'some minor excel formatting of results
xlApp.Cells.Select
xlApp.Cells.EntireColumn.AutoFit
xlApp.ActiveSheet.Cells(1, 1).Select
'xlApp.Activate
End Sub

But maybe there is some simpler way to get such report?

 

[JulieS]

Seems I'll be the first to answer my own question.

As far as I googled, it seems that the only way is to use VBA. I
managed to do it using some hints and snippets from the Internet. Here
it goes:

Sub exportResourceUsage()
'first define our variables
Dim r As Resource
Dim a As Assignment
Dim rs As Resources
Dim TSV As TimeScaleValues
Dim pTSV As TimeScaleValues
Dim i As Long, j As Long
'define excel variables
Dim xlRange As Excel.Range
Dim xlCol As Excel.Range
Dim xlRow As Excel.Range
Dim xlApp As Excel.Application

'open excel and set the cursor at the upper left cell
Set xlApp = New Excel.Application
xlApp.Visible = True
' Czy ponizsza linijka jest potrzebna?
'AppActivate "Microsoft Excel"
Set xlBook = xlApp.Workbooks.Add
Set xlsheet = xlBook.Worksheets.Add
xlsheet.Name = ActiveProject.Name
row_ = 1
Set xlRange = xlApp.ActiveSheet.Cells(row_, 1)

'start writing column headers
xlRange.Value = "data"
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = "pracownik"
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = "zadanie"
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = "godzin"
row_ = row_ + 1
Set xlRange = xlApp.ActiveSheet.Cells(row_, 1)

'use the dates from the project summary task TSV to set column
headings
start_ = ActiveProject.ProjectStart
end_ = ActiveProject.ProjectFinish
'Set pTSV = ActiveProject.ProjectSummaryTask.TimeScaleData(start_,
end_, TimescaleUnit:=pjTimescaleDays)
'For j = 1 To pTSV.Count
'Set xlRange = xlRange.Offset(0, 1)
'xlRange.Value = pTSV.Item(j).StartDate
'Next j

'loop through all resources and write out values
Set rs = ActiveProject.Resources
For Each r In rs
    For Each a In r.Assignments
        Set TSV = a.TimeScaleData(start_, end_,
TimescaleUnit:=pjTimescaleDays)
        For i = 1 To TSV.Count
            If Not TSV(i).Value = "" Then
                xlRange.Value = TSV(i).StartDate
                Set xlRange = xlRange.Offset(0, 1)
                xlRange.Value = r.Name
                Set xlRange = xlRange.Offset(0, 1)
                xlRange.Value = a.TaskName
                Set xlRange = xlRange.Offset(0, 1)
                xlRange.Value = TSV(i).Value / (60)
                row_ = row_ + 1
                Set xlRange = xlApp.ActiveSheet.Cells(row_, 1)
            End If
        Next i
    Next a
Next r

'some minor excel formatting of results
xlApp.Cells.Select
xlApp.Cells.EntireColumn.AutoFit
xlApp.ActiveSheet.Cells(1, 1).Select
'xlApp.Activate
End Sub

But maybe there is some simpler way to get such report?

Hello Benedykt,

You can export some of the data from the Resource Usage view assuming
you are using Project 2003 or earlier by using the "Analyze timescaled
data in Excel" button on the Analysis toolbar. I don't believe you'll
find you get the detailed information through that method as through
using VBA. In Project 2007, you can create pivot tables in Excel with
the data through Visual reports.

Also, Microsoft has discontinued this newsgroup
(microsoft.public.project) on their news server in favor of forums.
Try posting any future questions to the forums and you'll likely get
the attention of the community much faster. See:
http://social.technet.microsoft.com/Forums/en-US/category/projectserver2010,projectprofessional2010

I hope this helps.
Julie