Hi, I'm Heather O'Cull, another program manager on the Project team. In case you're wondering what happened to Treb, he is still here but has decided to pass the blog on to give more variety to it. Big thanks to Treb for all the great posts and hopefully he'll still be up for doing some guest posts.
I work more on the client and client reporting so I'll be giving the blog more of a spin in that direction. If there is anything you would like to see posts about, please let me know.
At the Project Conference I presented on client reporting and promised to post instructions on creating a burndown report through Visual Reports. Something like:
And here are the steps...
1. Have the project you want to report on open.
1. Go to Reports - Visual Reports
2. Select New Template, Excel, Assignment Usage, and click OK.
Your report is now being created in Excel. Switch over to Excel.
3. Add Time Weekly Calendar to the Row Labels section.
4. Check Cumulative Work, Actual Work and Baseline Work.
5. Move Values to the Column Labels box. Your fields should be setup like this:
6. Expand the time dimension out to the weekly level (you can really choose to any time level you'd like).
7. Make sure subtotals aren't showing (to remove in Excel 2007 go to the Design tab, Subtotals dropdown).
8. In the cell to the right of Baseline Work, type Remaining Actual Work, to the right of that type Remaining Planned Work, then Cumulative Actual Work, and Cumulative Baseline Work so you have the picture below. You now need to calculate all of these values.
10. For Cumulative Baseline Work, in J3 type =Sum($F$3:F3) and fill down the column for the number of weeks in your Project. You nave now calculated Cumulative Baseline Work for your project.
11. For Cumulative Actual Work, in I3 type =Sum($E$3:E3) and fill down the column like you did in the last step.
12. For Remaining Planned Work, in H3 type =(x-J3) where x is the total for the Cumulative Baseline Work column. Fill down the column.
13. For Remaining Actual Work, in G3 type =(y-I3) where y is the total from the Cumulative Actual Work column. Fill down the column. You've now calculate all the data that you need. If I switch to show formulas, you should have something that looks like this:
14. You're almost there. You now just need to graph your data. To do this, insert a column to the left of remaining actual work. This is the week column. Now paste the week numbers there so you'll have them in your graph.
15. Now just select the week column you just added, remaining actual work, remaining planned work, and choose to graph them as a line graph. You should have something like the picture at the top of the entry.
To make this look even better you can draw a status line to help demonstrate where you are in the plan. I also prefer to delete the values in Remaining Actual Work that are in the future to make the graph more compelling.
Ok, this is more about views than reports.
If I set a lookup table in a tree structure like:
How do I make a view in project center that only shows items from "a" and not from "b"? The filter will only allow "equals" and "does not equal". Kind of defeats the purpose of hierarchical lookup tables.
Also, why do the views in project center not "roll up" to each depth of the hierarchy?
This looks great but I can't follow your instructions on Excel 2003, can you post instructions for 2003?
I just don´t understand how to save the template.
When I save it and try reopening it does not work. It just loses the conection to project.
Can you please help me?
For some reaons, running Visual Reports on my computer consistently gives me an unknown error.
I have tried toggling the Tools > Options security settings, reinstalling Office, reinstalling Project, etc....to no avail.
Posted on the Newsgroups, and nobody could think of anything.
Figured I would ask you if you have any suggestions. Am running MPP 2007 on a Vista machine w/ Excel and Visio 2007 installed. To my knowledge, Visual Reports have never worked on this machine.
Eduardo - are you re-opening the template through Visual Reports or just through Excel?
Andrew - On the Visual Reports dialog, can you try going to Save Data and letting me know if you can save a database and save a cube? Also, when you installed Office did you do a custom install or go with the default settings?
I´ve tried reopening through Visual Reports.
Then it happens the data source is not available(I can see when I open the xlt) and excel would open with a dialog (expecting me to press "ok") so Project doesn´t know how to deal with it and excel crashes.
I tried to create a more elaborated template because I also want a "Size tracking" chart in the same report of the Burndown... But I always end up with the same problem....
By the way...
Do you have any paper or experience you could share about tracking "Size" using MS Project?
(despite of all other variables such as effort, duration, cost, etc, of course)
I´ve been doing this by inserting a "number 1" column (which I name as "Size"), and then, for each feature in the schedule I set a milestone linked to the summary task of that feature. For this milestone I set a value for the Size column.
In the report I want to be able to compute the size for all summary tasks that are 100% completed. :)
This way I can control how much of the software has already been delivered vs the effort vs duration.
Thanks for the help on my Visual Reports issue. Turned out to be a bad pc image. Reinstalled everything, and it all worked.
Next question. Am trying to do a Pivot Chart comparing Cumulative Baseline Work and Cumulative Work. Do the Visual Report, export to Excel.
I know I can do the sum feature like you have demonstrated above, but I would like to do the summation of the Cumulative Baseline Work w/in the Pivot Table. In theory, I should be able to select the field properties for Baseline Work, and set it to show a running total.
Yet every time I do that, I get an #N/A error in the Baseline Work field.
Why does the Running Total In feature not work for this PivotChart, and is there a way to get it to work?
Thanks in advance....
Good conversation. Has anybody had a chance to alook into Andrew's question? I am getting the same #N/A error and my chart doesn't do the trick I need. I am badly in need to have a solution.
Thanks in advance.
Are you trying to do the running total calculation in the pivotTable or to the outside of it? As far as I know, you can't update the pivotTable so you'd have to do this outside of it.
Normally when I get #n/a I have a typo in my formulas. For an easy way to verify this, go to the Formuals tab and select Show Formulas.
I also receive the #N/A error. I want to get a cumulated value of the actual cost and the baseline cost. The Pivot table features "the running total in" setting for values, which can be set in the field properties. Theoretically this should cumulate the values, but it doesn't and I receive the #N/A error mentioned by Andrew and Daniel. At least is there a possibility to add calculated measures to the local cube generated by the project client?
Thanks in advance,
I did this sample and everything works pretty good, but now I have a question regarding how to show the progress daily instead weekly.
Make a great day!!!
To see everything at the day level instead of week level, you need to set this in the main visual reports dialog. When you first go to Visual Reports - in the Create Report dialog, set "Select level of usage data to include in the report" to Days instead of Weeks.
For people having trouble getting this work, in Excel, go to the Formulas tab and try some of the commands in Formula Auditing. I'm betting your formulas are pointing to the wrong fields.
For some reason the visual report is not showing baseline work values. There is a baseline saved for the project and when I go back to project and tell it do display the baseline work values there are numbers there. Any idea why it shows up as 0?
Is this report possible using SSRS? Pivot tables are not needed if that makes it easier. Just looking to run a canned report on a schedule with this data along with other data I’ve been able to query using SSRS.
Thanks for any help,