Microsoft Project Custom Reporting with Excel – Transcription

Please find below a transcription of the audio portion of Jeff Chamberlain’s session, Microsoft Project Custom Reporting with Excel, 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 recording of this webinar at your convenience.

Melanie: Welcome to Microsoft Project Reporting with Excel. During today’s session, we’ll be sending an always appropriate and adorable MPUG plug to the audience member that asks the third question. This is going to be easy as I know everyone is very interested in reporting with Excel. Please just post your questions to the question chat window. I have it up here on the screen now. And I will present those questions to Jeff, as we go today.

Melanie: I’d like to introduce our expert, Jeff Chamberlain. Jeff is a certified product project management professional and Certified ScrumMaster with over 20 years of experience spanning IT, Telecom, healthcare consulting, and the outdoor adventure industries. He has managed technical and administrative programs both domestically and worldwide, including Brazil, Venezuela, Italy and Ukraine. He has spent much of his career helping teams operate at the higher level of effectiveness.

Melanie: We welcome Jeff. Jeff, I am going to make you our presenter now.

Jeff: Thank you. Okay. Let me share my screen. Do not watch the man behind the curtain just yet, here we go.

Jeff: Thanks for the introduction. My name is Jeff Chamberlain as indicated. We’re going to go through a bit of a patching project for me, if you will. This was created out of a need for some great reporting out of Microsoft Project. And we’ll get into that a little bit more in a minute. As an interesting point about me, I got into project management because of Microsoft Project.

Jeff: I working for Hughes Aircraft at the time and my boss came out and said, “Does anybody know how to use Microsoft Project?” And I didn’t really know how but I volunteered. I’m not sure why. But I ended up kind of figuring it out and over the next couple of months sort of started really enjoying using it. So, that led into a bit of a career as a project manager. So I have been using it for a number of years. And I don’t know everything about it, I definitely am always willing to see if there’s new or exciting features that I am unaware of.

Jeff: So feel free, if I’m not doing something the way you would do it, post something in the chat. I’m always looking for new ways. So, as we talked about, this is a training where I’m going to go over some custom reporting with Excel about an hour long, and there is the activity ID for you.

Jeff: So, let’s talk a little bit about what you need to do from a project standpoint prior to exporting and or getting into Excel.

Jeff: So, some of the best practices I like to talk a little bit about are there are things in a project where we call the status date, which is the little date you set up in the field, which essentially base as your reporting. So, of course, that can be a week prior or a month prior or whatever it is. But what that does a lot of times is it establishes a date that’s different than today, the current date. So, keep in mind that some of the formulas I’m going to be using are relevant whether you use a status date or a current date, and I’ll try to point those out as I go along.

Jeff: So, the other thing you might want to need to know is that the current date will always require you to save the file. So, if you open a file and you quickly run a report, you may not see that that particular current date is accurate until you save the file.

Jeff: In Excel, it does not receive project flags as visuals, it receives them as the values they’re set up to be. So, if you have a red flag, for instance, that shows up when a one occurs in your custom field, it will come over as a one. Again, we’ll show you some examples of that.

Jeff: And in Project, I have not been able to figure out a way to sort by visuals, flags, checks, things of that nature. And then, durations in many fields translate the minutes in order to accurately calculate a business day. Another thing I’ll point out is that Excel often receives the duration as 30D for like 30 days or 30W for 30 weeks. And that is not a number as considered by Excel. So, there is something I will be showing you that works to help you as part of your export and import to help address that.

Jeff: So, the goals are going to be basically separated into Microsoft Project and Microsoft Excel. We’re going to talk a little bit about the custom columns and how we create them for easily exporting your schedule. We’ll talk a little bit about some of the formulas that I use that are helpful or help columns in Excel and the columns that you really need to export for Excel. And then finally, we’ll talk a bit about custom views and filters so that you can return back to the same look and feel of your project schedule when it’s been updated very easily.

Jeff: In Excel, we’ll talk a little bit about what’s great about project reporting Excel project reporting. I’m going to talk a bit about PowerQuery, if anybody is not familiar with that. It’s a great on board tool for more recent versions of Excel. If you have an older version, you can plug it in, it’s an add on. Talk about tables and visuals. And then finally, talk about the data refresh.

Jeff: So, custom columns. I have several custom columns that I use as part of this reporting. So, I’m not going to go over each of these formulas. But I am going to point out for instance, the days until start days until finish or day since completed. These are all duration fields and I’m using a status date here. So, again, as I indicated earlier, this is only calculated from that status date that you’ve applied in the project itself. Now, you’ll see that I have another two custom fields. And you’re going to say, “Well, these exist already in Microsoft Project. Why are these custom?”

