Webinar Recap: Using Custom Fields and Graphical Indicators to Analyze the Quality of Your Schedule

Please find below a transcription of the audio portion of Ira Brown’s session, Using Custom Fields and Graphical Indicators to Analyze the Quality of Your Schedule, being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions, such as those steps included in product demonstrations. You may watch the live recording of this webinar at your convenience.

Kyle: Hello, everyone, welcome to today’s MPUG webinar, Using Custom Fields and Graphical Indicators to Analyze the Quality of Your Schedule. My name is Kyle and I’m going to be the moderator today. And today’s session is eligible for one PMI PDU in the technical category. And the activity ID for claiming the session is on the screen now.

Kyle: And like all MPUG webinars, a recording of this session will be posted to mpug.com shortly after the live presentation ends. And all MPUG members can watch the recordings at any time and still be eligible to earn the PDU credit. All the sessions you watch on demand can be submitted to your webinar history. And the live sessions you attend are automatically submitted.

Kyle: Within your history, you can print or download your transcript and certificates of completion including the one for today. And you can access that by logging into mpug.com and click on the My Account button. If you have any questions during today’s session, please send those over at any time using the chat question box on the GoToWebinar control panel. We’ll give time to answer those questions for you throughout the session today.

Kyle: All right, we’ll go ahead and get started. We’re very happy to welcome back Ira Brown, today. Ira is the founder and president of Project Widgets. A leader in the field of project management and is a recognized Microsoft Project expert. Project Widgets is well known for offering add-on products for Project and Project Online, as well as for creating custom solutions that meet their client’s unique business requirements.

Kyle: They even have several free downloadable widgets available on the website, projectwidgets.com, that you can begin using right away. So with that said, I’d like to welcome you back, Ira, and I’ll hand it over to you to get it started with today’s session.

Ira Brown: All right. Thank you very much, Kyle. And I’d like to welcome everyone to our session for today. Let me go ahead and share my screen. And this is kind of a fun session. And I think you’ll find it to be very useful. And the topic is Using Custom Fields and Graphical Indicators to Analyze the Quality of Your Schedule. And many of you may be aware that there’s this feature available within Microsoft Project that allows you to create graphical indicators. But if you’ve never worked with it, it can be a little bit intimidating.

Ira Brown: So we’re going to kind of take the whole mystery away from using graphical indicators and custom fields and formulas and things like that. And we’ll show you how it’s done. And then, I think, all the different tools that we’ll be developing today, together, will be really useful for helping you further analyze your schedule and be able to spot problems in your schedule easily by just creating that visual indicator that will point you directly to any kind of problems that you need to address. So with that, let’s get into this.

Ira Brown: Just a moment here.

Ira Brown: Okay. So just one quick slide about Project Widgets. As Kyle mentioned, we are a Microsoft Project solution provider and I’ve been on this now for well over 25 years. Our whole business revolves around the use of Microsoft Project and helping our clients get the most out of it. And in particular, we specialize in creating custom solutions for our clients, just to make Project a whole lot easier to use and to get it to do things that it just can’t do out of the box. Or if it can, it just will take you hours and hours and we can give you a button to click on and a couple of seconds you can get done would otherwise will be very difficult.

Ira Brown: And our custom solutions frequently turn into our add-on products that we offer for Microsoft Project, which we refer to as our widgets. And you can learn more about them on our website. And as a matter of fact, speaking of the widgets, at the end of today’s session, we’re going to be giving away three free copies of what we call our driving path widget to help you understand… if you click on a task, you’ll understand what are all the other tasks that are causing this task to be scheduled the way it is. So it helps you kind of understand that easily without having to do a lot of deep analysis of your schedule.

Ira Brown: So we’re going to be giving away three copies of that and all you have to do is at the end of the session, just shoot me an email with your contact information. So your name and what company you’re with and how we can get in touch with you. And we will pick three people to win a copy of the widget at the end of the session. Also, we’re going to be covering quite a bit of material today. And if you send me an email at the end, I will be happy to send you the presentation that I’m going to be showing you as well as all of the formulas and graphical indicators that we’ve created during the session. Just shoot me an email, I’ll make sure that I get that right out to you. So with that, let’s review our agenda.

Ira Brown: So we’ll start off with just an introduction with how to work with custom fields. And then we’re going to get into some really interesting topics here, how we can specifically analyze certain aspects of our schedule. For example, tasks that have not been baselined or maybe, are there any summary tasks that have resources assigned, or show me all the tasks that are on my project’s critical path that have an unfavorable variance, or show me any task that is in need of an update. Right?

Ira Brown: So we’re going to look at a variety of different topics. And we’ll be able to create, not only are we going to be creating all these different kinds of graphical indicators, but we’ll put them all in one view so you could actually apply this view any time to any schedule and easily see all of these different aspects to the schedule. Okay?

Ira Brown: And then, I’ll finish up by showing you some other things such as, it’s great that you’ve created these all for yourself, but how would you share these with other people within your organization? And then what about people that are using Project Online or Project Server, do any of these things apply? And how would you get them out to those platforms? So we’ll cover that as well. Okay? So with that, let’s build a widget. Okay?

Ira Brown: So the first one we’re going to look at is how we can create a graphical indicator to identify any task in our project that has not been baselined. Okay. So I guess, the question first is, why would you want to do something like that? Well, first of all, let’s just review quickly the concept of a baseline. After you’ve built a schedule and everybody kind of agrees to that schedule, and this is what you’re going to be using to manage your project, normally, that is when you want to capture a baseline for your project. That’s basically what you’re saying that we want to have a kind of a snapshot of what our project looks like at that point in time so we can then see how our project changes over time and identify what are referred to as variances in the schedule. Especially unfavorable variances when things are falling behind schedule. So in order to be able to know when things are falling behind schedule, you have to have a baseline to do that.

