Need to report by Resource/time period/Finance category

[tripleboston]

Hi, I have a report I am trying to create for the finance team. The finance
team has a special outline category for each task. I need to break down the
report by this category first so all of the tasks under Finance category 1
are grouped together etc.

Finance categories need to be further broken down by the resource. So every
resource is listed if they have worked on a specific finance category.

Finally each resource needs to be broken down by time intervals which I need
to be able to specify.

So in summary

Finance Code 1 (A+B)
User 1 - Summary User 1 hours A (Aa+Ab)
Task 1 - hours Aa
Task 2 - hours Ab
User 2 - Summary User 2 hours (Ba+Bb)
Task 1 - hours Ba
Task 2 - hours Bb

Finance Code 2... etc

Mike

 

[Mike Glen]

Hi Mike,

Welcome to this Microsoft Project newsgroup

Create a Text field for your Finance category and populate it with the
groupings you want. Then have a look in Help for the "group" functions,
that should give you what you want.

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
MS Project MVP

 

[tripleboston]

Hi Mike, thank you for your response.

If I am understanding you correctly, I will need to manually assign the text
in each of the text fields with the finance code I need? For example if there
is a task called "Assemble car door" which I have 2 resources working on.
Task View:
Assemble Car Door

Resource View:
Bob
Assemble Car door
Joe
Assemble Car door

I will need to assign the text in the resource view (Finance Code:
assembly)? This would be quite time consuming if I had to assign the text in
the reource view since we have some steps assigned to many individuals.

Thanks again.

Mike

 

[Mike Glen]

How, Mike, would Project know which was a Finance Category 1 or 2 or...?


Mike Glen
Project MVP

 

[John]

Mike,
Perhaps I missed something in Mike's suggestion but based on your
original post I'm not sure you can get there from here using grouping
and/or filtering. As I understand it, the finance code is applied to
each task. However, even if that code is translated to an assignment
text field in the Resource Usage view, the grouping only applies to the
resource, not the assignment.

If the finance code is indeed applied to each task, won't the Task Usage
view more or less give what you want? If not, then I guess I'm not quite
following your file structure. Regardless, if I am missing some feature
of grouping, fine I'll learn something. But if not, you can get the
exact report you need by using VBA.

John

 

[Mike Glen]

Perhaps I missed something also! From the Gantt Chart view, inset a column
for the Finance Code. Manually add codes for each task. Now Project/Group
by.../More Groups.../New.../ give it a name and in Field Names, Group By
Finance Code field, Then by Resource Names, Then by Work (or whatever field
you want to see)/OK.


Mike Glen
Project MVP

 

[John]

Mike,
You are exactly right, as usual. I guess it's pretty obvious I don't use
the grouping feature very often so I am not that familiar with its
power. I forgot that grouping can be applied in tiers much like
filtering. I tried a sample file with a mock finance code applied to
each task. I then grouped first by the finance code (I used Text1) and
then by Resource Names. As long as there is only one resource assigned
to each task the resulting structure comes out exactly as Mike wanted.
However if multiple resources are assigned to each task, a separate
group needs to be created for each individual resource.

Depending on how complex the file (i.e. number of tasks and number of
resources), grouping may become a little cumbersome and it might be more
convenient to use a VBA approach. At least that's my synopsis.

John

 

[tripleboston]

First off, thanks for everyones answers.

I have tried what John suggested, but it results in grouping resources with
like tasks together, which I do not want to have.

What is the VBA option you are describing? Are you saying I write a macro to
generate this report? Is there a doc or url you can direct me to for an
introduction?

Here is a little background on what I am trying to accomplish:

There are multiple projects going on with multiple resources. Each project
is divided up into a number of subtasks. These subtasks are categorized by
finance as requirements gathering, development, and testing. (I am simplying
this).

Finance needs a report that breaks down by finance code > resource > tasks
and hours

So
Requirements gathering tasks (finance code 1)
Requirements gathering for project 1 (task)
John Doe 8 hours (resource and resource hours)
Fred Doe 8 hours " "
Requirements gathering for poject 2 (task)
Alan Doe 7 hours

Development (Finance code 2)
Programming for project 1 (task)
Alfred Doe 8 hours (resource and resource hours)
Fred Doe 8 hours " "

Thanks again everyone.

Mike B.

 

[Laurence Kelly]

Hello Mike B,

sometimes I think we try to do too much in Project...

You say Finance, I think spreadsheet!

Why not do this;

- create a consolidated project file containing all the separate
projects (you probably already have this, if not checkout
Insert/Project on the MSP menu)

- Select File/Save As...
- in the file type, select MS Excel Workbook
- Create your own map for the short list of fields you need from
assignments and Tasks (see below)
- Save the spreadsheet

You will find that if you have used Task Text1 field (or similar) for
the Finance Code as suggested in prior posts, you can't get at it via
Assignments, because they have their own Text1 field. So you will need
to export tasks as well, which will create a separate worksheet for
tasks.
You can then add a column to the assignments sheet to do a simple
vlookup of the Finance Code from the tasks sheet, matching by Task
Unique ID (you need to sort the task sheet by Unique ID first).

This issue will be the same whichever method you use (e.g VBA), you
need to associate the task Finance Code with the assignment.

Hope that helps.

Laurence Kelly

 

[tripleboston]

Laurence, we got what you described below to work, but would it be possible
to also get a breakdown of the daily actual hours worked as well? Right now
we only get a total hours.

Thanks again.

Mike

 

[John]

Laurence, we got what you described below to work, but would it be possible
to also get a breakdown of the daily actual hours worked as well? Right now
we only get a total hours.

Thanks again.

Mike

Mike,
Static (i.e. non-timescaled) data can be exported to Excel via an export
map as was suggested by Laurence. However daily actual hours is
timescaled data. There are two ways to get timescaled data into Excel.
If you don't mind doing some manual manipulation of the data you can try
using the "analyze timescaled data in Excel" utility (Project 2002 and
up) or add-in (Project 2000). If the utility just doesn't do what is
needed or becomes to laborious to manipulate, you can always write a
macro to export whatever data you want and format it in any way you
want. The nice thing about this approach is you can get exactly what you
need but it does take some VBA experience.

John