Jeff: Well, the reason why is when you export these into Excel, this NA that occurs as a default and it’s obviously in an earlier schedule, you’re going to see more NAs than you have actual dates. It tends to push errors into Excel. So, what I like to do is this is either an NA, excuse me, this is either an actual date or it’s a blank which is much more compatible for the export of the data into Excel.

Jeff: Key deliverable, this is different than the milestone or the milestone date or critical and that critical is only a field that tracks the critical path or anything with zero lag. This allows you to set milestones that you feel are important that need to be reported on in your actual report. More about that later [crosstalk 00:08:26]. I’m sorry, there’s a question?

Melanie: Just one quick comment. I think you have the presenter view of your slideshow up so people are seeing…

Jeff: Okay.

Melanie: There’s just a little confusion on the slide presentation, if you could just show the…

Jeff: Okay, let me see if I can pull up Presenter View. This is a different look and feel for me. So, is there an icon that I should not be…

Melanie: If you go to display settings and swap Presenter View in slideshow, right, that should give you… I think that’ll give you what you want.

Jeff: All right. Apologies about that. Does this make it a little bit easier to see them?

Melanie: Yeah.

Jeff: Okay, perfect. All right. So, again, you’ll be able to see these a little bit more in detail as we go along. And then, finally, the RAG which is a red amber green status, it calculates the difference between your status date and start date.

Jeff: So, a couple of other columns. Hub, work stream and PM, these are all specific to a project. Hub in my mind is just some sort of identifier. You can call this whatever you want. It might be something like channel or work group or these are just divisions that I use to separate out organizational structure. It could be client, it could be internal or external, whatever you want.

Jeff: And then PM, in some of my projects, I have multiple PMs. So, I’ve added this column in. Again, it’s optional. This is just something I want to demonstrate when you do have multiple PMs or multiple responsible parties that you want to report on.

Jeff: And then these are some other fields that you will see as I go along. Summary, I use the summary row, because in Excel, when you export from Microsoft Project, the summary rows come over. Basically, they bring their values over as sums. So, if you want to remove the duplicate data, you have to essentially exclude your summary rows and all your formulas. And this is how I do that.

Jeff: Unique ID allows me to identify, it creates a fixed ID for Excel. Active, you have to turn on, it’s pretty straightforward. And it’s used in order to remove or ensure that a task is included. And then that critical, I also include that so that if you want to include values, and you’ll see why, some values that are on your critical path and you may want to report on. That is included in the spreadsheet that I’ve built.

Jeff: Okay. Custom view. Let me pull up my screen. Can everybody see this okay? I don’t know. I mean, you guys are muted, but go ahead and put in the chat if you need any greater…

Melanie: It looks good.

Jeff: Okay. So, all I’ve done is I’ve created a custom view. And if you’re familiar with Microsoft Project, and forgive me if this is redundant or stuff you already know. But one of the things I do is under Custom View, once I’ve set up what that view looks like, and I name it, I always use brackets to kind of tell the setup that this is for me. I have other projects that I manage here and these are all my views. And I know they’re mine, because I’m the only one that uses brackets. And it just helps me kind of swarm to the top and I can quickly access them. It’s just sort of a best practice in my mind.

Jeff: My custom view, in general, is I like to display for this particular report, these fields up here. The order needs to kind of stay the same quite simply for logical purposes. But in the event that you build this on your own, you can put this in whatever order you want. Some people want the task name further to the left. For me, I do it this way because of my calculations and how I pivot my tables, if you will. And then as we go further down, you’ll see there’s my tasks, you’ll see the… So, I’m separating my plan, my calculated days versus my planned and actuals.

Jeff: And then, my various flags occur under key deliverables and my red amber green. So you’ll see here that these are all basically visual symbols. Now, this is a total sample file. If anybody wants my materials, I’m more than willing to email those to you. We’ll talk a little bit more about that in the future.

Jeff: Okay. Presentation mode. Go back into, how do I do that, there we go. All right. So, we talked a little bit about that. Now, we’ll go back and I’ll bring up the filter. The filter, basically, I’ve created a couple of different filters for this. So, again, apologies if you know how to create a filter. But what I always do for mine is I always start off with my active and display only my active fields. You don’t have to build it that way. If you do have your active equals yes, you can essentially remove that active column. I do it both ways, just simply to ensure that I can at least pivot on inactive tasks if I want to show them.

