Standard and custom fields not available in Visual Reports

[Huckey]

Hi,
I want to visualize my project's progress by showing a Radar graph
which would have project phases as axis (that would correspond to
Task1 in how Project exports data to Excel) and showing % Work
Complete on each axis.

The problem I have is that even though I have % Complete and % Work
Complete fields selected in Field picker when I try creating a new
template in Visual Reports, these fields (and I guess many more) are
not available on the list of fields in Excel's Pivot Table. Same
problem applies to any custom field I have.

Since I have fields visible in Selected fields list in Field picker,
I would expect them to be available in the corresponding Pivot table;
unfortunately this is not the case.

How can I get % Complete and % Work Complete to be available in the
Pivot table when creating a new Template in Visual Reports?

I am using Project Professional 2007 and Excel 2003.

Thank you!

Best regards

Jacek

 

[JulieS]

Hello Jacek,

If you have added the fields to the report as you've built it, you can
add the field to the report. Add the task names to the pivot table if
you haven't already then right-click on any task name. Choose the
option "Show properties in the report" and select the fields.

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


in message
news:7540a49e-7bf3-47dd-ad6b-3686b63e784f@e39g2000hsf.googlegroups.com...

 

[Huckey]

Hi Julie,
thank you for the answer. Indeed, I was able to get these values
displayed (I had to use the Pivot Table bar and click Pivot Table-

Property Fields though).

This however is only half a win. I can get the values displayed but
I can not use them in a chart (as they are only properties and not
data from Pivot table point of view). I know I can manually copy tasks
and corresponding % Work Complete values to another sheet and then
make a chart but as this is a manual procedure it somehow goes against
my understanding of how these templates are supposed to work.

Is there a way to get % Work Complete values being treated by Excel
as data so that they can be displayed on charts automatically?

Thank you!

Best regards

Jacek

 

[JulieS]

Hi Jacek,

You're welcome for the assistance and thanks for the feedback

I'm afraid I have some bad news regarding what you want -- I believe
you are looking for *time-phased* % Work complete values and sadly,
they aren't in Project. It is the time-phased values that are
included in the pivot table data as "chartable" fields. The 3 % Work
complete fields (task, resource, and assignments) certainly change
over time as you update the information but the history of those
changes is not maintained in Project.

I'm not sure I follow what other data you are looking for other than
task names and % work complete, but have you considered just a
straight export to Excel with File > Save As? You can easily select
any data you would see in the Task Sheet just not any data you would
see in the right side (time-phased) side in the Task Usage view. No
pivot chart created, but you'll have the raw data. You can also
export the values of custom fields.

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

Julie
Project MVP


in message

 

[Huckey]

Hi again,
thank you for the explanation.

I wanted to have these fields as "chartable" in order to get as much
automation with the template as possible. I was able to work around
this problem by a simple macro (I include it below - maybe someone has
a similar problem).

Just one question more: there is a very nice field available in MS
Project called Status. I can display it for instance in the Gannt view
but this field is not available in the field picker. Is there a way I
can get it available for Visual Reports?

And here is the macro. I assume here there is a Pivot Table with
tasks (whatever level) as Row Fields and that there is one Property
Field added for them. The macro creates then a Radar Chart (but it can
easily be changed to any other type). The code should be put in
PivotTableUpdate Event handling procedure for the Worksheet where the
Pivot table is. Hope it helps someone.

<CODE>
Dim lSheet As Worksheet
Dim i As Integer

ActiveSheet.Range("A3").Select
ActiveCell.PivotTable.RowRange.Select

Selection.Copy

'ActiveWorkbook.Sheets.Add

'Set lSheet = ActiveWorkbook.Sheets(2)

Set lSheet = ActiveWorkbook.Sheets.Add()

lSheet.Select
lSheet.Activate
lSheet.Range("A1").Select
lSheet.Paste


i = lSheet.UsedRange.Rows.Count - 2


lSheet.Range("B3:C" & i).Select
Charts.Add
ActiveChart.ChartType = xlRadarFilled
ActiveChart.SetSourceData Source:=lSheet.Range("B3:C" & i), PlotBy
_
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = "0%"

ActiveChart.PlotArea.Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
Selection.Border.LineStyle = xlNone
ActiveChart.HasLegend = False
ActiveChart.ChartArea.Select
Selection.Copy
</CODE>

Thanks!

Best regards

Jacek

 

[JulieS]

Hello Jacek,

Thanks very much for posting your code to enable charting of the data.
To the question of adding additional fields to the OLAP cube used by
the pivot tables, I don't believe so. As you seem to have a talent
for VBA, try posting to the microsoft.public.project.developer
newsgroup. Someone there may be able to help you create an export
(through code) that will allow you to export the precise fields you
need.

I hope this helps.

Julie
"Huckey" wrote in message
news:be1698ad-119b-4161-bae3-6ba6062b591f@d45g2000hsc.googlegroups.com...