Ira Brown: Well, if you have captured a baseline, but then maybe have gone on to add other tasks into your plan. Any newly added task would not be baselined automatically. So we might want some easy way to identify when a task has not been baselined. So that’s what we’re going to start off with here. So how do you go about doing this?

Ira Brown: Well, whenever you’re building a graphical indicator, a graphical indicator in Microsoft Project is associated with a custom field. Now, you may notice that the very first column that you normally see in Project is this column. It’s actually referred to as the indicators column. But these are the built-in indicators that are part of Microsoft Project. So you can see things like when a task is complete or if a task has a note on it or if a resource is over allocated, those are the built-in indicators. We’re going to be creating custom indicators.

Ira Brown: So any kind of custom indicator that you create is always associated with a custom field. Okay? So what we’re going to do is we’re going to go ahead and right click right here on the Task Name column, and I’m going to choose Custom Fields. And this will bring up the custom fields dialog. And this is where you can specify, “Well, do I want to build a task field or a resource field?” Or if you’re using Project Online, for example, you could build a project level field. So in our case, we’re going to be building task fields. Okay?

Ira Brown: And then the next question is, what type of fields do you want to be able to create, what type of custom field? So if you drop that down, you’ll see there’s all different types of custom fields, really, different types of data, such as text and date and cost and flag. Well, we’re going to be building a lot of flag fields today. And a flag field is a yes or no field. It’s either going to have a value of yes or it’s going to have a value of no. By default, all flag fields start off with a value of no, but then, you could change it to yes.

Ira Brown: So let’s start off by selecting Flag here. Okay? So notice when I do that, these are all the built-in flag fields. You have 20 of them that are available. All right? So we’re going to start off with Flag1. And we’re going to go ahead and rename this field by clicking the Rename button. Okay? So I’m going to call this Tasks Not Baselined. Okay? So that’ll be the new name for the field Flag1. It still knows it’s Flag1 behind the scenes. But then, if you actually go to insert this into one of your tables, you can actually find it by its new name, which is kind of convenient, right? So we’re going to go ahead and click OK.

Ira Brown: And now, what we’re going to do is we’re going to where it says custom attributes. Normally, there is no custom attribute. It’s just plain old yes or no and you get to set it yourself. But what we’re going to do is we’re going to put a formula into Flag1. So we do that by clicking the formula button. All right? And there’s all kinds of options here. Now, if you’ve ever worked with Excel or Access, there are some similar kinds of formulas that you can use. But these are formulas that are specific to Microsoft Project. And you have access to the various Microsoft Project fields that can be included in a formula.

Ira Brown: So we’re trying to figure out whether or not a task has been baselined. So one of the ways you can do that is you can evaluate either the baseline start or the baseline finish field for a task. So, for example, if the baseline start field had a value of NA, that would tell you that that task has not yet been baselined. Okay? So that’s what we’re trying to understand is, does the baseline start field have a value of NA? If it does, then we know this task has not been baselined. That’d be a good way to test it.

Ira Brown: So what we want to do is, and this is a formula that will get us that answer quickly. So we want to start off with the baseline start field. So if you’re not quite sure how to refer to a particular field, you can just drop down where it says field here, right? Notice, it says insert a field. And then you kind of have to go and try to find the field you’re looking for. So we’re looking for baseline start.

Ira Brown: So I’m just going to guess that maybe that’s going to be with the date fields. And look at that. There it is. So up the top, there’s baseline start. And notice, there’s a whole bunch of baseline start fields, right? Because you actually have your original baseline start, which is just called baseline start. But then, you have baseline 1 through 10, and all these others as well. Well, the one that we want is just the one called baseline start.

Ira Brown: So I’m going to go ahead and pick that from the list. And notice, when I do that, it simply puts the field in and encloses it in these brackets, right? So you could have actually just typed that in once you’re more familiar with working with the formulas. Okay? So baseline start equals… Okay. And now, we’re going to use this special function that’s available within the formulas. And this is one that you might not readily find on your own. So that’s why I think that you’ll find this particularly helpful.

Ira Brown: What you have to do is you have to type in this special formula here or the special command called ProjDateValue. And by the way, that is available if you go to the functions. It’s under this date/time, so that’s another way I could have just inserted it right there, just by picking it, ProjDateValue. Okay? So we’re going to use the ProjDateValue. And then, we’re just going to put NA in quotes, and then enclose it in parens, just like that. So that’s the formula you can use basically to evaluate whether or not the baseline start field contains NA.

Ira Brown: Now, it’s not good enough, in this particular case, just to say baseline start equals NA, you have to actually use that ProjDateValue. So here’s what’s going to happen. Remember, we’re putting this in a flag field. So this formula here is either going to evaluate to a true or a false. It’s either baseline start equals that or it doesn’t equal that, right? So it’s either true or false. So true or false, in a formula is really the equivalent to yes or no, in a flag. So if it’s true, it’s going to be yes. If it’s no, it’s going to be false. Okay.

Ira Brown: So all I have to do at this point now that I put my formula in is click OK. You will get this warning message that will tell you that existing data in this field is going to be deleted. Because now, all of the values are going to be calculated by the formula. So what this is basically saying that is if you were previously using Flag1 for any other purpose and you might have set those values manually to yes or no, this formula is now going to override any of those values that you might have put in there previously. So it’s just a warning message. Normally, it doesn’t make any difference at all. And we’re just going to go ahead and click OK. There we go. So now our formula is in there.