Jeff: And then, you can do a custom date range. And I’m not sure if anybody’s familiar with how you add a custom date range. I’ll show you that real quick. This was actually something I learned relatively recently and it’s been kind of really helpful for me. So, if I come down and I grab my date range filter, this is the one that I’ve pre-built. It’ll ask when I want to start.

Jeff: So, let’s just say I want to filter out a couple months ago. I grab here and then after. Let’s just go out here, hit okay. And that filters on the date range that I just selected. So if I only want a 90-day view or a shorter snapshot for my RAG report that I’m recording, I would use my shortened date range view. And the way that works is you’ll see, and sometimes Microsoft flips it off. But your finish and start is greater than or equal to, and then you basically run a show task that start before or after and it’ll have a question mark and an end quote, I’m sorry, it’ll have a quotation, end quotation, and then a question mark. That forces a dialog box to occur in your view. Okay.

Jeff: Let’s go back to the presentation. All right. So now, one of the things that I do in my project is I’ve created various custom fields. I use a lot of default fields because they’re obviously helpful. But this is one of my custom fields. And as you can see, as you go through, you’ll see these little plus symbols at the end of my field. I do this for me so that I know it is a custom field. And it’s really easy to come in and start naming how you want it to look like.

Jeff: So, in this case, the field name, I’m using Duration10. That’s the customizable field, and the label days until start and I put a space with a plus key. That way, when I’m going through, I can easily see, “Oh, this is going to have a formula or a drop down box or something of that nature.” If you come into the custom field setting, you can see here, you can rename it here as well, because of course Microsoft likes to give you a whole bunch of different ways to do the same thing. This is an easier way of doing it. Actually, it’s just all done in the formula.

Jeff: So, come down here, you’re going to create the formula itself. And in this case, this is what I wanted to talk a little bit more in detail. This formula basically is a project date difference. It’s a standard formula in Microsoft Project that allows you to take two different dates and give you the delta in an absolute value format. So, standard in this case is using the standard calendar. So, I can change status date to, I believe, the other switches current date. I always forget it. I rarely use it because I always like to use status date.

Jeff: But you could also do start date, end date. And this is the way you build the basic formula for a duration to tell the difference between date A and date B. And then of course, using the standard calendar. You could also go in, if you have a custom calendar, you would essentially name it in this particular formula. I’m going to go pick up one that has another type of formula in it so we can look at that as well.

Jeff: Okay. Days until finish will be pretty similar. Days since completed is another value. That’s a little bit more complicated. And this is the one where I start building in instead of just a standard duration where it gives me * D as in eight days or 8W eight weeks, this is a little bit more complicated. So, I’m starting with my actual finish and using a project date value calculation and I’m having the NA population. The NA display is removed so that ends up being just an integer, if you will, just a straight value.

Jeff: Then basically, I take the other calculation which is the actual finish, the status date and the calendar. And here I have to do a divided by 480 in order to get dates because as a default, and I just kind of lost how it does it, but basically Excel is going to give… I’m sorry, Microsoft Project is going to return the value in hours, I believe, and this conversion gives you the dates. I have to look at this formula, but it’s very straightforward.

Jeff: Okay. And then again, you’ll see here, it’s just a straight value. It’ll be empty. Let me take my filter off. It’ll be empty if there’s no completed date yet. So, a check will be if the days until finished and this are the same.

Jeff: My hub is a custom formula. And in this case, it’s a lookup. So for me, I have three lookup values, these are very easy to enter in as you see fit. It’ll just be a basic drop down box. The naming convention is from a text value. It’s the same way with the work stream and the PM.

Jeff: I’m not changing the task name, the start and finish. The actual start and finish are the same type of values. All I’m doing here is the same kind of formula, just in this case, suppressing the NA. The key deliverable is a yes or no field. So, this is going to be a little bit different. So, it is a flag field. And the graphical indicator requires a conversion. So, let me cover this real quick.

Jeff: So, you choose the flag field that you want to use. It doesn’t have any attributes, so in other words, you’re not going to create a formula, you’re not going to calculate it based upon anything. This is really just a yes or no. So, what I’m doing is I go down to the value to display. So, in my actual criteria, I say, if it equals yes, if this field equals yes, I want it to display my checkmark. So, what’s going to export to Excel is going to be yes. And what’s going to show here is a green check.

Jeff: Okay. I don’t complicate this because some people like to say, “Well, if it’s not a key deliverable, why don’t you put a red X?” You can do that, it’s not going to benefit, it’s not going to subtract from your data export. But what it does do is add data. So for me, if you don’t need it, there’s no sense in really putting it in. Predecessors, pretty straightforward, we know what that is. I really don’t even use this in my export. I just have it in because it’s something I use as a visual.

