view limitations

[M. Roberts]

Hi all,

I've added some custom fields to assign to detail lines
(specifically used to record a "Timesheet Project" code
to which costs for the task will be charged). It's
allowing me to group/report/manipulate based on cost
centres, but only to a point. One of the key reporting
requirements I have is to report on work remaining in
upcoming months, per project, per resource, etc. The
Resource Usage view is great for this, except that my
custom 'Timesheet Project' (of which there may be three
or four in a single *.mpp) fields are task fields, and as
such I can't group by these in the Resource Usage view.

So I'm using the Task Usage view. The limitation here is
that if I have a task which has multiple resources
assigned, and I group by project & resource, I get a
group heading for the combined resource effort, and need
to manually split the time between the resources.

Also, I find it infuriating that the timephased views
have Work, Actual Work (and even Cumulative Work!) but no
Remaining Work (despite Remaining Work being available in
other views...).

I'm using 2002 Professional. Do other versions have
enhancements that solve these issues, or can anyone make
suggestions?

Cheers,
M.

 

[John]

M,
I played around with your scenario a bit (assuming I understand it
correctly) and didn't come up with any ready solution using built-in or
even customized views. As you found out grouping in the Resource Usage
only groups resources, not their assignments. Consequently in the Task
Usage view, grouping is not identified to the resources which are the
assignments in the Task Usage view.

Ok, so you already know all that. So what is the solution? Unless
someone else has a way of showing what you need for your "timescaled
work remaining by project by resource", I suggest a VBA solution. It is
very feasible and not all that difficult to create a VBA macro that
gathers the basic Project data, manipulates it as you need and then
exports the data to Excel in exactly the format you want. That's the
grand solution.

You mentioned that Remaining Work is not available on the Usage views.
That is not correct. Remaining Work is available in either Usage view
but not as a timescaled value. However, on a smaller scale (less grand)
it should also be possible to create a macro that automates the manual
operation you perform on the Task Usage view to split the remaining work
across all resources in timescaled fashion. It will be a little more
complex if the resources have different calendars or work availability
than the full task duration.

I also took a look at Project 2003 (standard) and it does not perform
the type of grouping you want and neither is Remaining Work available as
a timescaled value in the Usage views.

I know my response doesn't solve your issues but it at least gives a
couple of suggestions.

John

 

[Marshall]

Thanks John.

Could you give me a starting point re the VBA sourcing
timescaled data? I'm familiar enough with VBA for you to
just throw a few keywords or similar at me. I wasn't
aware that you could access the timescaled data via VBA
so any suggestion here would be great, thanks.

 

[Mike Glen]

Hi Marshall,

Welcome to this Microsoft Project newsgroup

There's a small macro which might help in FAQ Item: 37 - Custom Fields in
Tables. FAQs, companion products and other useful Project information can
be seen at this web address: http://www.mvps.org/project/

Hope this helps - please let us know how you get on

Mike Glen
Project MVP

 

[Sarah]

Thanks John.

Could you give me a starting point re the VBA sourcing
timescaled data? I'm familiar enough with VBA for you to
just throw a few keywords or similar at me. I wasn't
aware that you could access the timescaled data via VBA
so any suggestion here would be great, thanks.

Marshall,

Here's a snippet of some code I wrote to access the timescaled data:

Set tsvs = pjResource.TimeScaleData(txtFromDt.Value,
txtToDt.Value, _
pjResourceTimescaledWorkAvailability, pjTimescaleDays)

For Each tsv In tsvs
appXL.ActiveCell.Offset(rowoffset:=0,
columnoffset:=1).Activate
If tsv.Value = "" Then
appXL.ActiveCell.Value = 0
Else
appXL.ActiveCell.Value = tsv / 60
End If
Next

The VBA Help may be a good starting point. Between searching the
archives of this newsgroup and searching through Help, I managed to
figure it all out! ;-)

Sarah
sarah_kiko@(removethis)cinfin.com

 

[John]

Marshall,
It looks like Sarah gave you something to get started although her
method runs with foreground processing. That is, Excel is visible and
each cell is activated so the code runs slower. For small Project files
this isn't a problem. However if your Project file is rather large or
has a large amount of data to export, I recommend code that operates in
the background. It is more efficient and runs quicker.

If you have further questions or need more help, post again.

John

 

[John]

Mike,
I considered recommending FAQ 37 but that would only transfer his custom
code from a Task text field to an Assignment text field. Unfortunately,
that still wouldn't allow his grouping technique to work.

John

 

[Mike Glen]

Ah!!! I haven't tried this, but I would have thought that copying the codes
from the Task Usage view where it worked to the Resource Usage view would
also have worked.

Mike