Ira Brown: Now, the good news is, if I typed anything incorrectly or had any kind of syntax problem, it would have actually given me an error message right here. But because it did not, I know that I don’t have any kind of errors with my formula right now. So the next thing you want to think about is, do you want this to also apply to summary tasks? So in other words, do we want to know if our summary tasks have not been baselined? Well, I think in this case, we do. So we’ll say, for summary tasks here, let’s also use the formula. Okay.

Ira Brown: So, we’re almost there. So right now, all we’ve done is the formula is going to turn that Flag1, either into a yes or to a no. But that doesn’t yet get us the graphical indicator. The way we go about getting the graphical indicator is by using the graphical indicator’s button. So I’ll show you how that works. We go ahead and click that button. Remember, we’re setting it as it pertains to Flag1 right now. So we’re going to click graphical indicators.

Ira Brown: And when you’re setting your graphical indicators, you can say, I want to set a graphical indicator for a nonsummary row. Right? So that’s basically a task that’s not a summary task. Or I only want my indicator to apply to a summary row, or I only want it to apply to a project summary. If we’re talking about a project online or a Project Server project.

Ira Brown: So in this case, we’re going to just say nonsummary rows. And here’s the test. Right? We’re going to say, basically, you want to think of it this way. If the value of this field equals the word yes, because that’s what it’s going to get calculated to either yes or a no because that’s all a flag field can be is yes or no. So if it’s equal to yes, as a result of our formula, then let’s put some kind of graphical indicator on it. Notice, these are all the ones we can choose from.

Ira Brown: Okay. So I’m going to go ahead and pick a graphical indicator. And I’m going to use, in this case, I’ll be using lots of red flags. But notice, you can have red lights, green lights, yellow lights, all different kinds of colored circles here. All these different ones. So I’m going to pick the red flag. Okay? And then, what do I want to show for the summary row? Well, I’m going to just say summary rows will inherit the criteria from nonsummary rows.

Ira Brown: So that just basically says, also show that same red flag for summary. If I wanted to show something different on the summary in terms of the indicator, I could do that as well. So we’re going to go ahead and click OK. And we’ve just created our first indicator. Let’s click OK again. Remember, it’s on Flag1, right? We’re going to click OK again. All right. So that’s nice but I don’t see it anywhere yet. Well, there’s one more thing we need to do. We have to actually insert that into our table.

Ira Brown: So now, we can just say insert. So I’m just going to right click on task name and I’ll say insert column. And I can insert it either by looking at the flag field, see how in parens it says tasks not baselined or I could actually start typing in T-A-S-K-S. And notice, it’s there as well. So actually, two different places in my list of fields. So now, we have our indicator there. I’ll just make the column header a little bit bigger. There we go. Tasks not baselined. So we would see a red flag if there’s any task in the schedule that’s not baselined.

Ira Brown: Well, at the moment, I don’t have one. But let’s insert a new task. And let’s put this something like present to MPUG. And notice already, you can see that it’s working. There is our red flag that tells me that this task is not baselined, right? In fact, notice there’s that NA right there in the baseline start column, so it’s doing exactly what we want it to do. What you might want to do, by the way, is you may want to have that flag centered, right?

Ira Brown: So if you just right click on the column header, go to field settings. And then you can say, align the data, center, just like that. Of course, you can also change the width and that sort of thing. I’ll just click OK. And notice that my indicator is now centered. And we’re doing great here. So let’s test this a little bit further. What if I now set my baseline for this task.

Ira Brown: So what I’m going to do is I’m just going to highlight this task. I’m going to go to project, set baseline, then I’ll choose set baseline again. Notice, it shows me that I last set my baseline on this date right here. I’m going to say let’s set the baseline just for selected tasks, not the entire project. And then we’ll click OK. And notice when I do that, my indicator goes away. Right? So that’s exactly what I wanted to happen. And we’re doing great. So we now know how to use custom fields to create a graphical indicator to show us if we have any tasks that are not baselined in our schedule.

Ira Brown: And what we’re going to do now is we’re going to continue to build more of these kinds of indicators, analyzing all different aspects of the quality of our schedule. So by the time we’re done at the end of this session, we’re going to have about seven or eight different indicators that are going to be analyzing the quality of our schedule. Okay, so let’s do one more. Now, the next one we’re going to do is how we can use a graphical indicator to identify tasks that do not have dependencies. Okay? So I guess, the first question is then, why would you want to do that?

Ira Brown: Well, the reason you want to do that is because normally, when you build a schedule, you really take advantage of the scheduling engine of Microsoft Project to allow your scheduling to be dynamic so that if the duration changes at one task, you don’t want to have to now manually change all the different start and finish dates in your schedule. You would like the schedule to recalculate based upon things like durations changing and any dependencies changing and constraint dates and calendars, right?

Ira Brown: So you normally want to make sure that the tasks in your project have dependencies. And most tasks in your schedule will have a start link, right? So something that’s driving its start. And then in turn, the task will be driving some other tasks as well. So you’re going to have basically a predecessor and a successor for a task.

Ira Brown: Now, not all tasks will have a predecessor. For example, one of the early tasks in your schedule is one of the first things that you’re doing. Like for example, this task right here. Since this is basically the first thing that’s happening in our schedule, it’s not going to have any predecessors but we would expect it to have a successor. Then as we get to maybe the last task in the schedule, it’s not going to have any successors but we would expect it to have a predecessor. So this is going to be a way of identifying tasks that are not being driven by a predecessor or don’t have a successor. So how are we going to do that?