Jeff: The summary field I put in towards the end, this is a yes or no. And you can tell, summary row is just that. It’s an auto calculated field. It’ll tell you if the row is a sum to value field and you can tell that because they are bold. And then my RAG status. Let me show you this one. This one is a formula, but it’s also a flag. So, it’s a combination of the two that I’ve previously shown you.

Jeff: It’s a Duration field. And what it’s doing is it’s showing you the difference between your project status state and the start date. So, I want to kind of indicate for my RAG report, my milestones, I want to know what’s important to me is when they are starting. So, this formula really has two flavors to it. You could put this as your start date or your finished date. So in other words, if your task is finishing late, you might want this to be a red status. If it’s starting late, you might want it to be a red status. It’s entirely up to you.

Jeff: So I’m simply explaining at this point, I’m just doing a differentiation between my status date and my start date. So, does anybody have questions about that? I want to make sure that that’s pretty clear. You want to put something in the chat or? Okay. So now, it gets a little bit more fun and that I’m doing a formula based image or a value based image, if you will.

Jeff: So, the first one is, if I am within zero days of my start date, I want that green or that circle to be red. The next one, if it’s within range of zero to 60 days, I want it to be amber. And if it is greater than 61 days, I want it to be green. So logically speaking, what that means is, if I’ve got 60… Or I’m sorry, I said 60 and I meant 61. If a task is 61 days out, I, according to my project values, I’m okay in my reaction level. I have plenty of time to deal with it, it’s not a critical level.

Jeff: These values may change for your particular project, you may want a 30 day look ahead. So your green might be 30, your within might be zero to 29. And obviously, less than zero is adjustable as well, like if it’s less than 10 days, or what have you. I think the zero days for red is pretty indicative of a project that you already knew is going to be late, but you can stretch that out to the way you see fit. Now, keep in mind, I am not rating my non-summary rows for how late they are. I’m essentially just doing my regular rows. You can do summary and you can do project summary.

Jeff: What this does is it calculates based upon the various fields. It’s really not helpful in an Excel calculation. So, always use your non-summary rows in this case. Okay. Let’s go back to our PowerPoint presentation. I think it’s just going to tell me to open up my Excel.

Jeff: So, let’s go into the Excel setup. All right. Excel. The first question I always get is, why don’t you just build these reports in Microsoft Project? And I do now and I’m trying to navigate more and more to Microsoft Project. But one of the kind of ongoing complaints people say is, “I don’t have Microsoft.” So data democratization is a phrase that I’ve been using for the better part of the 20 years that I’ve been in project management. It gives everybody the ability to have the data. It’s also kind of a burden because now everybody has the data.

Jeff: But at the end of the day, more people know Excel and are comfortable with it than people that know Microsoft Project. The ability to sort by visuals in Project is a bit of a pain. Not that I haven’t tried, it just hasn’t happened. The tool availability, we talked about. Now, it just makes sense. So, it seems like some of the processes here may be complex. But it’s definitely a one-time thing. Once you take the files, once you reach out to me and I’ll send you these files, you’ll look at them and you can modify them very easily for your one time case.

Jeff: And then, every time, once a week, once a day, once a month that you have to run this report, it’ll be minutes versus the initial setup, which is all said and done. The initial setup is complex. It may take you a couple days to figure it out. But it will save you a lot of time in the long run. And the process is automated and repeatable. You basically refresh the Excel spreadsheet and you have new charts and graphs. Any work that you do in Microsoft Project is just transported over as I’ll demo here shortly.

Jeff: So as an overview, basically, you create and prepare your project file. You build your columns. You add your formulas in and you save and publish your schedule. From there, you copy the schedule, you paste it into a specific sheet in the Excel spreadsheet I’m going to show you. It’s a raw data, basically paste. And then, you refresh. From there, I have built a PowerQuery that does the magic for you, which we will step through depending on how much time we have. I think, we’re running pretty well on time right now.

Jeff: I will kind of walk through the query and what it does and why it does it. And if you have not discovered PowerQuery yet it’s an eye opener. It really does. What we used to have to do with macros and splitting columns and manually, now incorporates a bit of SQL type work into your Excel spreadsheet.

Jeff: So, data transformations, this is kind of a behind the scenes view of PowerQuery. So, one of the things that happens is percentage displayed as a decimal when it comes out. These are little bad things that happen. That’s not a good thing. It has to be fixed. I’ve mentioned this earlier than MS Project exports the value in labeled days in the same cell. You can’t take zero days or 60 days and subtract it from 120 days and get another value. It’ll error out and it will not give you a value.

