Spiro and Julie
I have been reading your posts for the subject Resource Group by Month and I was curious if you found out if this was possible to do in the visual reports Pivot Table feature. I see that Julie in her reply managaged to tweek the pivot table by converting it to a regular Excel sheet and present it graphically by making her own graphs manually.
This is timeconsuming and I was wandering if someone has been able to tweak the visual reports to be able to present a group of resources such as Engineering per month in the built in visual reports?
Looking forward for your reply.
ST wrote:
Need Resource 'Group' in Visual Reports
11-Aug-09
Hi folks
Need your help once more...I am having difficulty getting the resourc
'Group' field to show up as a choice in the Visual Reports > Resource Wor
Availability Report's "PivotTable Field List" box. I can see the Group fiel
in the 'Selected Fields' box when I click on "Edit Template...", but it doe
not appear in the "PivotTable Field List" that appears when the Excel fil
opens up. Is there any way to do that
I would like to use this field as a filter in this report so that I can sho
the data based on the resource Group
/Spiro.
Previous Posts In This Thread:
Need Resource 'Group' in Visual Reports
Hi folks
Need your help once more...I am having difficulty getting the resourc
'Group' field to show up as a choice in the Visual Reports > Resource Wor
Availability Report's "PivotTable Field List" box. I can see the Group fiel
in the 'Selected Fields' box when I click on "Edit Template...", but it doe
not appear in the "PivotTable Field List" that appears when the Excel fil
opens up. Is there any way to do that
I would like to use this field as a filter in this report so that I can sho
the data based on the resource Group
/Spiro.
Hello Spiro,Once you've generated the report in Excel, from the Pivot Table
Hello Spiro
Once you have generated the report in Excel, from the Pivot Tabl
Field list, add Resources to the report. Right click on one of th
resource's name in the report and from the shortcut menu, choos
"Show Properties in Report" and select Group
That being said, I am not sure you will be able to use the Group fiel
as a filter as it is a property of the Resource. You can certainl
filter by resource(s), but I do not believe the Group field is a
option
I hope this helps. Let us know how you get along
Juli
Project MV
Visit
http://project.mvps.org/ for the FAQs and additiona
information about Microsoft Project
ST --The Group field is not available in your Visual Report because it
ST -
The Group field is not available in your Visual Report because it require
the resources to be displayed in the Visual Report first. In you
PivotTable, add the Resources dimension to the Row Labels drop area. The
right-click anywhere in the Resources column, and then choose Sho
Properties in Report > Group in the shortcut menu. If you do not includ
the Resources dimension, you cannot include the Group field. Hope thi
helps
-
Dale A. Howard [MVP
VP of Educational Service
msProjectExpert
http://www.msprojectexperts.co
http://www.projectserverexperts.co
"We write the books on Project Server"
If I can't get Group as a filter, is there another way to do this what I am
If I cannot get Group as a filter, is there another way to do this what I a
looking for
How about if I use the Text1 as a field instead and populated that? I ca
not seem to bring that in either
Would creating a template with the resources preselected belonging to th
groups I need be the way to go
/Spir
:
Thanks Julie,I posted this for Dale too since he responded basically saying
Thanks Julie
I posted this for Dale too since he responded basically saying the sam
thing... but,
If I cannot get Group as a filter, is there another way to do this what I am
looking for?
How about if I use the Text1 as a field instead and populated that? I can
not seem to bring that in either.
Would creating a template with the resources preselected belonging to the
groups I need be the way to go?
/Spiro
:
Hi Spiro,Sorry, but a text field isn't going to do it either.
Hi Spiro,
Sorry, but a text field is not going to do it either. I understand
you are using the Resource Work Availability report. Are there
specific fields you are looking for? How do you have the pivot table
set up -- what values to row, column etc.
Describe more about what you wish to show and perhaps we can come up
with an alternative. Depending upon the level of detail you want to
show you may get closer by removing the Pivot Table features through
the "Convert to Formulas" option on the OLAP Tools drop-down on the
Options tab in the Pivot Table Tools Group in Excel.
In playing around, I was able to get much more customization
available by saving the data to a database then creating my own
queries using Access. It takes some experimentation to find the
correct data, but once I found the necessary tables, I was able to
create a query with a pivot table view, that allowed me to see
resources, monthly total work. total availability, total remaining
availability and use the Group field as a filter.
Julie
The default Resource Work Availability Report by month is fine.
The default Resource Work Availability Report by month is fine. I would just
like to filter by resource filed, like Group.
/Spiro
:
ST --I think the limitations of the Visual Report will prevent you from doing
ST --
I think the limitations of the Visual Report will prevent you from doing
what you want. As an alternate idea, you might consider the following:
1. Click View - Resource Usage.
2. Right-click in the timephased grid and then choose the Details Styles
item on the shortcut menu.
3. Choose the following fields in the following order:
Work Availability
Work
Remaining Availability
All Assignment Rows
4. Click the OK button.
5. Widen the Details column on the left side of the timephased grid.
6. Click the Group By pick list and choose the Resource Group item on the
list.
7. Click the Show button (bottom toolbar) and choose Outline Level 2.
I realize that this will not work as elegantly as a Visual Report, but it would
allow you to see the availability for your groups of people. Just a
thought. Hope this helps.
I created a default Resource Work Availability by Month report.
I created a default Resource Work Availability by Month report.
Added the Resources as Row labels and the time monthly calendar to
column. Right click on the Resources Name and add Group as a report
property. Under the Pivot Table tools tab, click Options, Open the
OLAP Tools drop-down, select Convert to Formulas. Insert a blank
row above resource, turn on Autofilters. Filter by Group. I can
now filter the list by Resource Group and show Work Availability,
Work, and Remaining Availability by month.
Julie
thanks Dale,I miss that 'Analyze Timescale Data in Excel' feature that we had
thanks Dale,
I miss that 'Analyze Timescale Data in Excel' feature that we had in 2003! : (
/Spiro.
:
Julie --You are way too smart!
Julie --
You are way too smart!
ST --Don't we all, my friend!
ST --
Don't we all, my friend! Visual Reports were meant to replace this feature,
but they really do not.
Thank you Dale.
Thank you Dale.
I would not say particularly smart -- just determined to get it to
work to my liking
Julie
"Dale Howard [MVP]" <dale(dot)howard(at)msprojectexperts(dot)com>
I've tried a couple of times...
I have tried a couple of times...
As soon as I select 'Convert to Formulas', the Pivot Table replaces my data
with ###### and Excel no longer responds (cursor = hour glass). I waited
over 5 minutes and no change in Excel.
/Spiro.
:
Hi Spiro,Sorry, I can't reproduce.
Hi Spiro,
Sorry, I cannot reproduce. I am running Project 2007 SP-2, Excel
2007 SP-2 on an Windows XP guest Virtual PC. I am running minimal
memory to the guest PC, so I am guessing it may be a service pack
issue?
Julie
Hi Julie,If I may... I would like to ask you a couple more questions...
Hi Julie,
If I may... I would like to ask you a couple more questions...
I have tried to reporduce the problem I mentioned later in the post on
someone else's PC, but they are running Excel 2003 (ie., Convert to Formula
makes Excel hang on my PC) , and now have this question for Excel 2003: Do
you know if 'Convert to Formulas' exist in Excel 2003? If so, where would I
be able to locate it?
Also, I experienced an issue on both versions when trying to access the
Excel files I saved that Visual Reports helped me output yesturday... On the
Excel 2003 files that I create yesturday, and the one I tried to access from
within Powerpoint that I embedded, I get this message: "Can not open Cube
C:\...\ResourceTP.cub", then a Multidimensional Connection wizard window
comes right up and offers me a choice: Analysis Server, or Cube File.
The file that I created a few moments ago, even with MS Project shut down, I
can still make selections from field drop downs, but I can not do so with the
files I created yesturday.
Is there a valid choice I can make in the Multidimensional Connection
wizard? Do I create a offline data file, or something?
/Spiro.
:
Hello Spiro,I've saved the resource usage cube and transferred it to a machine
Hello Spiro,
I have saved the resource usage cube and transferred it to a machine
with Excel 2003 SP-3. Upon opening the cube in Excel, I do get an
"Opening Query" message warning me of possible security issues.
Once I okay the opening, I do not get any further messages.
I have taken a quick tour around the choices in the pivot tables, and
do not find any choice of converting to formulas.
I also took the Pivot table created in Excel 2007 and opened it in
Excel 2003. It did open without trouble but it obviously does not
work quite the same as it does in 2007.
I have not had any trouble working in the Excel file without Project
being opened, so I am not sure what you are seeing on your
installation. I have never seen the "multi-dimensional connection
wizard" so I am afraid I am not of much help here.
Julie
Re: Need Resource 'Group' in Visual Reports
Hi Julie,
When you said:"I have saved the resource usage cube and transferred it...",
how/ what do you mean... i.e., save the usage cube? Perhaps this is my
problem; I am just doing a simple savE operation in Excel of my Visual Report
output; nothing to do with saving the cube. Let me know...
Thanks again.
/Spiro.
:
Hi Spiro,Because the machine I use with Project 2007 doesn't have Excel 2003
Hi Spiro,
Because the machine I use with Project 2007 does not have Excel 2003
(only 2007), instead of running through the Visual Report which
saves the data directly to Excel, I tried an experiment of saving
the resource usage data as a cube file. Instead of running through
the Visual reports as usual, click the Save Data button in the lower
left of the Visual Reports dialog. From the Save Reporting data
dialog, pick the cube you wish to save data from. You can also use
the Save Data button to save all the reporting data to an Access db.
I was able to fairly quickly create a pivot table query in Access
showing the same information as the Visual Report in Excel.
Just to clarify , I was able to take the Visual Report created in
Excel 2007 and display it in Excel 2003 (sp3), however the "Convert
to formulas" command does not appear to be in Excel 2003.
What is your service pack level to Excel 2003 and Excel 2007?
Julie
Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server FOR XML EXPLICIT Examples
http://www.eggheadcafe.com/tutorial...8-6f0cacdd9000/sql-server-for-xml-explic.aspx