Ira Brown: So once again, we’re going to use a flag field to do that. So let’s right click and we’re going to say custom fields. We’re going to go to our flag fields. And we’re going to use our next available flag field which is going to be Flag2. I’m going to go ahead and rename this. I’m going to call this Tasks without Links. Okay? And now, we want to be able to put a formula in there. All right. So let’s talk logically about what that formula might look like and then we’ll see how we do it.

Ira Brown: So basically, what we want to say here is something like if the task doesn’t have any predecessors and if the task doesn’t have any successors, then set the flag so that I can see a red flag. In other words, we want to set it to true. Remember, true is translated to a yes automatically. Okay? So that’s basically the logic. If the task doesn’t have any predecessors.

Ira Brown: Now, you could say, if it doesn’t have any predecessors or doesn’t have any successors, right? That’s one way to do it. Or you might want to look for both, doesn’t have any predecessors and doesn’t have any successors. Maybe you could come up with maybe two different versions with this. But let’s see how we would go about building it. So how do you incorporate that kind of if logic into a formula?

Ira Brown: Well, to do that, we have this special function. We’re going to go hit function going here, and under General, and it’s called the IIf function. It’s the IIf function with an extra I there, I-I-F. So let’s go ahead and select it. Okay? So as you can see, this IIf function says, let’s evaluate some expression. And I’ll show you what that expression’s going to be in a moment. But about something to do with predecessors and successors.

Ira Brown: So if that expression evaluates to true, then we’re going to want to set this flag to true. We would put in the word true here. And if the expression evaluates to false, then where it says false part, we’re going to put in the word false. All right? So what will this expression look like? Well, what we’re going to say is if the predecessors field… Well, how do I get to the predecessors field?

Ira Brown: Well, I would imagine I can hit the drop down here. This will get me to all the fields and now, I have to start looking for it. And after a while, you will get pretty good at finding them. So the question is, where is the predecessors field? Well, I’m not sure. It’s probably not cost, right? Is it a date related? I don’t see anything there. Is it duration related? I don’t see anything there. Flag? Nope. ID code? Nope. Ah, there it is. Sometimes you have to hunt for it a little bit. All right?

Ira Brown: And there it is in the list and I’m going to pick it. And notice, when I do that, all it really bought me is it put the word predecessors in the bracket. So that way, you know you haven’t misspelled it but you could just as easily type it in. So I can say if the predecessors… How do you say does not equal? Well, it’s just simply less than or greater than, that’s how you signify not equal. Actually, in this particular case, I want it to be equal. So I’m just going to use the equal sign. So if predecessors equals, remember, there’s a field in Microsoft Project called predecessors, right? So when a task doesn’t have any predecessors, that field is blank. How do you signify blank then?

Ira Brown: Well, if the predecessors equals, then to signify blank, you just put in a quotation mark, followed by another quotation mark, right? That is a blank string, as they call it. So if predecessors equals blank and I’m going to use or in this case, or. And now we’ll do the same thing for successors. Well, now, I know where to find it, field, ID code, successors. So predecessors equals blank or successors equal blank. Same thing, two double quotes. Then true part is the word true. False part is the word false. And that’s it, folks.

Ira Brown: So this says, if the predecessors is blank, meaning the task doesn’t have any predecessors, or successors is blank, meaning there are no successors, then set this flag field to true, which will then get translated to yes. Or set it to false, which will then get translated to no. Let’s click OK. There’s that warning again. If there’s any existing data in that flag field, it’s going to get overwritten by the formula. That’s fine. We’ll click OK. No syntax errors, that’s good.

Ira Brown: In this case, I’ll just have this apply to nonsummary, so I won’t put it on the summaries. And now, we just have to put in the graphical indicator. So I’m going to go to graphical indicator and I’m going to say equals. And remember, we’re not looking for true here, we’re looking for yes. So if it equals yes, and then what image do we want to put in there? So I want to stick with that same red flag. Click OK. Remember, this is now in Flag2. Click OK again. Now we’ll insert Flag2. There it is. And there you have it.

Ira Brown: So now, this is going to show me any task that is missing either a predecessor or successor. Now in certain cases, it’s really just information. Like it’s not necessarily a problem. Like we know, yeah, these first couple of tasks don’t have a predecessor, not a problem. And probably, as I get to the end of the project, there might be some tasks that don’t have a successor, like here’s my very last task, right? We do that right click, scroll to task, right? No successor, not a problem. But maybe this one is a problem, right? So this will at least draw to your attention, where you can evaluate it to determine if it’s something that needs to be addressed.

Ira Brown: So, for example, here, provide training to team. So what you want to ask yourself is, could I really do this at the very beginning of the project? Is there anything that has to happen first, right? Well, I would say defining team roles would need to happen before I can provide training to the team, right? So that’s a problem but that brought it to our attention, right? So I’m going to… I really should give that task a predecessor. So check this out, folks. Here’s something that you may not have seen before.

Ira Brown: If I hold my mouse over this task right here, right? I want to make this test the predecessor to provide training to team. Well, I can now click and drag. You notice as I do that, you see that little link there. I’m going to drop that right on top of that task right there and then let go. And notice that that task now has the predecessor, right? Now, the flag didn’t go away because there’s currently no successor to it as well. But what if I didn’t want to define my tracking process until I provided the training?

Ira Brown: Well, I can give this task a successor as well by dragging it to here. And when I do that, notice my indicator goes away. So see how useful these are, right? It draws it to your attention. You can then analyze if anything needs to be done. And then once you’ve fixed the problem, if there is a problem, then the indicator will go away.