Jeff: And then finally, certain blank values come over as null or NA or other things as discussed. So, PowerQuery in conjunction with Microsoft Project or some of the fixes we’ve done, PowerQuery will fix these for you. I’ve also included some Excel formulas that I wrote. They’re not special. They’re not custom. They’re just things that I’ve pulled up. So, for instance, I have the ability to pull in the last modified field from a table that when you refresh the Excel spreadsheet, it says, “Hey, the last time you’ve updated this was this date.”

Jeff: We’ll go into that in just a minute. I have an if statement that allows me to toggle my stoplights. And I’ll show you those. I’m kind of excited about those. It took me a long time to figure out but my effort in this will lead to some ease of your use. And then, the same thing for the series. This is very similar to the if statement above. And it allows me to show you the red amber green filled circles for the actual stoplight.

Jeff: All right. Demo time. So, the first thing I’m going to do is this value. I have a little table of contents in here that I built that links to the actual slide, excuse me, to the sheet. So in this case, there is… It’s not a table as well, it is a table but it’s raw data. So what I’ve done is I’ve come over here and I’ve clicked. I’ve selected my schedule. I’ve come over to the task field and I’ve copied it.

Jeff: I come over to Excel. I grab this and I do a paste and I paste this match destination format. I’ll just do a control C or control V. You want to be able to match the destination format. This updates all of your data in here. And it makes sure that all of the fields you pulled in from that custom view and that custom filter have been pushed in, and they have.

Jeff: From there, a value, the values are then going to be updated through PowerQuery. Let me make sure of my metrics. Okay. So what I do is when I prepared the query, you have this data formula and you have PowerQuery, which is this get and transform area right here.

Jeff: So, I have a series of queries. I have the get schedule and I have this MPUG demo. So, I’m going to show you the MPUG demo one because that one’s kind of straightforward. There is a series of tables schedule, raw schedule and file info, these are all tables that are kind of built. This is one of them. What this does is it goes out to the directory where I have saved the actual files. And it looks for the date modified on the project field.

Jeff: So what this does is it tells me that on 10-08-2021, I updated this file. So, that’s really the last time the project file was updated and saved. It’s kind of an easy way to get this date right here. And I use this date in all my tables so that I can know that when I’ve done a refresh, I know that everything has been updated.

Jeff: Okay. So, the more complicated one is the get schedule. And I’m going to step through them. So, when you run PowerQuery, sorry if it seems like I’m bouncing around a little bit, there’s a bit of… Okay. When you run PowerQuery, what you do is you choose the table that you want to run the query on.

Jeff: So in Excel, you have two types of data. This for instance is a table, and I know that because it has a name, Demo One. It has this little table tools comes up, I can tell that it is a table versus just a raw field like data one, data two, column one, column two. This is not a table, this is just raw Excel data. So, if you wanted to convert this to a table, there’s a couple of ways of doing it.

Jeff: One of the easiest ways of doing this is to go control T. And then, it asks for your range, and whether or not you have headers, this one does not. But you’ll notice that this now adds a little header row that looks a lot like this and then it parses out your data. You’ll now notice that table tools now appears and the table name appears. So, I always like to work with tables in Excel, because it makes it a little bit more SQL like, if you will.

Jeff: All right. Now, going back to my raw schedule. From here, I essentially say, “I want to fix this table.” This, for instance, I want to fix this, I want to fix some of this other stuff. So I’m going to use PowerQuery to do it. So I do it from table then I’m going to build the query. So I’ve already done so and I’m going to step you through it.

Jeff: So, one of the first things that it does is, where did you get the data? In my current workbook, I’m just grabbing table one and I’m grabbing the entire contents. Then, it applies steps as you do them. So it’s going to change types. So in this case, there might be a value that I fixed. A change type indicates that, and you can see, maybe it’s hard to read. But all of these columns here, I changed them from one type to another.

Jeff: So for instance, the start date, I wanted to make sure that the table knows that it’s a date and a time, the finish is a date and a time. The duration, I had to convert it as a text, the key deliverable. These things are all formatted specifically so that I can now use them in Excel. And I only have to do it while I’m writing the query. You don’t have to do this every time.

Jeff: The only thing I did was I removed some columns. So, like task mode, I do not need it. Task mode is where it’s auto scheduled or manual scheduled, I’m not using that for my calculation. Then, I’m going to split some columns. All I’m doing here is I’m taking the various columns and I’m splitting them by a delimiter. So, for instance, in this case, it’s the red amber green column, I’m getting rid of some values there. I’m changing some more types.

