Project 2007 - Visual Reports - Custom Field, Code, or Group

[dpitts]

I am trying to create a visual report in Project 2007 to report budget / cost
information grouped by code, group, or a custom field that I created called
budget type. These fields are all listed in the Resource Sheet View. When I
generate the report these fields are listed in the field picker, however when
excel opens the fields are not listed in the pivottable field list and as a
result I cannot sort by these fields.

 

[JulieS]

Hello dpitts,

If you have added the custom field to the cube it should be available as
a property that can be displayed in the report.

For example, I added the Text1 field to the Resource Sheet and populated
it with codes for the resources. I added the Text1 field to the
Resource Cost Summary report cube. Once the report is generated, I go
to the Resource Usage tab of the report and add Resources from the Pivot
table field list to see each resource name. I then select one of the
resource names in the spreadsheet, click my secondary mouse button to
show the shortcut menu. I select "Show properties in the report" and
select the custom field (Text1) I added to the cube.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

 

[tom berlin]

Hi Julie,
I have similar problem about this topic:
I have a custom text field for resources with a lookup table. Now I could
add this to the resource usage cube for example, but not to the assignment
usage cube. Why? it's very reasonable for me to can filter my pivot table
with this special resource attribute. If I understand something wrong I would
be very happy when you could give me a small advice.
thx a lot
tom

 

[JulieS]

Hello Tom,

I'm afraid I don't have a good answer for you. When running through the
available templates for Assignments data (Baseline cost Report, Baseline
work report, etc.) as well as creating a new Assignment Usage template,
none of the custom fields are available in the Field picker. Sorry, I
don't know why none of the custom fields are available.

As to why data you entered into a Resource custom field is not available
automatically in the assignment custom field, perhaps the information
below may help shed some light, but it still won't answer the issue that
the custom field is not an option to add to the cube once you do have
data in it.

Custom fields in MS Project come in 3 broad categories -- Task,
Resource, and Assignment. Filling data into the Resource Text1 field
does *not* automatically fill in data to either the Task Text1 field,
nor the Assignment Text1 field.

In Project 2007, unlike earlier releases, you can have data "roll down"
from a custom task or resource field to the same assignment field.(You
can set that option in the Customize Field dialog box). As far as I have
been able to determine, the "roll down" simply copies the data from the
task field to the task assignment field or from the resource to the
resource assignment field. Also note that unlike previous versions of
Project the Resource Assignment Text1 field is not the same as the Task
Assignment Text1 field.

I hope this helps in some small measure and doesn't serve to just
confuse you more. Please do post back with any additional questions and
we'll try to lend a hand. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

 

[Peters]

Dear Julie,

Your advice always helps me out! Thanks.

I did just the way you explained, however, I still have a problem. I have
made a custom field "text1" to indicate if a resource is internal or
external. When using the report you suggested, I can get for example Material
and Work divided into the resource names and then the text1 field indicating
wether it is an internal och external cost. But I would like to skip all the
resource names, and just have for example Material and Work divided into
internal and external, thus four sums or categories:

Material
Internal $1000
External $1500

Work
Internal $1200
External $2000

I have not been able to do that, could you please help me?

Thanks in advance,
Peter

 

[JulieS]

You're welcome Pete glad to have helped.

I'm afraid the best I could do with your current question is a bit of a
work around by manipulating the data once it is in Excel, but I hope it
helps with you scenario. Go ahead and create the pivot table as you
describe, adding the Resource Names and Text1 field to the cube. What I
see when I do that is just as you describe:

Column A in the Excel spreadsheet shows Type - Material & Work
subtotaled by type.
Column B shows the names of the resources in detail
Column C shows the data from the Text1 field
Column D shows the subtotal by type and the detail for each resource.

Unfortunately (and I'm not an Excel expert) working with the data in the
pivot table is a bit of a challenge, so I take the pivot table data and
copy it out to another area of the worksheet and manipulate the data
there. Step by step follows:

1. Insert a new worksheet in the workbook.
1.Go back to the Resource Usage sheet and select the pivot table by
clicking the cell A3.
2.Copy.
3.Click on the inserted worksheet tab, select Cell A1, right click to
show the shortcut menu.
4.Select Paste Special... in the shortcut menu to display the Paste
Special dialog box.
5.Select Values and click OK to paste just the data from the pivot
table.

Now column A in the new worksheet shows Type - Material & Work
subtotaled by type
Column B shows the names of the resources in detail
Column C shows the data from the Text1 field
Column D shows the subtotal by type and the detail for each resource.

6. Delete Column B in the new worksheet which contains the resource
names.(You could also just hide it)

You can now insert and delete rows to separate the external and
internals and then total each in Excel.

You can use the grouping in Excel to group the internal/external or just
hide the rows you don't want to see.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

 

[JulieS]

Hello Tom,

Project has three types of fields, task, resource, and assignments.
When you added the custom text field for resources and populated it with
data, that data is *not* passed to either tasks or assignments. See FAQ
#51 at http://project.mvps.org/faqs.htm for some additional details.
When you are working with the assignment cube, resource properties (your
custom text data) is not available nor is task property data.

I'm afraid I cannot answer your question about why custom fields are not
available in the assignment cube, I can only confirm that they are not
available to be added to the cube by customizing the visual report. I'm
sorry I don't have any more insight to offer.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

 

[Peters]

Thanks Julie!

I was working along the line you described but was hoping that I could keep
the dynamics of the pivottable and create the wanted result without making a
copy of the data. The grouping feature is a good advice that I will use!

I will start copying and pasting!

Thanks again,
Peter

 

[JulieS]

You're welcome Peter and thanks for the feedback.. I tried working with
the data in the pivot table but couldn't add the additional rows to be
able to the pivot table, that's why I had to copy and paste.

Probably the same reason you were copying and pasting as well

Again, glad to have helped and let us know if you have any questions in
the future.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

 

[prm]

Hello Julie,

I have one connected question - I'm able to display the customer field as
mentioned by you.
But How do you use the same for filtering purpose.
The custom field comes as properties of resources and looks like can't be
used for filtering purpose.
Basically - i would like to use custom fields to display the relevant
information

Thanks and Regards
P. Rama Murthy

 

[JulieS]

Hello P. Rama Murthy,

I'm not 100% clear on what your question is. You note that you have
been able to display the custom field in the cube report. After sorting
by the custom field (to group the custom field values together), can you
select the values you wish to filter by in the field and from the
shortcut menu select Filter > Keep only selected items or Filter > Hide
selected items?

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project