Ira Brown: Now, what you could do, maybe you might want to have a different flag, a different color perhaps if it’s only missing a predecessor and then a different color if it’s only missing a successor and then, a different color if it’s missing both, right? Those kinds of things can be done as well. But once you get more familiar with these, then you can do extra fancy things like that. All right.

Ira Brown: So what I’m going to do is I’m going to go ahead and save my project because these indicators are actually getting saved with my project right now, along with the custom fields. Okay, so make sure you save your project as you go. And what I’m going to do at this point is I’m going to just take a short break and open it up if anybody has any questions. So Kyle, anybody have any questions so far?

Kyle: Hey Ira, I don’t see any questions in the queue just yet. But just to remind everyone, you can submit those using the chat box there and we’ll answer those live, during the session.

Ira Brown: All right. Well, in that case, let’s keep going. So the next one we’re going to take a look at is using a graphical indicator to identify summary tasks that have dependencies. Okay. So a little bit of a different twist compared to what we just did. Now, why would we want to do that? Well, one of the things, generally, in your project, there are some exceptions. But for the most part, you don’t want to have dependencies on summary tasks.

Ira Brown: Normally, what you want to do is you want to create your dependencies between subtasks. Having summary tasks that have dependencies will often obscure the details of your critical path and give you less visibility as to what is actually causing a task to be scheduled the way it is.

Ira Brown: Like, for example, if you noticed this task is scheduled a certain way but you’re not quite sure why? And maybe it’s because the summary task for this subtask had a dependency but you can’t readily see that by looking at just the subtask, right? So that’s a good reason why you might not want to have dependencies at the summary level.

Ira Brown: So how are we going to do that? Well, we’re going to also once again use a flag field. We’re going to go to our custom fields. And we’re going to go to flags. And we’re going to go to the next available flag, which is Flag3. And we’re going to rename that. And let’s call this instead, Summary TaskswWith Links. Okay? And we’re going to want to put a formula in there.

Ira Brown: Now I’m going to save myself a little bit of typing by going ahead and just grabbing the formula that I have, my little notepad file here. And I’m going to copy it in. And that way, I just don’t have to type it in each time. But let me let me just take you through it. And remember, as I’ve mentioned in the beginning of the session, I am going to make all of these formulas and all these indicators available to you.

Ira Brown: In fact, I will email you this project that will have everything in it that you need. So just shoot me an email at the end of the session. I’ll make sure I make my email address available and you can just send it to me and I will send you this presentation, as well as the project file. Okay? So what does this say?

Ira Brown: So there’s that IIf command again, right? So if the predecessors field is not blank, right? In other words, the task has a predecessor or the successors field is not blank, meaning, the task has a successor and summary equals true. Well, summary equal true means that the task is a summary task. So that just naturally gets set by Microsoft Project, that if the task is a summary task, summary will be equal to true. And if the task is not a summary task, summary will be equal to false.

Ira Brown: So if all of this criteria is met, now notice that we actually have the predecessors and successors evaluation contained within its own parens right there. Right? So what that says is it’s going to evaluate this first. So, in other words, does the task have a predecessor or does the task have a successor? It only has to have one of them. And if it has one of them, it’s going to consider that part of the expression to be true. But it’s got to meet one more criteria and it’s a summary test. Right?

Ira Brown: So if it meets both those criteria, then it’s going to set the flag to true or set the flag to false. In other words, yes or no. That looks like what we want. We’re going to go ahead and click OK. And remember, we want to be able to use the same calculation for summary rows. Because in particular, we actually want this to apply to summary rows. We’re going to say use the formula. All right? Now, we’re going to go to our graphical indicators.

Ira Brown: Now in this particular case, we don’t want to use any graphical indicators for a nonsummary row. We only want the graphical indicators to apply to the summary row. So we’ll choose summary rows here. And we’re going to say, if it equals yes, just like we did for the others, then let’s set some sort of flag or some sort of image.

Ira Brown: Well, just to mix things up a little bit, why don’t we pick a different image this time? So, I don’t know. How about we use a red dot this time. And then we’ll click OK. And then we’ll click OK again. Remember, this is Flag3. So let’s insert Flag3 into our view. There it is. And sure enough, we have some summary tasks that have either a predecessor or successor.

Ira Brown: Now, if we take a look. You always want to test your formula by looking at the data. So you can see, sure enough, this particular task has a predecessor, has a summary task. So it would probably be better to not have the predecessor directly on the summary. So it looks like the predecessor is task nine. Now, what’s interesting is that task 11 looks like it has that exact same predecessor. So we probably don’t even really need it, right?

Ira Brown: So I’m just going to go ahead and delete that value right there. Just hit my delete key. And as soon as I do that, my indicator goes away and I fix the problem with my schedule. So we’re doing great here, folks. Let’s save this and let’s move on to our next quality check that we’re going to do. And the next one, let’s go to the PowerPoint and see. Okay, using a graphical indicator to identify summary tasks that have resources assigned. Okay, well, why would you want to do something like that?

Ira Brown: Well, there’s a couple of reasons. Ordinarily, you don’t assign resources to a summary task because it impacts the roll up behavior for the resources that are on a subtask. So for example, let’s say we have something like task A here. And let’s say I’ll go over to my resource sheet and I’m going to put on here, Ken. This is like a shout out to my colleague, Ken Jamison, who helps put some of these materials together.

Ira Brown: So I’m going to go back to my Gantt chart. And I’m going to assign Ken to task A. All right. So that works great. I’m going to split my screen here and I’ll give Ken 16 hours’ worth of work on this task. Okay? And then, let’s say I realized that maybe there’s more work on this task and it’s going to be something like 80 hours’ worth of work.