Jeff: And this is kind of like a macro. As I’m stepping through, I’m removing columns, removing other. I’m going to split another one. This is an example of where days until start. I’m removing the days text. So here, let me see if it’s going to show you. I’m going to change that. The days until start. I have to show you. I have to find it. There it is. So days until start, you’ll notice that it’s now an independent number.

Jeff: And then, it pushed off the days into a separate column. But I don’t want that column. So, I’m going to remove it. Now, I’m going to go to the next one and rename it. I’m going to split that column. I’m going to rename that and format it to a number. I’m going to remove the days. Now, I’m going to remove another column. I’m going to change another. So you can see as I step through, what PowerQuery is doing is writing my query for me all the way through. And then I filter rows.

Jeff: So for instance, the summary rows, I don’t want those. I want them to be active but I don’t want the summary rows because all my calculations are based on the direct task values anyway. And then, I added an additional column at the end. And this additional column helps me do calculations in Excel. So, you’ll notice that it says, “Hey, if it’s less than zero, it’s red. Otherwise, it’s greater than or equal to.”

Jeff: There’s also a value of 60 if it’s Amber. If it’s greater than 61, it’s green. So, it matches sort of what the red amber green status in Microsoft Project does, I’m able to now do it here. And you’ll see, that column will show up as a red amber green. It’s actually a text type that I’ve added to the formula. Now, this is what it ends up really looking like.

Jeff: This is your query. And this is highly transportable. So, all you need to do is copy this and email it to somebody and they can bring it into their spreadsheet. All they need to do. I mean, there is literally no external data, it’s as long as their tables are named the same, table one, table two, the source in this case is table one. If it’s not, they just need to change it to what it is. If they call this table Jeff, they just edit the … In the advanced editor, they just name it whatever they want.

Jeff: Now you do a close and load. And it will come back. And it’ll refresh. And it’ll load a table that looks like this. And this is your actual raw, excuse me, this is your formatted data. So, you’ll notice that there’s start and finish dates, actual start and finish dates, durations, the predecessor is still in there. I don’t need that one. I could remove that column if I want to, whether or not it’s active. The RAG status, I’m sorry, the RAG, and then the RAG status. This is the number of days if you remember. And then the RAG status column.

Jeff: All right. Let’s take a minute right there, because there’s a lot that I just went through. Take a minute or two for some questions. We’re at 12:42. The next thing I’m just going to do is go through the graphs. So, do we have questions coming up?

Melanie: We do not have any additional questions right now, Jeff. You’re doing a great job. It’s all clear.

Jeff: All right. Well, I hope I’m not over complicating anything. But in any event, let’s go into the graphs. So, this is the dashboard in and of itself. It’s just a lot of data here. So, I’m going to break these down by what I call individual key points for what I would report on to, say, my manager. So you’ll notice there’s a lot of stuff here, a lot of data. So, the first thing I do is I do a RAG status to my boss.

Jeff: On the left is a project status, in the middle is the key status. And on the right hand side is critical path. The first thing that I always tell people is that Excel is not perfect. So for instance, this is a yellow status. And this is a green status. It’s kind of hard to tell, because those two colors don’t show up great. But you can tell this is definitely a red status.

Jeff: These are charts. And what they do is they pull data from this table right here. And it’s relatively straightforward. I’m going to tell you where this percentage comes in a minute. But basically, it says each one of these, it’ll say, if this value is between that zero and 60, if it’s between various percentage points, I want it to be NA. It’s the same way with red. If it’s between these various data points, which I’m going to show you in a table, I wanted to show you NA. One shows up when that is the matching criteria.

Jeff: So in this column, the total status light for this one is a project status. And what this means is my total project is status amber, because my percentages meet that earlier value, which I’m going to show you.

Jeff: The key status is the same way. These are my key flagged custom fields. These are not on the critical path, necessarily. But they’re events that I have flagged as being important to the project. Custom field, I’m counting them and I’m identifying which ones are running late or what have you. But in this case, none of them are. So, I figured that all my key values are green status.

Jeff: Now my critical path, same way, I take the critical path value that I’m exporting from Microsoft Project and I’m looking how many of my tasks are late and what is that percentage? So, I’m displaying those up here, 31% of my total is late, 29% for key is late, and 67% of my critical path is late. So that’s how this table works. But now I need to kind of take you back to the background.

Jeff: So, all of these values here come from this middle column. These are pivot tables. If you don’t know much about pivot tables, ask now, it’s a basic Excel table format. You basically select your data and you tell it what to display. There’s a ton of resources on Google to look at this. But in any event, I have a key milestone account, we have a total of 85 milestones, 58 are key, 27 are not.

