start & finish dates - what happens to dates inbetween

[AngePaton]

I have a large project that I need to create a pivot table and various
reports on. Th e pivot table needs to display by week, the amount of hours
that are required by each resource. However, I can only export Start & Finsih
dates to Excel, I am unable to work out how to create a pivot table that will
show that a resource is working on a task over 3 months so Wk1 = 16 hrs
effort, wk2 etc..... Using grouping on the pivot doesn't work as it only
shows the week the task starts in.
Does Project have a 'work effort by week' report? The 'resource usage'
report gives me what I want, but I can't export it to Excel, is there a
way????

 

[JulieS]

Hi AngePaton,

Have you tried showing the Resource Usage view and use the "Analyze
Timescaled Data in Excel..." button from the Analysis toolbar to export the
report to Excel?

Hope this helps. Let us know how you get along.

Julie

 

[Steve House [MVP]]

Have you looked in the Reports menu and examined the Custom Reports option?
In custom you'll find a listing for a Crosstab report, which is just another
name for what Excel calls a pivot table report. If you select Crosstab and
choose Edit, you can choose either tasks or resources for the rows and you
can edit the field to summarize to be Work, among other things. When I run
it, I get a report that shows weeks as columns, resources as rows with their
tasks indented under them (an option you can turn on or off as you wish) and
the table shows the total work done by that resource week by week. If a
task runs more than 1 week, the work to be done in each week is apportioned
out into the week where it takes place, NOT accumulated into the week during
which the task begins as you said you've been getting. That sure sounds like
the report you're trying to generate and you don't even need to mess around
exporting to Excel at all - it's right there in Project's standard report
packge right out of the box. True, it is a static snapshot, but then a
Pivot Table report in Excel is also a static object and an exception to the
automatic recalculation behaviour usually associated with Excel worksheets
and formulae.

 

[AngePaton]

Thanks Steve, that has worked well, however, there is one little problem that
you might be able to help with, as some of my data in the columns is large,
the columns display ### instead of the numbers.
I have not been able to widen the columns, no matter what changes to setup
are made, also am I right in thinking that the reports can't be exported as
my boss really wants a bar chart so I need to export to excel.

Many thanks

 

[Steve House [MVP]]

I'm not aware of any way to widen the columns either. Of course it would be
relatively rare for a resource to have more than a total of something aound
40 hours in a week plus OT so it's not usually an issue and certainly a
column width that can accomodate up to 999 hours in a week should be more
than adequate. If a single resource has more hours than that in a week you
really need to reconsider your task breakdowns and resource listings - I
tend to favour listing resources as individual workers unless they are truly
indistinguishable units, not large groups, since one usually should be
breaking down the tasks to the level of 1 resource on 1 task. If I have
Task X that requires 10 welders, for example, it's very likely that X really
should be a summary task with 10 or so individual component activities
detailed as subtasks under it.

There's not any way directly to export reports to Excel, unfortunately. I'm
trying to visualize what sort of bar chart your boss wants and frankly it's
a puzzle what it might look like given that there are 3 data elements, 2
categories and a value (resource name, week, hours scheduled) while a bar
chart only deals with 2 elements, a category and a value.
--
Steve House [MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

 

[JulieS]

Hi AngePaton,

To widen the columns in the report, select the report and choose edit. On
the Details tab, change the date format to a longer format and your data
should appear.

Hope this helps. Let us know how you get along.

Julie

 

[Steve House [MVP]]

Good idea!