Ira Brown: So I decide, you know what, because it’s 80 hours, I probably ought to split it out into more detailed subtasks. So I’m going to put another task here, and I’ll call this task x and task Y. And let me make these auto scheduled. And then I’m going to indent these guys, which will turn task A into a summary task. There we go. And now, I’ll assign Ken to this task and I’ll assign Ken to this task. There we go. Let’s click OK. Let’s insert the column called work.

Ira Brown: So you see what just happened. We have Ken for eight hours on task X. And we have Ken for eight hours on task Y, that’s 16 hours. Wait a minute, that doesn’t seem to be rolling up properly. Right? Well, because task A also has work on it, right, which is a summary task. So you see, that’s really why you don’t want to do it. You normally want the work for the subtasks to roll up to the summary level, right? But sometimes, when you turn a task that was a subtask into a summary task, it brings the work along with it. So this is a problem. So let’s see how we can maybe use a graphical indicator to fix this problem.

Ira Brown: So I’m going to go back to my project that I was in. Let’s go back to our custom fields. And let’s use another flag field. This time, we’ll use Flag4. And let’s call this Summary Tasks with Resources and click OK. Once again, I’m going to go ahead and grab my formula so I don’t have to type it in. And now, we’ll just paste it on in. Let’s review it though. If summary, what this says, if the task is a summary and resource name, remember, that’s the resource names field. You’re all familiar with that, right?

Ira Brown: So remember, if the task has one resource assigned to it, then that resource names field is not going to be blank, right? So this says if it’s a summary and resource names is not blank, then set this to true. Otherwise, set it to false. We go ahead and click OK. Click OK. Remember, we want this to apply it to a summary task so we say, use formula. We now go to our graphical indicators. We want this only to apply to summary rows. And we say equals yes. And then, what image do we want?

Ira Brown: Well, we want to show that it has resources. So let’s see. Let’s pick a good one. Is there anything… How about something that has a face on it that is bad, right? So how about a red face, click OK. Click OK. And let’s insert that in our view. Insert Flag4, Summary Tasks with Resources. There you go. And it already found one, right? Form team appears to have a resource on it. Well, let’s prove that it does. There it is, right? Don’t want to have a resource on the summary normally. Let’s delete it. And when I delete the resource, you can see right away that the indicator is gone. Okay?

Ira Brown: Let me just center that and then we go to field settings. And then we go to align data, center. And we’ll do the same thing for these others, field settings, align data, center. I want this to look good for you. And field settings, align data, center. Okay, cool. All right. So we got some pretty good indicators here. Let’s keep going.

Ira Brown: The next one I want to do is any task that have negative total slack. Okay. So why is this important? Well a task that has negative total slack is basically, this is preventing you from hitting your targeted project finish date. Essentially, this is how many days you are behind schedule. Okay. Just want to do a quick sound check here. I got a little message here. Kyle, are you still hearing me okay?

Kyle: Yeah, I can hear you now. I think it cut out for just a second but I think you’re good now.

Ira Brown: Okay, yeah. Just wanted to double check. And you’re still seeing my screen, right?

Kyle: Yup, looks good.

Ira Brown: All right, perfect. Okay, so using a graphical indicator to identify tasks that have negative total slack. Okay. So again, this is something that you probably would want to be aware of so that we can try to address the situation. So let’s see how we’re going to do that. Okay. So once again, we’re going to use a flag field. So let’s go ahead and go to our custom fields. And let’s go to our flag. And Flag5 is the next one that’s available. Let’s go ahead and rename it. And let’s call this Tasks with Negative Total Slack. Okay.

Ira Brown: And once again, I’ll grab one of my formulas right here and let’s paste it in. There we go. Click OK. And we’re going to want this to apply to a summary task also. And now, we’re going to go to graphical indicators, equals yes. And then, we just decide what kind of image we want to put on it. So maybe negative total slack. How about a negative sign that’s red? How about that? That’s pretty good, right? Click OK. Click OK. Let’s insert the column. There it is. And looks like we got a few of them in there that we have to deal with. Now, again, how do we prove it?

Ira Brown: Well, if I insert the column called total slack, there it is. And there’s a negative number. Okay. So it looks like it’s doing what we want it to do. Perfect. All right. All right, let’s do another. This hour is going by quick, folks. We have two more we want to do. So the next one I think is a really, really useful one. This is called using a graphical indicator to identify tasks that have an unfavorable variance and are on the project’s critical path. Okay, so what does that mean?

Ira Brown: Well, if a task is on your project’s critical path, by definition, that means it has zero days of total slack. Meaning, that task is directly impacting the finish date of your project. So if that task was to slip even by one day, that’s going to push your project’s finish date out. So, as a project manager, you’re particularly interested in those tasks that are on your project’s critical path. But if it also has an unfavorable variance, meaning it’s finishing later than planned. In other words…

Ira Brown: Remember, we talked about setting the baseline at the start of the session? So let’s say you set a baseline for your schedule. And that task was supposed to finish on September 15th but it’s currently scheduled to finish on September 20th. So that means it’s five days later than planned and it’s on your project’s critical path. So since it’s already late, there’s a decent chance that it’s going to keep getting even more late. So you want to be able to identify tasks that are already late and on your project’s critical path. So let’s do that one.

Ira Brown: So let’s go back to here. Once again, we’re going to use a flag field for that. So we’ll go to our custom fields. We’re going to go to Flag6. Let’s rename it. All right? This one, I’m actually going to copy the names as well because there’s quite a bit of words there. Unfavorable Variance on Critical Path. All right. Now, actually, I just realized that I want to do this a little bit differently. No, I’m not. I’m sorry. No, that’s fine.