Jeff: The red amber green key deliverables, I’ve got a count here of how many are in each category and what percentage that reflects. If they’re not key status, this goes into my total count for my … It’s basically a separation of this minus this as a validation. And then, my all key deliverables. I have my critical path count over here.

Jeff: And in this case, you’ll notice I don’t have an amber showing and it’s because none of them counted. If there was an amber value that appeared, it would show up in this row, it’s dynamic. And then over here, this is by PM. Okay. So this is how I calculated it. I actually did all deliverables plus to get my total project status. So that’s where the red amber green will show up. That’s where these percentages are.

Jeff: Okay. Now, back over to the RAG status. The calculation that I am basically creating here is whatever your comfort level is. I can show you where to adjust this. But some people might feel that 10% of their values need to be less than 10% before it’s red or less than 15%. So all of this can be adjusted through your tables. And that is a calculation here and through each individual pivot table.

Jeff: And if you look at the dashboard itself, what I did was I created a little helper table here that allows you to sort of point to that field. You can actually physically just build this table. So for instance, I’m saying that the percentage I want needs to be 31% or more for red, 24% for amber, or 46% green. So, that’s just my valuation. Again, it’s a comfort level to whatever you prefer for your statuses. And these can be adjusted up or down.

Jeff: So, the next field is the all milestones. This is just a basic table that I’m doing by Project Manager. In this case, they’re colored to the red amber green. And I have three project managers on this particular KPI on this particular chart. And basically, what I’m looking at here is I’ve got a project manager that the story this tells me is Kathy is a little overloaded. And probably one of the reasons why she has a bit of her tasks in the red status. Whereas me and Carolyn, we’re not quite as stacked up from a resource loading standpoint.

Jeff: So, I can just take a quick look at this bar chart and go, “Yeah, we need to kind of split off some of Kathy’s duties on to Jeff and Carolyn if possible. And that will give us a little bit of work balancing.”

Jeff: Same thing here. Oh, by the way, this is all milestones. And down here, this is key milestones. I guess I’m not responsible enough to have a key milestone. So Carolyn and Kathy, both of them have been provided with a basic workload here that you can see. Again, it just so happens that Carolyn all had two, two, two key milestones, so a total of six. Kathy has a little bit more variance in hers. Again, her red is a little high on this. Again, might want to point out that we maybe want to balance her work a little bit.

Jeff: As we go back over to the task counts, this table is, I think, there’s a lot to be said here. This table has two or three things going on. Number one, it’s a bit of a complexity. So, for me, as a project manager, one of the things I always build into my project schedule is a differentiation of duties. And it often is named whatever the client wants.

Jeff: So, I use hub and worked through here. I don’t know if anybody picked up on this really quick. But this is a music tour. This is the various people responsible for the events on a tour. So, one of the things I did was, this is a category of activities. And I like to throw this in because it’s really helpful. And you can tie it to the pin block, if you want. You can tie it to categories that are already planned out on the project.

Jeff: But this is really helpful visually for me because I can come in and I can categorize something. In this case, I kept it simple into these three areas. It’s either planning, implementation, or it’s go live. So in this case, it is a category of the task. Now, the word stream in this case is in my mind who’s doing it. So you can have a project manager that might be responsible for a work stream or a hub, or all. Or you can divide it out and they’re only responsible for a particular hub. But what’s really great is to differentiate these two because it tells you the type of task and who’s doing it. And this is why I went with this.

Jeff: Did I hear a question?

Melanie: Yeah. We have a question about understanding the RAG count more. Are you saying that it’s red if the start date is currently 60 days later than the baseline start?

Jeff: Oh, okay. So it depends. This is, in my case, it’s always start date. And you can change the formula over in the Microsoft Project schedule. But in my case, this is the status, which is a number of… In the RAG report, this is the number of events that we meet red status as per your Microsoft Project schedule. It’s not the number of days. You have to go back to your Microsoft Project schedule and go, “All right, this task is in red because it is over the amount of days that is required to meet the amber or green,” if that makes sense.

Jeff: So, let me pull up the value real quick. So in this case, if it is zero days or less, that is a red status. That is a red status symbol. So the other thing is, keep in mind, these tasks are open and not complete. So, if this was complete 120 days ago, this wouldn’t even be on my chart. I’m sorry, on my project.

Melanie: Zero days or less than what?

Jeff: I’m sorry, the planned start, in my case, it’s planned start.

Jeff: So again, my RAG status always goes to the start date. You can calculate or you can modify that count to be finished date if you prefer, by using the value calculation here by changing this to finish.

