Forum Discussion
How to Display Blank field using custom field
- Okay I've got something that appears to work.
1. In the Resource Usage or Resource Sheet customize the Resource Text1 field with this formula and check the option to "use formula" for task and group summary rows::
IIf([Budget Cost]<>"",round([Number1],1) & "%","")
2. This macro will populate the Resource Number1 field with the desired percentage data
Sub CustomBud()
Dim Den As Single
Dim CFF As String
Den = Round(ActiveProject.ProjectSummaryTask.BudgetCost)
CFF = "iif([budget cost]<>"""",[budget cost]/" & Den & "*100,0)"
CustomFieldSetFormula FieldID:=pjCustomResourceNumber1, Formula:=CFF
CustomFieldPropertiesEx FieldID:=pjCustomResourceNumber1, Attribute:=pjFieldAttributeFormula, _
summarycalc:=pjCalcFormula, GraphicalIndicators:=False, AutomaticallyRolldownToAssn:=False
ViewApply Name:="resource sheet"
ViewApply Name:="Resource Usage"
End Sub
This was challenging.
John
dear John
My humble apologies for getting you frustrated over this issue that I am trying to explain. I will provide the screenshot as well hopefully this will help. I have create a View of my report and I will explain what I want to do.
I have a col Budget Cost which is NOT a custom field it gives all the Budget allocation as defined in the Resource Sheet. %Budget is the custom field which is now of type Text (as suggested in your earlier reply so that I can display blank/empty cell for those cells that I do not have any budget values during my percentage calculation. This is working the way I want now.
In order to calculate % tages I need to know the total of the Budget Cost column which is $34,500 + $38,000 + $70,400 + $28,100 + $15,000 = $186,000. I am now manually inserting $186,000 in the formula in my custom field to get the %Budget figures.
My question is how can I get this col sum of Budget Cost to avoid hard-coding $186,000 in the custom field.
Lastly, your last statement "You cannot create a custom field formula for assignment rows. However, you can use VBA to create a macro that will calculate assignment row data and can also roll it up to the resource or task rows." - is they any youtube video that I can watch to understand what you meant here or any article that I can read or any example that I look at and understand this.
Once again appreciate you taking a lot your time in looking at this issue I have.
Rgds
KR
1. In the Resource Usage or Resource Sheet customize the Resource Text1 field with this formula and check the option to "use formula" for task and group summary rows::
IIf([Budget Cost]<>"",round([Number1],1) & "%","")
2. This macro will populate the Resource Number1 field with the desired percentage data
Sub CustomBud()
Dim Den As Single
Dim CFF As String
Den = Round(ActiveProject.ProjectSummaryTask.BudgetCost)
CFF = "iif([budget cost]<>"""",[budget cost]/" & Den & "*100,0)"
CustomFieldSetFormula FieldID:=pjCustomResourceNumber1, Formula:=CFF
CustomFieldPropertiesEx FieldID:=pjCustomResourceNumber1, Attribute:=pjFieldAttributeFormula, _
summarycalc:=pjCalcFormula, GraphicalIndicators:=False, AutomaticallyRolldownToAssn:=False
ViewApply Name:="resource sheet"
ViewApply Name:="Resource Usage"
End Sub
This was challenging.
John
- Hi John
Thanks a lot for the solution. This awesome! I guess if things don't work the normal way we can always do some programming to solve it. I am not very familiar with VB and I got your code to work in my project file. I need to understand some of the variables that you used which are MS Project dependent. Is there a site I can go to see all possible MS Project's variables that I can use to manipulate data?
One last thing I need to know is that if I want to display all my fixed cost items that are assigned to some Tasks ( fixed cost column) on the same report I attached previously (which is more of a resource level cost information) is that possible? Is there a work around?
Or is it a rule that we cannot combine Resource level data with Task level data period!
Once again thanks a lot for your help. Much appreciated.
Best Rgds
KR- What you really need to understand is the Project Object model. You might look at the following:
https://docs.microsoft.com/en-us/office/client-developer/project/project-home?redirectedfrom=MSDN
To answer your other questions, with VBA you can combine any Project (e.g. project, task, resource, assignment) data to form a report. Depending on thr format sometimes it is possible to create the report in Project, as I did for you in this scenario, but normally I find it much more flexible to export Project data to Excel to create virtually any custom formatted report.
John- Hi John
Thanks again. Until I hit another problem I will post another discussion. Until then take care.
Cheers!
Stay Safe