Ira Brown: Now, let’s go ahead and grab the formula. We’ll copy that. Let’s paste it on in. And let’s just analyze it for a second. So there’s the IIf again, right? IIf critical, meaning, it’s been calculated to be on your project’s critical path. And the finish variance field, that’s the field that’s automatically calculated by Microsoft Project, whenever the finish date is later than the baseline finish. Then it’s going to be greater than zero.

Ira Brown: So if both of those are true. I should say, if it’s critical and the finished variance is greater than zero, then calculate it to true or calculate it to false. We’re going to go ahead and click OK. Click OK. We’ll have this apply to a summary task as well. For our graphical indicator, if it equals yes. Okay? So we’re going to show some kind of image there. How about we use a red light? And we want that same thing to apply to a summary. So we’ll just say summary is going to inherit the criteria from the nonsummary. That’s perfect. Click OK. Click OK. Let’s insert the column. And this is going to be unfavorable variance on the critical path. There you go.

Ira Brown: And let’s just do a quick test. Let’s insert the column called finished variance. There it is. I would say that’s a little unfavorable. Wouldn’t you? So that tells you that if that number is greater than zero and it’s on the critical path… Well, how do we know what’s on the critical path? Well, there’s a field in Microsoft Project that’s automatically calculated called critical. I can just insert it here. I don’t have to keep it in there. I can just look at it just to test my formula. There’s my yes, greater than zero days. There’s my indicator. So it looks like it’s working perfectly well. All right. Let’s go ahead and center that. And we have one more. Okay?

Ira Brown: And the last one, what we’re going to do, now this is my favorite one of all. And I will tell you that I’ve delivered this session or similar session previously and I’ve actually ended up working with some folks as clients who’ve attended these sessions. And I actually have seen people that are using this next one. They got it from this MPUG session and they actually implemented it. And they’re using it operationally within their organization. And I was really pleased to see that.

Ira Brown: So this is one that I think is particularly beneficial. It’s my favorite one. It’s called update required. It’s going to tell you whether or not a task is in need of an update. And your goal, and you can teach all your project managers this, that you never want to see any kind of flag field in this field. If you see it there, that tells you this task is in need of an update. You got to update it. And your goal is to not have any flags and I’ll explain why in just a moment. Okay?

Ira Brown: Now for this one, we’re going to use a text field rather than a flag field. So let’s insert the column. Sorry, let’s go to custom fields rather. And we’re going to use Text1. And let’s rename it. And let’s just call this Update Required. Okay? And I’m going to copy in my formula. Now this one is a little bit more complicated. And we’re also using something that we haven’t seen before. This is called the switch command. All right. Let me just see if I can explain this to you a little bit.

Ira Brown: So the switch command, basically, is allowing you to evaluate things. It’s very similar to IIf, but it’s when you’re really evaluating multiple things, not just one thing. So, let me take you through the logic. What this says is if today’s date, that’s what this day field means here, date. If today’s date is greater than the start date of the task and the percent complete of the task is zero. That means that what? That the task should have started, right? Because today’s date is greater than what Microsoft Project is showing as the start date for the task. But there’s no indication that the test started because the percent complete still says zero. Then what that says is if this particular portion of the expression is true, then put the text and the text field should have started.

Ira Brown: Okay, now, it’s going to evaluate another pair. If today’s date is greater than the finish date of the task, right? And percent complete is less than 100, that means the task should have finished. So in that case, right? Should have finished in the text field. And now, the next one says, if today’s date is less than or equal to start and then we just put a blank value in the text field, then we essentially don’t have a problem. And if today’s date is less than or equal to the finish, then we don’t have a problem. We just put a blank value in. All right?

Ira Brown: So we’re going to go ahead and click OK. We’ll apply that to the summary row. And now, we just have to do our graphical indicators slightly different. What we want to say here is if the text field equals and then you actually have to type in the text, should have started. All right? Then we’re going to put in a different graphical indicator. Maybe we’ll use one that we’re shading the left portion of the circle, but if it equals, should have finished.

Ira Brown: Because remember, that’s what’s in the formula. Then we’ll use that guy right there. Click OK. Click OK. Let’s insert it into our table. So this is Text1, update required. And there you go. So this is telling you that this task… Look, it was supposed to start September 1st and be finished by September 1st. So let me mark this as 100% complete. Click OK. And notice, the indicator goes away.

Ira Brown: So basically, you want to tell all your project managers that they need to update every single task that shows that update is required until you no longer see any indicators in that column. Then you know you got a nice schedule. It doesn’t need to be updated in terms of any start or finish dates. Okay? One last topic to cover briefly and then we’re going to go ahead and open it up for one final round of questions here. And that is these other considerations. How do you get this to other people within your organization? And how do we get this out to people that are using Project Online or Project Server?

Ira Brown: Well, in terms of getting it to other people in your organization, if we go to our project here, we’re going to go to something called the organizer, file, info, organizer. And if we click on the fields tab, notice these are all the fields that we just created with all the formulas in them. All right? That’s where they live. They live within this project that we created them in. So if you save this project, you can then walk over to your colleague… well, maybe not so much today, but you could email it to your colleague. And then you get on teams or go to meetings with them or Zoom meetings. And then have them open up this project, have them go to the organizer, just like I did now, right?