Jeff: You can actually have two columns if you want. You can have a RAG status start, RAG status finish, you would just duplicate this column and then change one of them to start and make sure that the other one was at finish.

Jeff: So, going back to the Excel spreadsheet, all this is doing is counting the number that’s in red of each of these categories, and presenting you with a percentage of that over the total.

Jeff: So, how many of these are red? How many of these are amber? And how many of these are green? And then this basically pivots on them using this table here for a key deliverable. I’m sorry, for a key deliverable, a critical path or the all which is kind of a combination of these.

Jeff: I’m afraid I’m not answering the question. I want to make sure that you guys get a proper answer. Did you get one?

Melanie: I haven’t seen another question on the topic, but we’ll wait and see. It seems clear to me at this point.

Jeff: Okay. So again, my tasks always map to the starting date. You can change this very easily to the finish date. I think that’s the summary of the question.

Jeff: All right. So, back to my dashboard.

Melanie: Okay, hold on one second. Actually, I do have another question on it.

Jeff: Okay.

Melanie: So, if the task is supposed to start today, the status date, it is red?

Jeff: It was supposed to start? Well, here’s a great example. This one was supposed to start February 10th 2021. That several months ago, it’s not complete, in other words, percent complete is less than 100%. And it did start but we have a project red status because it’s not complete, if that makes sense.

Jeff: So, these are open tasks that have a scheduled, I don’t know how to say it, it’s basically, they’re scheduled to be completed. And since they’re open and on the project file, they’re still processing, so they would be considered a red status.

Melanie: Okay, thank you.

Jeff: Sure thing. Okay. So, we were kind of talking about these last metrics here. And we have just a few minutes left. And I want to make sure that everybody gets a comfort with these. This is really just, again, a stoplight count. So we have red amber green. This is how many hub tasks are in red, in amber, and in green, and then the percentage across each hub of tasks in total. So, 56% are in planning, 17% are live and 27% are implementation. This does not indicate a red amber green. In other words, 56% of planning tasks are red, it just indicates the total within the hub itself.

Jeff: I haven’t broken it down to another level because my program manager just didn’t need to see that. You could, but this chart does not. This is the work stream, 31% of the work stream tasks as counted are in red, 24 and 46, respectively. And then this is how they break down. So, you can see that a lot of our stuff is finance and accounting. We have another, 21% is manager based tasks. So, it’s just a high level view of where they each fall.

Jeff: And then, finally, this was just an all in one dashboard when we came in. Sometimes my project manager or my program manager just likes to come in here and just look at it on one page. I’ve just separated them out for the presentation to make it easier. And then, my tables are all just helper tables. All that data, this is how I create the charts. I just use these tables to do so.

Jeff: They’re pivot tables, they refresh when you refresh the data. That query we wrote earlier, all you need to do is copy as I showed you earlier, paste it here. And then you just come up over here and you do a refresh. And then all of these tables, all of these tasks counts, everything does a refresh. If you had 1000 rows in your schedule, it might take a minute.

Jeff: So, we are at 1:00. I want to be sensitive to everybody’s time. If there’s other questions, you can send them in or if you want a copy of these files, feel free to email me. I’m more than happy to provide them to you. They are living sort of documents. So I might be making improvements over the coming months, but I’m documenting them as I go along.

Melanie: And I can send out Jeff’s contact information as well in the thank you email with a link to the recording and the survey.

Jeff: Thank you everybody for your time and participation.

Melanie: Since I don’t see any additional questions. Thank you so much, Jeff, for a wonderful presentation. A big thank you to the MPUG community today attending live and those who will be attending on demand. Thank you for choosing MPUG to grow your skills.

Melanie: The PDU code is up on the screen and I will keep it up here as we go. I also want to say congratulations to Ray who will be getting a lovely MPUG gift for his question today.

Melanie: I will also put up here some courses that we have coming up on MPUG that I’d like to draw your attention to. So, we have a requirements management panel discussion and audience Q&A. So, you’ll be able to voice your questions live to this panel, we have a bunch of experts on for that. We have a fantastic three-part course on demand on MPUG given by Walter on the same subject. So if you haven’t checked out that course yet, please do and then join us for those questions.

Melanie: And then, on December 8, we’ve been surveying you, we have seven common misconceptions from you, our audience, about Microsoft Project and how they affect your productivity. So again, we’re going to have a panel. We’re going to have a program manager from Microsoft on for this and Tim Runcie PMP to answer your questions. So, please bring any questions you have for our program manager from Microsoft who will be joining us and that should be a great discussion as well. So we’ll look forward to having you there.

Melanie: Once again, the PDU activity code is posted back on the screen and thank you again.