Forum Discussion
Production % Complete - Based on Actual Cost Vs Baseline Cost - Displayed in Visual Report
Hi everyone, In my previous post below, I was looking for a way to show me how many of the active subtasks under a summary task were complete: Post Location on site: Home → Project → General ...
Scheduler_SPP,
Wow, that's a lot of explanation but a quick read says you are trying to do some complex operations. The basic operations are easy enough but become complex in the format you want.
In your final notes you ask about a field name for a summary line. That field name is simply "summary" and it's value is boolean, "yes" or "no". However as I explained in one of my responses to your original post, custom field formulas in Project can only operate on data for the task row. To include data from other rows (e.g. summary) a combination of extra fields must be used, as detailed in Ignacio's process.
In your current request it appears you want to timescale the calculated value for your percent production cost in the Visual Report. Although custom field values can be included in Visual Reports that can only appear as single values, they cannot be timescaled (i.e. custom fields in Project cannot be timescaled).
So, you asked for advice on how to achieve your goals. My advice is to use VBA and I've done stuff like this for years for other users. With VBA you can get exactly what you want. It won't be a pivot table but it can be in the exact format you want in an Excel spreadsheet.
That's my input, others may jump in with their thoughts.
John
Wow, that's a lot of explanation but a quick read says you are trying to do some complex operations. The basic operations are easy enough but become complex in the format you want.
In your final notes you ask about a field name for a summary line. That field name is simply "summary" and it's value is boolean, "yes" or "no". However as I explained in one of my responses to your original post, custom field formulas in Project can only operate on data for the task row. To include data from other rows (e.g. summary) a combination of extra fields must be used, as detailed in Ignacio's process.
In your current request it appears you want to timescale the calculated value for your percent production cost in the Visual Report. Although custom field values can be included in Visual Reports that can only appear as single values, they cannot be timescaled (i.e. custom fields in Project cannot be timescaled).
So, you asked for advice on how to achieve your goals. My advice is to use VBA and I've done stuff like this for years for other users. With VBA you can get exactly what you want. It won't be a pivot table but it can be in the exact format you want in an Excel spreadsheet.
That's my input, others may jump in with their thoughts.
John
- Hi John, I always appreciate your quick and detailed responses, thank you very much!
I understand what all I am asking is a lot to ask of MSP (Desktop). I had to ask the question to verify if it was possible or not, but I had the feeling that the functionality within MSP (Desktop) just won’t be able to give me what I am looking for.
My first thought was using VBA to take the pivot table data, arrange it in a database raw data type format on the next sheet, then recreate new pivot tables or a layout on the next available sheet and have the full functionality of Excel to be able to do the calculations I am looking to achieve. My VBA knowledge is zero and I would need to teach myself how to do this, but if I can achieve this, the type of templates I can create, and detailed reporting would add a lot more value to the existing data within the project schedule.
I had searched for BI solutions and like RFromm suggested and came across Power BI which I see would only work on the online/web versions. My first job was financial reporting, using some locally designed software that would interrogate the database and by using certain parameters, dump the data from the database in Sheet 1 of Excel (RAW DATA) and thereafter, multiple sheets could use this data to display informative reports.
Many thanks for your response once again.- Scheduler_SPP,
As I said, one VBA macro in Project could do all the calculations, and export the data to an Excel Workbook in whatever format you need.
John
.jpg){kind=link}
%20Overspent.jpg){kind=link}
.jpg){kind=link}
%20Overspent%20+%20Delays.jpg){kind=link}