Ira Brown: And then what you would do is you would basically copy all of these fields into their GlobalMPT, just like you see here, just by clicking copy. And once you do that, these will be available for use with any project that you then work on from that point forward. I think, you could do it for yourself as well, so that these fields are available for any project for you, not just the one that you created it in. Okay, there you go. So now, that’s really all you have to do.

Ira Brown: And then one final thing, if we’re using Project Online. Let me go to the Project Widgets demo environment here. There we go. If you’re familiar with Project Online, all you really have to do, and this applies to Project Server as well, not sure why. Let me do a quick refresh here. There we go. All we have to do is go to server settings, enterprise custom fields and lookup tables. And we can take any of those formulas that we created. And I can create a new field here, task update required or whatever we’re calling it, right? And I can go and I can say this is a task level field. And it’s a flag field or in this case, it was a text field. And I can go to formula and I can just paste that exact same formula in right there. And now, this is an enterprise field. This will become available to any user on your system that’s using Project Online or Project Server. Okay? I know we’re just about a minute over here. Does anybody have any questions about anything that we’ve covered?

Kyle: Thanks, Ira. Would you be able to share your email for those who may need to jump off the session?

Ira Brown: Yes. Let me go ahead and do that. Thank you for the reminder. So just yeah. So as Kyle said, here’s my email address right here, ira.brown@projectwidgets.com, there’s our website. Shoot me an email, I will send you a copy of this presentation. I will send you a copy of the project that has all the formulas in it and all the indicators so you can use that on any of your projects. I apologize for going a couple minutes over. Kyle, does anybody have any questions?

Kyle: We do have a few questions here. And just to remind everyone, there’s a screenshot button at the top of the viewer window. So you can take a quick screenshot of what you see on the screen here and have Ira’s contact info saved right to your computer.

Kyle: I had a question from Manny asking, “Will the indicators work in a master project with subprojects linked?”

Ira Brown: Will the indicators work in a master project with subprojects linked? Absolutely. Yeah, because if you think about it, if you don’t do a master project, you’re still going to have those same fields available in the master project, start and finish, et cetera. So, yeah, everything that we covered here should work fine in a master project.

Kyle: Okay, great. Charles was curious. He’s saying, “So I have many columns to show the different flags, which could take up a lot of screen real estate, is there any way to consolidate those items?

Ira Brown: Sure, I mean, first of all, I think what I would recommend is why don’t you create this as a special view? It’s not something that you’d have on every time you’re using Microsoft Project. You don’t necessarily want these fields front and center. But my recommendation would be, you can still… Oh, I guess you can’t see my screen at the moment. Is it okay, Kyle, if I just share my screen a second again?

Kyle: Oh, we can…

Ira Brown: Oh, you are seeing it?

Kyle: Yup.

Ira Brown: That’s right. Sorry about that. My recommendation would be, let’s actually just take this table that we’re in right now and let’s go to more tables. And let’s copy it and let’s call this something like My Schedule Quality table, right? So I’d give it a whole new name here. Click OK. And then create yourself a whole new view.

Ira Brown: Again, you go to here and you say more views. And you say I want to create a new view. And I’m going to call that schedule quality. And I want it to be based on a Gantt chart. I also want to use the schedule quality table that I just created. I don’t need any group. I don’t need any filter. So I’ll just say, maybe active tasks, and then click OK. And then click Apply.

Ira Brown: So now, we have a whole brand new view that we’ve just created that’s just for analyzing the quality of your schedule but your regular views do not need to have all of these columns in or maybe you might just want to have like the update required, but not necessarily all the other ones. So that’d be a way to do it.

Kyle: Okay, great. Well, thanks, Ira. Sorry for going over, folks. Just want to make sure we answered a couple of those questions there. And if you have any additional questions, we’ll be sending a link with the recording of the session to you. And you can comment directly on the video and we’ll be able to answer questions there as well. So with that said, I’ll hand it back to you, Ira, to close us out.

Ira Brown: All right. Well, thanks very much, everybody. I do appreciate everybody attending today’s session and hanging in there. We went a little bit over. But I hope you found the materials to be really, really useful. Again, feel free to shoot me an email. I’ll get you all these materials. And if there’s anything in Microsoft Project that you feel like I might be able to help you out with, especially through some of this kind of automation, and any kind of custom solutions that you may need, that’s all we do with Project Widgets. I’d love to hear from you. And please let me know how we can help you. And thanks again, everybody, for attending today’s session. And thanks, Kyle.

Kyle: Thank you, Ira. We really appreciate your time and sharing this with the community. I know everyone’s going to find this very useful. I’d already seen a lot of great comments rolling in. And for those of you that are claiming the PDU, I’ll get that back on the screen for you now. Today’s session is eligible for one technical PDU and you can claim that with the activity ID shown. And if you missed any of the session and would like to go back and review anything that Ira shared, as I mentioned, a recording will be posted to mpug.com a bit later today. And you will receive an email with a link to view that.

Kyle: And just a quick update on our upcoming sessions. I chatted over a link where you can see our full calendar and register for upcoming webinars. But our next two are on the screen here. Next week, Lynette Reed will join us for Finding a Perfect Match, which is a look at both sides of the interviewing process. So that’ll be a great session for those looking for a job or hiring candidates. The following week, Praveen will join us for the Ten Best Microsoft Project Tips for Beginners.

Kyle: Both of those are open for registration now. So we hope to see you there. And that does it for today’s session. So once again, thank you, Ira, for presenting today. And thank you, for everyone that joined us live or is watching this on demand. And we hope you have a great rest of your day. And we’ll see you back for the next session. Thanks.


Watch the on-demand recording


Share This Post
Avatar photo
Written by Community
Where Project Managers and Microsoft® Meet.

Leave a Reply