Thursday, February 02, 2012 6:51 PMMy MS Project file has several projects added to it. I have a custom field called "Project number" to define which row is the top level project for each sub-project. Is there a way to create EV graph for each sub-project by filtering by "Project number" ?
Thursday, February 02, 2012 9:11 PM
The simple answer is yes, however more information is needed.
The subject line of your post says "visual report" yet I see no mention of how that fits in with your question. Is there a particular visual report you are trying to use?
There are several metrics associated with earned value so "EV graph" doesn't really tell us much. What exactly do you want to graph?
You indicate you have a "project number" to identify the top level summary for each subproject. If you have a master file as you indicate, each subproject already has a readily identifiable summary line in the master, so why the "project number"?
Friday, February 03, 2012 3:12 AM
Within each Project file, at Outline level 3 summary task is a level that I assign "Project number" and "Project type". In Task usage, I group project by "Project type", so I can get Workload information (Time-phased) for all projects in the same Project type (containing Multiple Project numbers). What I want to get from Visual Report is
1. Workload graph for each Project Type.
2. EV (BCWS, BCWP, and Cumulative Work) by Project Type from Earn Value view.
Friday, February 03, 2012 5:00 PM
I set up some test files based on what I think you have. I used Text1 as the "project type" (see Gantt Chart below) In the Task Usage view I simply used fill down to populate all task and assignment text1 fields. I'm sure it's not exactly what you have but I believe it is close enough to demonstrate the feasibility.
I then went to Project/visual reports and selected the Earned Value Over Time report which I edited to include Text1 as a selected custom field. I ran the export. Now the fun began. I do not find pivot tables to be very intuitive and I've only used them when attempting to help someone, like you, in this and other forums. I played with the pivot table and chart characteristics to get a filtered chart for "Proj A" or "Proj B". I won't begin to try and tell you everything I did to get the desired workload graph for each project type over time, but it is doable. If I were more proficient with pivot tables I could probably do a better job of walking you through the details.
Personally I would probably code this all up in VBA to get the desired graphs simply because I'm much more comfortable with VBA and have done this type of report several times using that approach.
Hopefully I answered your question. It can be done. If you want to use visual reports, you'll simply have to become proficient with pivot tables. Otherwise I suggest you try a VBA approach. I guess there is also the inefficient but straightforward approach of cut and paste from a grouped Task Usage view to Excel, so you really do have a few choices.
Friday, February 03, 2012 9:53 PMThe problem that I can see is pivot table summed all values of sub-tasks for each particular summary task. Seem like pivot table doesn't pull a summary row which I assign Project number or Project Type. So, I can't filter a value by those criteria. Do you agree?
Saturday, February 04, 2012 2:35 AM
Maybe I'm missing something but that's what summary lines do, they sum values of the subtasks.
Pivot table data that is generated by visual report default templates is pretty massive, that is, the amount of Project data that is stored in the OLAP cube is large unless the user edits the template to only include a few select fields. The pivot table that results from my sample file is shown below. I filtered the pivot table data to only show the values for "projA". All values in the filtered table agree with those in the grouped Task Usage view in Project. The Excel chart looked a little funky, (I expected a cumulative type curve), but that's because I didn't attempt to re-structure the chart.
So to answer your question, no I don't agree. My bottom line is, play with the pivot table data. If you just can't get it to do what you want, use one of the other two methods I mentioned, VBA or copy and paste.
Saturday, February 04, 2012 9:16 PM
Below is how I setup in MS Project. In Project Number column, I only assign Project number to the most top level of each project (that's the only way I can do filter in MS Project to only show top level project of all projects). For Project Group, I assign Group name on every row of each project.
When I select fields to show in Visual Report. Project number shown as (dimension) but Project group is not. What is the reason behind this?
See below for Visual Report I produced. I can filter by Project number (the one that has dimension); however, all shown as "Null". So, my guess is only sub-tasks are exported to Pivot table.
For Project group, I have to right click on Task field to select this property to show in a report. My understand is because this field is not dimension, so It's only work as a property of a task and I can't filter information based on this field.
Please let me know what you think.
Sunday, February 05, 2012 7:02 PM
I don't understand your statement about only assigning the project number to the summary line because, "that's the only way I can do filter in MS Project to only show top level project of all projects". I replicated your structure and can easily filter only the summary level lines using the following filter
That being the case I see no reason why you can't roll the project number down to all subtasks.
What field did you customize to create the "project number"? In my sample I use Text1 for the project number and text 2 for the project group. Neither of those are dimension fields.
Does that help?
Sunday, February 05, 2012 8:18 PM
Project number will not be always assigned at Outline level 2. Users have flexibility to assign at any level as long as it represents the most top level of each project.
Project number is Text1 and PRoject group is Text 2. I don't know why only Project number is the only one that has dimension. I didn't do anything different on this field.
Since all other custom fields I assign values to every row, so in Pivot table it should appear the same way as Project group. How do you filter to only show PRoject A?
Monday, February 06, 2012 5:42 PM
The first part is easy to fix. Simply change the second part of the filter to look for a value greater than "0" in the Text1 field. Then it won't matter which outline level your users enter the project number, just as long as it is on a summary line.
For the second part, are you using Project Server? I don't do Project Server but if you used enterprise Text1 and non-enterprise Text2 than perhaps that why one is a dimension and the other isn't. If you are not using server, then try a different extra text field for the project number.
For the third part, as I said my knowledge of pivot tables is very limited, but if you resolve the text1 issue (i.e. so it's not a dimension) then I think the filtering of the pivot table data will resolve itself.
Tuesday, February 07, 2012 2:14 AM
For your first response, it still doesn't fix the problem. Because if I filter to show all the summary line, I will end up getting so many summary tasks both my top level project and other summary tasks within a project. How do I know which row is the most top level of each of my project?
For your second response, I don't use PRoject Server. Other custom fields (10+ fields) don't have dimension.
For your third response, according to a picture that you posted, can you tell me how to do filter Text1 to only show Project A?
Tuesday, February 07, 2012 5:20 PM
Okay I'm confused. In your post on 03 February you state that you enter the project number into summary lines at outline level 3. So I gave you a filter to use, except I used outline level 2 in error. But then in your post on February 05, you sate that your users can enter the project number into any outline level as long as it is the top level. So I gave you a modification to the filter to only look at summary line with a project number entry. Now apparently that isn't good enough. So what exactly do you have? It may turn out that the entry "rules" for the project number are simply too loose to be able to use a filter and a VBA approach may be required.
I can't explain why you see extra field Text1 as a dimension in visual reports. I looked through several of the visual reports templates for Project 2010 and in no case were any of the extra text fields shown as a dimension. For lack of other explanation, I suggest you pick another text field.
This is how I created the filtered pivot table shown below for your sample file.
1. Select the Earned Value over Time report and add Text1 and Text2 as selected custom fields
2. Run the report and selected Cumulative Work as the sum value for the table
3. Add Text1 and Text2 properties
4. Select column "G" and go to Home/Editing/Sort & Filter
5. Select the filter icon from the drop-down
6. Hit the filter icon in the column "G" header
7. Uncheck the (select all) and select only "A"
If you also select (blanks) in the filter you will see summed rows for all projects.
- Marked As Answer by Mimi Laongpanich Wednesday, February 08, 2012 2:51 AM
Wednesday, February 08, 2012 2:51 AM
Sorry to confuse you. I think in my case, even though I'm trying to make a rule that users should assign PRoject number at level 3, it's still possible that they do not follow the rule. So, what I did is to prevent those exception cases.
I tried pivot table filter per your suggestion but i did a little bit of change. I use Text 2 as a filter field. So, when I select only Text 2, pivot graph will only show for Project A.
Thanks so much for your help.
Wednesday, February 08, 2012 3:35 PM
You're welcome. It's been a long haul but I'm glad we finally got to a resolution. It sounds like your users need some re-training.
Friday, May 11, 2012 12:23 PM
I am using Project 07, Professional, non-server based. When I add the Text 1 (Task Usage View) field and then select as a custom field for a visual report, it does not appear in the resulting Excel Spreadsheet or pivot table. Any suggestions?
Friday, May 11, 2012 12:46 PMModerator
Your query is answered on your other post, please refer it.