Advanced reporting part 3

Transcript

Hello everyone and welcome to day three of advanced reporting. Again, my name is Dan. Welcome and welcome again, and hopefully everyone is doing well today.

Now we have quite a few people that we’re still waiting on. Each day, every time that I teach this course, each day of the advanced reporting courses, the amount of people that show up or at least attend diminishes a little bit. That is most likely the case today, but we still have about 38 people signed up for today.

Absolutely, John, you’re very hardcore. Awesome.

Now is actually a good point for you. If you have any questions that you’d like to bring up from yesterday or the day before, then you can go ahead and post those questions in the chat window and I’ll get to those. Then once we get started, just also want you to know that even though today is day three, on the calendar, it does say that this is meant to be a two-hour course. I have a surprise. This is not going to take the whole two hours. It’s actually going to be less. Although you can stick around, I will be around for as long as you need me to. But usually it takes maybe an hour, an hour and a half to go through this course.

We’re going to get started here pretty soon, just still waiting for a few more people to join us. But one thing I’d like everyone to do is just type in the chat window. I want to make sure that everyone can see me, can hear me, really see my screen. That’s what I meant by see me. But see me and let me know where are you located? Where’s everybody located today? Awesome. As you can see here, everybody, that we have quite a bit of people all over the place. We have people from Baltimore, from New Jersey, Tampa, Salt Lake City, Utah. In fact, I’m actually in Lehigh, Utah. Got several people from Poland. Thank you guys so much for your time today. I appreciate it. I know that it’s really, really late in Poland. But hopefully this has been very helpful for you. But hopefully that news that it’s only going to be about an hour, an hour and a half course, I hope that was good news. But we’re going to go ahead and get started.

All right. So we already have gone through all of the Zoom meeting information, how to navigate, how to set up your chat window. Then let me just check on the participants. Looking at the participants, it does look like everybody has either renamed themselves. I’m not seeing anyone with a random number or anything like that. It looks like I’ll be able to figure out who is attending today. Then what I’ll do is I’ll mark you as attended after today’s course. Now, later on down the line, maybe like in a week or so, if you still do not see that you have attended or at least have been marked as attended, please feel free to reach out to me, or you can reach out to educationinfoatworkfront.com. I’m going to give you both of those. So here’s my email. Then again, it’s educationinfoatworkfront.com.

On top of that, the education info, again, I want to reiterate if you haven’t received or haven’t asked for a case study test, or not a case study, a test drive to practice your advanced reporting in, then I do recommend that you get that. It’s going to be something similar to what I use, where you’ll be able to use or log in as different types of users, practice what they’re seeing, and also practice creating reports. You can also do this in your own environments. You can do it in your live production environment, or you can also have access to or at least go and access your preview environment.

Now, if you have any questions, I’ve actually had a few questions come through about how to access a preview environment. First off, if you are looking to access a preview environment that is given to you, or at least you would need to be the system admin to be able to find where that preview environment is in your system.

But if you haven’t seen that or haven’t heard of how to do that, and you’re a system admin, let me know and I’ll get that information over to you.

All right. So, again, prerequisites for today’s class, being able to have taken report creation. It’s actually really great if you take the live course, because that way you can ask questions, you can get answers, and then also spending quite a bit of time in the system within the last three to six months.

Again, for you to get the most out of this class, just make sure you’re actively participating, not necessarily just asking questions, but actually looking at what I’m showing you how to do, and then also following along with that, either in your environment or your test drive environment.

If you’ve done anything that you feel like is a better way, or maybe even just a simple different way that I’m showing, then please feel free to bring that up in the chat window.

There’s been a couple of times throughout these days where someone has brought up something that is like, oh, yeah, that’s a good idea. I haven’t used that before. So, for instance, curly brackets, we talked about curly brackets yesterday.

Now, there’s some exercises. Hopefully, you all have had the opportunity to look at the PowerPoint I sent over, and that PowerPoint is what I’m showing now. There are exercises within that PowerPoint. Go ahead and practice using those. Make modifications to what you want to see or get out of those reports that you’ll be creating.

Okay.

So, today, we’re focusing mostly on groupings. The first part of this, though, is still going to be in the views section, but it is managing how your, what you see in your groupings based off of the views. We’re going to be looking at aggregates by aggregating results up to the groupings bar.

And that’s not views agenda. So, calculated aggregates is what I was talking about. Being able to take, for instance, a number of days, or maybe even, you know how yesterday we created a date diff where we’re looking at the total duration of a entire project from start to finish? We can take that average number of duration for all of your projects and place that in your aggregate area or your grouping area.

Be able to expand what you see in your grouping by referencing in the grouping section, and then we’ll also create some calculated groupings different than your calculated aggregates.

Right? So, let’s talk about what a aggregate is. Now, this is going to display anything like an average, a total count, or the earliest or latest dates, and you’ll see these. So, for instance, if I come into my system, customize this, this is that owner’s manager view that we looked at the other day, but if I click on the start on date, anytime that you see a summarize, and then that summarize has these options here, then that is creating an aggregate.

You’ll get different types of aggregates. You get, for dates, you have a minimum, a maximum. Some of these, you might see a count.

Some of them you won’t see the ability to aggregate, but for instance here, the percentage, it’s going to give you count, sum, average, also the minimum or maximum, so lots of different options. If I choose one of these, maybe if I wanted to, actually, I’m going to add another field here for progress status, and I get the ability to summarize this based off of how many counts, and this is nice because what I can do is actually create a grouping and group by this status, and then that grouping will give me a number of how many late, how many on time, how many at risk projects I have, but what it does on the text mode side is it adds these lines that start with aggregator, and there’s different types of aggregating functions and different ways to display the information, but we’re going to move back here, okay? So for instance here is showing different types of aggregations. You have by hours, so we’re totaling up an entire amount of hours, right? You do have the ability, or out of the box, right out of the box, you do have reports that have an aggregate that automatically occurs, and that’s just your count based off of how many, you know, in this case, it’s how many does Jennifer Campbell own, how many projects, but with the aggregates, you’ll notice here that they have the exact same items, at least these first three have the same fields or functionality as what you see in a text mode. You’ve got that name key, you’ve got the value field and value format, and typically we use a value format of HTML, but then we also have a couple of new functions. We’ve got display format, so how do we want this to be displayed? Coming back here, do we want it to be displayed as hours or as a currency? And also function, is this going to be a count or a summary or an average? So for instance, let’s take a look at that in the system, you’ll see here that we’ve got our value field, sorry about that, we’ve got our value field, name key, and then our value format.

Notice that the value format here is exactly the same, it’s both VAL for value, and then our display format is also the same, it’s VAL for value. But our functionality here is count. Now switching this back over to what our standard mode looks like, our summary is count. And you’ll start to see more like this, so we have different options, you’ve got summary, count, for instance here we’ve got summary, count, average, minimum, maximum, and you can see what those different functions do, or at least look like when we select them. So we did count, let’s look at sum, right? So sum just as S-U-M, pretty simple.

You can change these back over to, let’s do average.

All right, so it’s pretty simple. Now the next ones, if we’re looking at minimum versus maximum, it’s pretty simple. The next one would be max, right? M-A-X, all capitalized.

Now the value format, again that’s at date as at date, but then we also have an aggregator display format. How are we seeing this? This is at date.

Now what you could do is go to the support site, look for display format, and you’ll see there are some options here. So for instance, you can look at a couple of these. We’ve got, we don’t have anything that is actually showing what type of display as format, but you can find that either in display as format, or actually display format. Okay, and you’ll notice that there are different actual sites that will help you. One of my favorite ones is actually this Stack Overflow.

Typically it will give you a lot of options like date format string or data format string. So if you’re interested in that, go ahead and take a look at Stack Overflow or one of those other sites. And they’ll be able to help you get a better idea of what are the different options you can use for your display.

Okay, so that gives you a good idea of what we’ll be looking at here. But once we have all of these, we have the new display format, and then those can switch over. You just make sure that they have the aggregator period and then the code or the information after that.

So one of the best practices though, when you’re doing, when you’re actually using calculated aggregates, is start off with your standard builder as much as you possibly can. That’s a best practice. And in fact, that’s a best practice with all of your reporting, especially advanced reporting, is start off with as much as you can with what you already have in your standard builder.

So for instance here, if I wanted to customize this view, add a new column, bring this over here, I can actually maybe start off with owner’s manager or owner manager or something like that. But the other option is just copy paste, make things easier on yourself.

So we’ll do another one, owner, manager, manager, manager, one more.

And just copy all of this.

All right, now most likely we’re not gonna see anything because I believe that Mark Lewis is kind of the top of the chain here. And we can see that by looking at his org chart. Let’s see, details, org chart. Yeah, it looks like he is the very top. Now the other thing that is a best practice is try to be as simple and efficient as possible with what you, or just trying to get exactly what you need. You don’t need to be, you don’t need to create some elaborate report. Oftentimes when I’ve created reports for customers or internal people, you’ll find that they really just want a very simple way to get their data or to access something.

And so that’s one thing that you have to kind of work with. So again, your best practice, start off with the standard builder, in this case we have our work balance.

So we’re gonna look at that work balance that we created, but I’m going to maybe copy a few things from a different area.

Okay, so let’s start off. Let’s say for example, I want to see how often my people, this is a good one because you might want to use this for your training purposes or for just making sure that people are using your work front system. But you want to see how many times people are logging into your system and maybe even how many days since they have last logged in. So let’s create a report like that.

And I’m gonna leave the projects area and actually go to the people area. And we’ll go to people, create a view, customize my view and then let’s actually just save this as a new view. Call it last login or actually let’s do this, days since, if I can spell last login. There we go, geez.

All right, so first let’s get rid of a bunch of these fields or columns and let’s remove this too.

And then what I can do is say last login. Notice there’s a last login date. And then I also have another one that is login count.

And we’re gonna add another login count.

This is gonna be my placeholder.

Let’s save that. So I’ll give me what I want here. And then what I can do with this is actually take it. This is a great example of showing you how you can create a view here, but use that same view in a report.

So using a new user report and finding the one that I had days since last login.

Next, what I’d wanna do is maybe aggregate some of this. Let’s average how many counts.

I can average both of these.

I can group this by company.

And I’m just choosing company at this moment because that way I am getting an idea of, or I’m actually just giving myself that grouping bar. You can group it by group or by manager.

Any one of those would work. Next, I’m gonna filter.

What I wanna do is filter on any counts, or for any counts that are greater than zero.

All right, so it should look like this in my text mode, login count, login underscore modified to be greater than. My groupings, take a look at what this is, and we’ll come back to that later. But here, let’s call this our number of days since last login.

Okay, so now that we have our login count, notice that our last login, reason that I’m using the average instead of maybe using a minimum or maximum, because I want the average to be the aggregator that we use that’s going to build up. So what I wanna see here is how many days has it been on average that people have been logging in or logging out.

So one of the ways I can do this, is choosing the login count. First, let’s switch this. Notice that I have the aggregator function average, display name.

Let’s call this number of days since last login. Pretty simple, right? Next, what I can do, if I can remove this linked name, all right, the log name key, that’s fine. If I keep it, I can also remove it. I don’t need that necessarily. Remove query sort because we’re not going to be able to sort on this.

And then value field, what the login count is.

And we’re going to change this first off into expression. Okay.

And so what I want to do is see how many days since the last login.

And so I need to find out the last login date.

So let’s go to our API Explorer. Okay.

Find user this time because we’re doing a user report.

Then I can just search for login.

Actually, we’ve got last login date right here. And it’s in the field type of a date and time. That’s no big deal. We’re going to use that. Okay, now because I am using an expression, make sure that you are placing those fields into the curly brackets. Always have to place them in the curly brackets or else it’s not going to work.

All right. So first off, let’s use what’s called our date diff.

This is going to take the difference between two dates. And we want to first start off with the highest number and then go to the lowest number. So our highest number would be current. We can either use today or we can also use now. Either one of those work.

And then we can place this in a comma.

And it’s basically like a subtraction. So subtract now from the last login date. And that should give us how many dates or days that it’s been.

Then our value format, instead of integer, let’s actually change this to HTML.

Actually, let’s see what it does if I leave it as integer. Okay.

And then down here, where we start to get into the aggregator name key, all of that, we don’t necessarily need this name key. We can just remove it. The display format, we can keep it as integer or HTML. Let’s again, leave it as integer just to see what happens.

And then our value format, integer as integer. Let’s leave it as that too, just to again, see what happens. I’m just curious. Now the thing about this though is, if we do make a change such as changing the value format up here to HTML, you’ll wanna change that or you could leave it. But if you change it in the aggregator, you’d wanna change it for the value format. Same thing, not the display format. Some people have gotten a little confused and changed it in the display format. Doesn’t give you the right results all the time. So.

We’re gonna choose integer or int.

And then what I need to do next is actually change my value field.

And the value field up here is going to take an average.

Or it should be taking an average, right? So what we’ll do, is change this to expression.

And then we’re going to go ahead and remove this part.

And then what we can do actually is just simply take this top part and copy it and paste it right down here.

Now I’m going to get a very large expression up here. But because of what I’ve chosen down here in the display format, I might not. So let’s take a look at this, see how it, or actually if it works, because there’s always going to be a possibility that there’s something wrong with your code. Well, not code, but yeah, text mode, right? So I’m gonna save this.

See what we get. Oh, it took me to the summary tab. That’s weird. There we go. So again, I’m filtering on anyone who has not logged in.

Or who has, yeah, anyone who has logged in.

So it looks like for these two, the number of days it’s been since they last logged in was, you know, several days ago. Now it’s show, in this case, this person is two, it’s showing it as an integer. If I changed that though, let’s change this to HTML.

And change this to HTML.

Actually, sorry, that was exactly where I told you not to place that.

I am going to get, make sure that it’s opening up on the details tab.

Hey, there we go. Then it puts it at a decimal, right? Makes more sense.

Now it’s not giving me that average though. I mean, in this case, it probably is for this first person because they only logged in once. But the number of days since last login, it’s saying it’s 0.4. That might not necessarily be the case. It could be something else, right? So for instance, let me show you a good view of this.

So if we have all of these people, right? And I added that last login count, let’s do that here.

Okay.

Date, by last login count, whoops, login count. Login count.

Add one more for login count.

Okay. Save that view. And we’ll start to see how many times people have actually logged in and when they last logged in. So that makes it a little better for us to actually view it in this case. So let’s use that in a report.

Okay.

Create that grouping. We’ll group it by group this time.

Do home team actually rather. And then we can do our filter, filter anyone who has not logged in.

It’s greater than zero.

Then our number of our actual aggregations. Oh, where did that go? Oh yeah, that’s right. Here we go, last login average.

Let’s average this and this one.

And then we can modify this one. So switching this over, we’ll call this our average days since last login.

So I’m removing both the linked name, the direct name or the direct, the query sort. We’ll change this to HTML.

Remove the value field and make it value expression.

And then let’s start off. You know how I did before where it was date diff.

And then we did, let’s do now comma last login date.

And then just copy this to the next level. So display format, just value field.

Remove the name key.

And then let’s add to this because remember, it gives us this very long number. Let’s round it to the nearest second decimal.

And then let’s also concatenate it.

So that if it is a, excuse me, if it is a number of days, then let’s actually say days.

And then I just wanna verify, do I have three parentheses on each side? Looks like we’re good. Then let’s save this. Now there is a limit, now there is a limitation here guys, because you’ll notice that if I added all of these up, the average is most likely not going to be 205.

I mean, it might be, but it might not be. So let’s actually take this. We’re gonna just do 309, 85, 308, 322 and 2.17.

And then divide that by five.

Awesome. In this case, it did work. It works very well. But the thing is, it’s not giving me the date. That’s okay, that’s totally fine.

So that’s one way that you can use this to figure out how many days on average has it been since these people have last logged in.

So for instance, here, maybe Ashley and I should have a talk same with Jordan and Betsy, all having a long time since logging in.

So this is where I get my aggregated results. Another example is, let’s say we did want to find our work balance and take an average and place that with an aggregation at the top. Let’s actually go to our report, excuse me, that we created where I used that work balance.

So this is going to be a project report this time. Now I’m going to use that one that had work balance, I believe it was in here.

Oh, it was calculations.

All right, here’s our work balance calculation.

We also have our work balance field.

And then let’s remove a couple of things. We don’t need owner. We’re going to keep actual hours, planned hours, and then we can remove these, excuse me.

And we’ll keep this one as the work balance.

Now remember what we did is we created our work balance and we looked at the actual work and subtracted the work to find out how much is required. Now let’s switch this. Let’s change this around a little bit.

Let’s change rather than work required here.

Actual work required there. So actual hour, or actually, sorry, planned hours minus actual hours. Again, we’re dividing it by 60.

And then we’re going to place hours at the end. Got our value format. You can have a description if you want.

And then what I can do, let’s say that I created this, but then I’m like, oh yeah, you know what I forgot to do? Is average this.

And so what I can do is take the average, or actually, you know what? Let’s take a sum.

So I can take the average, and I can take the average.

So we’re summarizing these, and then I’m going to go to the text mode here and copy all of my aggregator functions.

Not cut, but copy. All right, so a few things. I can change the value format to HTML. But this case, let’s leave it as compound. Let’s see what it does, because the last one, when we were looking at last login, I left it as integer, and that worked out pretty well.

So we’re going to leave this as compound. Notice that the display format is showing as minutes as hours string. So what this means is it is automatically going to convert the minutes from this and convert it to hours, and then it’s also going to add the string or the word hours at the end. So that’s awesome. I don’t need to make a, or actually make any kind of conversion or anything like that. So really, all that I need to do is just place this, this whole portion, just down here.

But I do, of course, need to change the value field to value expression.

And again, that’s because this display format, the only reason I’m keeping all of, or just the middle portion of my equation is because the display format is automatically changing or converting the minutes here to hours, and then also placing the word hours at the end.

Pretty convenient.

So now that we’ve got that, let’s actually take a look and see what that, how that comes out, see what it does for us. So I’ll save this.

First, let’s actually group it. All right, so we’re doing it by project. Let’s actually group it by portfolio this time.

I’m not gonna put any filters on there because what I wanna do is see all of my projects. Let’s say this is my aggregated work balance.

Okay.

Oh, that’s annoying. So it automatically is switching it over to my details, or my summary. But let’s take a look at this. So looking at the total work balance, right, it is supposed to be taking the planned hours and subtracting the actual hours to give me the work balance. Whereas this one, the work balance field here, is taking actual hours and subtracting the planned hours. So it’s kind of a separate work balance option. All right, so this first one, obviously we’re getting 16, 30, 73 hours. That means that we are in the hole, or maybe a better way to look at it is this is how much we have left. So we’re gonna take the total, and we’re gonna take the total, and we’re gonna take the total. So we’re gonna take the total, and we’re gonna take the total. And so then up here, we’re taking that total, and summarizing it.

So next, let’s do a little bit more, but we’re actually gonna switch over and start looking at our advanced groupings for this next section. My comments, and we’re gonna create a note report.

So first off, let’s actually take a look at our groupings. We’re going to create a task report.

And I’m not gonna change anything here, but let’s use a grouping here. And first, let’s add a placeholder grouping for portfolio.

Notice that the project is not letting me, or the portfolio is not automatically coming up. So let’s actually just do project, and then we’ll do name.

Okay.

And then let’s do another one for, if we tried to even do program, we would not be able to do it, but let’s do project again. Do last one, project again.

Okay, now if I saved this, my groupings would all be the, would actually shrink down and just end up being just one, but with my text mode, I can switch this over, and you’ll notice that it has group.zero, and this is set up exactly the same way that you have your different columns. So the first column here is actually column.zero, and the next column is column.one.

And as you can see here, we only are able to use the standard builder and go up to three levels, but we can actually take this text mode area and go up to four levels.

That’s as many as we can do. We can actually take that, starting with the linked name here, let’s do three.

But then we’re going to start referencing. Now we’re in that task report, we’re gonna reference by portfolio, then by project, or then by program, then by project.

So first, we’re gonna look at this name key, and then we’re going to switch the name key here from linked, or from related column, change it to portfolio.

All right, so name key portfolio, then we have our value field. We need to change the value field here to portfolio, but let’s do it like this, project, and then portfolio name.

And then the next section here, this name key, arc key, this is what you will see right here. So the first part of that name key, arc key, .0, is looking at project, and then the next one, .1, is name. But what I can do is actually add more to this. Let’s actually change this one to two, this one to one, and then this final one, let’s change to portfolio.

If I select done, notice that the project portfolio changes, but the thing about that too, maybe rather than project portfolio, I would rather do, oh, you know what, I just made a copy of that.

Name key, arc key, let’s do this.

And then change this to portfolio, make sure that my name key .0, and then next down here, once I start with the second group, I can start adding to this as well. Actually, there we go.

So we’ll keep this first one as program.

And then view related column, let’s change this to program.

And we’ve got project.

Program, colon name.

Next, I can remove the linked name if I wanted to, that’s no problem, I can leave it. Let’s actually change this linked name up here to portfolio.

The next linked name to program. And then I don’t need to change anything else.

The last thing down here, let’s actually just leave this as a task name. So we’ll do, let’s actually, let’s leave this as task name, and then, oh, whoops, down here is where I want it, task. And we’ve got, just leave this as name.

Name key, we’ll change that to task, and then linked name task.

So then I select done, we’ve got our portfolio, or our portfolio program project task. And then we’ve got port, prog, or prog, and then task.

Okay, so then you start to see how you can organize your portfolio, or actually your groupings, by looking at portfolio name, program name, project name, right, I can remove that project name portion, that’s maybe not necessary.

So let’s come here, look at my groupings.

Down here in the last one, we’ve got project, let’s remove the name key, arg key, dot one.

And it actually makes it so that it’s doing something different, and that’s okay. That’s totally fine. All right, so that is how you can use referencing with your groupings, but let’s create a note report this time, where we’re actually looking at the notes by the company of the owner who owns that note. But let’s first discuss what a note is. A note, guys, is going to be on your projects. It’s part of your updates. And a note would be a type of update that someone types in here themselves. So it’d be, for instance, from Jennifer Campbell, hello from Jen.

And that would be a typical note that we would see, right? Otherwise, the rest of these are system updates that we would see. But we’re gonna do a note update. Actually, they’re both notes, but they’re a little bit different. One is from someone, one is from the system. But we can find our note updates in this note report.

We’ve got different types. Is it going to be a project type? Is it going to be a task update? We don’t really need to know all of that, but we can say what project that is associated with, the text of the note, who the owner is.

We’ve got owner ID, but let’s actually do owner name.

You drag this over here, the entry date.

But then let’s group this. We’re going to start off by owner.

And then we can do, actually, let’s do owner company ID, starting off with that. Because remember, what we’re trying to do is find out who the owner company’s name is, who the project owner is, and then what we can do is also group it by who the owner name.

So next, let’s do project name.

And then next, we have our owner name.

Now to switch this from the ID, which if I save this right now, owner company project name. The first line is going to be either a no value or it’s going to be quite a weird random value here. And that showed category ID. What I want is actually company name.

Owner company name. There we go. Then let’s switch this. Let’s actually switch this over to text mode. We don’t have to look at the other ones because we are seeing it by project. And then by name, let’s actually switch the owner to company in our linked name.

Change the ID here just to name.

Column. What we can do here is add group.zero.name key arg key two. Arg key two.

Say this is name.

And then that’s all we really needed to do up here at the top. So we save. Notice that the ID is gone. We save and close. And then we can see, okay, which company is this with? Which project? And then who is the owner of that note? So we should be seeing a couple of projects here with multiple user or multiple notes.

Yep. I just wanted to double check that it’s pulling the right owner.

Now it is showing the FIFA World Cup. It’s got multiple users here. But then we can also see, okay, is this a person’s personal note or is it a system note? So one of the things I would want to do is maybe filter the type of note.

Okay. And we have different options. We’ve got attachment, general edit, note.

If we choose note, that will give us only those notes that have been actually given or set up by an individual rather than seeing your system updates.

All right.

Now we’ve got just a little bit more. We’re gonna start talking about calculated groupings, guys. All right. So now we’ve talked about our aggregated groupings. We talk about our referencing of our groupings. And now what we’re gonna do is do our calculated groupings. Now our calculated groupings are important. For instance, let’s actually create another report.

And this one is very useful because people will want to see how your projects or actually even your tasks are doing based off of percentage. Right? So let’s look at a report, a task report.

And we’re just simply going to group this by percentage. Not gonna do anything else except for percentage. So we’ll do percent complete.

And then let’s change the name and say tasks by percent.

Range.

Let’s use that, percent range.

Make sure it’s on the summary tab or else I’m gonna go nuts. And then we do done, save and close.

All right. That’s not the right tab I wanted it to. I was thinking details tab, but it showed me summary.

Okay. So notice that there are 783 tasks in my system right now. So let’s actually look at all of them because I wanna show you how it is breaking up my percentages. Since it’s grouping it, you’ll see that there are 100 tasks that are at 0%. And just kind of save some scrolling. Actually it says there’s 729 tasks out of 783 that are at 0%. So let’s close all of, or at least collapse all of my groupings.

And it runs slow because of all the data that we have here. That’s no problem here. And so next you’ll notice there’s some that are at 75% and then some that are at 100%. What about if there were some that were in between? So let’s take a look at our test drive and look at our tasks and create a grouping that shows by percentage. Same thing.

Now this one has only 557 tasks, but it’s still quite a bit. So percent range.

All right. There we go. Notice that it is pulling in that aggregation. There’s 3,656 hours at that point.

But now we see the next grouping is 10. That makes sense. Zero, 10, right? And then we have 20. But notice here that the entire percentage is just 20. And then the next one is 33.33. So it’s not really doing anything where it’s taking into account. Maybe I wanted to group it in tens or even by 25s.

And you can definitely do that. Let’s do this by tens this time. I usually do it by 25s.

But it makes it so that it’s easier to read and see basically what number of tasks are in this percentage or this percentage. So let’s take a look at that, make a change in our grouping. And then let’s see, actually that was in our report itself.

Change this grouping and switch this to text mode.

All right. So we can still use this LinkedIn name direct. We’ve got name key percent complete. Let’s actually change that and change it to, percent range.

Our value format, how do we want to see this? We can have it double as percent or double as a string. But this one, it really doesn’t, it makes more sense that we’re actually going to do it as HTML because here’s what I want the text to display on my screen. So in the group area or the grouping, I want it to tell me that we’re looking at tasks that are at zero to, or actually we’ll do zero percent to 10% and then have another one that is 11% to 20%.

You know, something like that. So in order for that to happen, the number type of format that I had here, it would not work with what I’m trying to do. I need that HTML to help me out. So next let’s change this to expression.

Have our percent complete because we are using an expression, just like when we’re doing a view, our expression needs to be in those curly brackets.

And so we have looked at multiple types of statements. And I’m gonna show you where you can get some of those. Ooh, that is close.

Or at least those types of equation options.

We’re just gonna do this and show you a calculated view. Here are the different options for your expressions. You can sort through all of these if you want to, but the ones I’m going to use right now is the if option.

So we’re gonna say if the percent complete is zero, or if it’s between this number and this number, or if it’s less than or greater than. So that is what we’re going to do here. And it’s gonna look like this, or at least start off with if and then the parentheses. So coming here, our value expression. One of the things that people would like to do actually, because what I like to do is actually split up my formulas. Let’s do this first one and say, if the percentage complete equals zero, then I want the system to say in quotation, zero percent. And then let’s close that just like that.

But what happens if it is any other percentage? It’s gonna not show up, or at least the grouping is not gonna show anything. So we need to add another if statement. So if you are starting to use multiple if statements, then what I like to do is actually separate them and place them on a separate line.

Now the problem with that in the text mode builder though, is let’s say, notice that I have this here, where it’s if, if I switch screens, it’s going to remove anything on my next field. Or if I click somewhere outside, it will remove that next field. So if I click out, so that’s not very ideal, right? So one of the things I like to do is actually copy this and open up maybe like a Google document or something like that, and just create a new document, just a blank one to help you when you’re creating your formula.

So let’s kind of start separating this into multiple levels. First, let’s copy this.

And that’s our next if statement. Let’s talk about the if statement just real quick. With an if statement, you have your equation or what we’re looking at. What is the area we’re looking at? So we’re saying if the percentage, and then we say, is this, then that’s what the comma is, then I want the system to post or at least show 0%. The next comma is going to be your otherwise. So if this, then this, otherwise I want you to do this where it starts with another if. And so here what we’re going to do is say if this is, let’s do less than 10. Actually, no, let’s do less than 11%. We’re going to have it say one to, actually let’s do 1% to 10%.

Okay.

Now the next one is going to be very similar. All we’re going to do is just, I’ll copy and paste, and then let’s change this. So this time instead of one to 10, we’re going to say it is starting at 21%, and we’ll say it’s 11 to 20.

Every time I paste, it throws me up there for some reason. Okay, so there, I’m going to zoom out.

Let’s just make this larger.

Okay.

So we’ve got our 21 is next to 30.

And then we’re going to copy and paste these some more until eventually we get up to the whole 100.

And our last one where it says 100, we’ll do if it equals 100, this time rather than saying a percentage, let’s say complete, and then close that parentheses.

All right. Now guys, one other thing I need to point out. Notice the difference here between the two quotations. One is straight up and down, one is curly. The curly ones are not recognizable in the work front text mode area. So if you are using the text document like this, you’ll need to make sure that you are changing these later on. And I’ll show you a quick way to change them when we get another second. But just remember, the quotation needs to be the straight up and down.

Okay, so then let’s come up here. We’ve got 20, 30, 40.

Can do 30 to 40, to 40 to 50, 50 to 60. Okay.

So you should be getting the idea hopefully. But if you feel like you’re lost or anything like that, feel free to let me know. Then we’re gonna say anything less than 100, anything less than 81%.

Yeah.

Okay.

I missed one, didn’t I? So 40, so this one should be, if it’s less than 40. Okay.

And then we’ll say this one is 30, starting from 21 to 30.

All right. So then, guys, what I need to do is just clean up what I have listed here. And the next thing, I don’t have to have another option. I’m not saying another otherwise statement, right? All of these are already taken care of, but notice that I have a parentheses on only one side of my screen. All right, it’s on the left side. So what I would need to do is count how many parentheses I have. And here’s a trick for you. I’m gonna do find in document and look for that parentheses.

So now it’s counting two of 11. That makes sense. Then what we do is just place 11 other right side parentheses at the bottom here, just to close that off. One, two, three, four, five, six, seven, eight, nine, 10, 11.

And then how do I take care of this? Very similar. What I’m going to do is copy one of the correct quotations.

And notice that I’ve got 22 quotations here. And here I can just replace all with that. And it’s going to update all of my quotations so it’s nice and easy. So use your find, your find and replace.

And then what we need to do is put all of this all on one line.

Because we’re going to paste this so that it’s available in our text mode.

So here’s our value expression, pasting it there.

And if I zoom out, see, that could be quite a bit that you can lose data in as you’re entering all of this information.

And look at this, everything, well before, everything’s all in a single plane basically, but I can follow up with that.

Here it’s a little difficult. So next let’s save this grouping. And of course there’s always going to be those times where the grouping might not work. And in fact, I already see a reason for it not to work is because I have a percentage here.

It’s not looking at, if I have that percentage outside of the quotation, it won’t work.

Whoops. All right, there we go. Looks like it’s okay. I’m not seeing anything, but if you guys see anything, please, please don’t hesitate to point it out.

Then we’re going to save this grouping.

So as you see here, there’s something that happened so there’s no range. Obviously we do have a percent remaining. So there’s something missing in my percentage complete here. Let’s take a look at my grouping.

Okay, so let’s take a look at this. We’ve got, oh, der, you know what guys? All right.

I believe that that’s it, but just to double check, we’ve got zero, 11, 21.

Actually, that’s a great question, Lily. And she asked, do you have to tell it to recalc? Because I’m doing this at a report level, I do not actually have to tell it to recalc. If I created a custom field and placed that custom field on my tasks or my projects, then I would need to make it recalculate. So this is working. You can see that the percent complete is complete here.

And then if we close all of these down, there we go.

The problem that you’re running into now is that it’s out of order, right? So there’s a quick way to fix that. All we need to do is take the percent complete right here and we say, actually, let’s switch this back to standard. And we’re going to have it sort on this column.

Then we switch this to text mode and we have there’s our sort order one, it’s ascending. We can switch that around if we wanted to.

Now it’s probably not going to use that grouping because I edited the report itself.

There we go. So then it is in its ascending order.

So you guys see how that can be beneficial? How many planned hours we have? We could also maybe see planned hours versus actual hours per percentage. That might be beneficial.

Okay, so another thing to think about is, remember when we’re creating these groupings, we only have four possible groupings that we can go up to at most, right? Or at least four separate levels that we can go up to.

But what if we really want to, we want to consolidate what we see here, but we want to see more in the grouping area or the grouping bar.

You can definitely do that. So for instance, let’s say in the task report, we wanted to show what the project name is, what the project status is and the project condition. And we wanted to group by those items. So if I create a new grouping here, notice that down here I could use, or I could bring in project name, project status, project condition. Actually, just for fun guys, I’m gonna create a new report.

All right.

Now to start off, let’s actually start with a standard, the standard mode and put in our project name.

That’s gonna give us our start. And then we can switch this not to matrix, to our text mode, make a couple of changes here. Got our linked name, that’s fine. It’s project name key, we can do related name, project name, value format is string. Let’s leave this, hmm. Let’s see if we can leave this as string. And change this to a value expression. Otherwise, if it doesn’t work, we’ll change it to HTML. But when all else fails, switch this to HTML, guys.

So our value expression up here, let me copy this and let’s actually take this to our, our view here. Move all of that.

And the first thing that we wanna do, let’s say that we want our project name. So let’s have it look like this. This is our project name. And then we’ll place like a line like that and have it say the status.

And then we’ll have it do the condition.

So in order to do that, again, since I’m on a task report, I need to reference the project.

So first off, we’re gonna put in project.

And then normally, if this were a value field, I would put in the colon, and I’d put in name. But because this is a value expression, I need the curly brackets. So I’m gonna separate the name by a period.

And then that’s the first part. So we have our project name.

And then what I need to do, I need a way to separate them or at least to create this portion where it has the space, line, space. So I’m gonna use the concat option.

So the first portion is going to take the project name. We’re gonna place a comma. And then we’re going to add a line.

And then do it again. Let’s do, what we could do is just copy and paste this.

Actually, let’s do this whole part. Copy and paste again. This last one, we’re gonna remove all of that portion and put in condition, close off my parentheses, and then add status. Whoops, not capitalized here.

So we should be seeing project name, project status, and then the project’s condition.

Remove this.

There we go. Okay, so let’s see if that string is going to work for us. If not, then what we need to do is switch it to HTML.

Or are we missing the curly quotes? Oh, here I’m putting in the actual report name.

So I don’t need the curly quotes here. Oh, I see what you’re saying, Lillian.

Thank you so much. You’re absolutely right. So it’s not gonna give me anything. She’s absolutely right. That’s because I am missing my curly quotes, or my curly quotes are incorrect. So one way to fix that, you can either go into the grouping and just go through and change it all. Or another way you can do it is maybe copy that.

I’ll do it the multiple step way, I guess.

Whoops.

Remove, there we go.

And then we’ll replace all of them. Now all of my curly quotes are no longer curly.

Ah, you know what I did? I did it again. I copied the entire group expression again.

All right.

So now we are actually seeing the project name, the status, notice that the status and the condition are both based off of the key. So for instance, if I’m looking at project, I wanna do a search for condition.

There we go. And you’ll see all of my keys or all of my values that are available. So what can you do to make it so that that makes sense other than training everyone on each one of those keys? You can use if statements, right? Now there are multiple keys here or multiple values that are options. So we would need to create an if statement for each one of these. We’d say, if this, then this, or actually if this, then this, or if otherwise, then this, then this, okay? So luckily guys, I can show you just really quickly how to do that.

And copy this portion rather than the whole thing.

And then let’s start off by creating some if statements for our project condition. So we’ll have if our project condition, and then we get to say, if it is equal to, but the thing is what we need to do here, what we need to say it’s equal to, we can’t just put in O-N, right? So if I put in O-N, the system would not fully recognize it. So what I need to do is put in the curly quotes because I can’t put in regular quotes right now, but I can change them later. So if it is on time, then I want to say on, again in quotes, on time.

Otherwise, if all of this, we’ll just copy of it all.

If it is next, looking at our options, we have at risk or in trouble, AR, it’ll say this is at risk. Now you don’t necessarily have to use the same names here. You can change this. Maybe it’s going to match your verbiage or your company’s verbiage.

Otherwise, oh, and then the last thing we can do, obviously if it’s not on time in this case, or if it’s not at risk, then it’s gotta be this. It’s gotta be in trouble.

So then just to kind of clean this up a bit, first I need to count how many if statements I added and then add two to the bottom, two parentheses at the bottom. Let’s copy this.

And then I need to place these all in the same line.

Looks like it’s good. Let’s copy and paste it just to see what it looks like.

All right, so zooming in here, we’ve got project name. If it is project name, then that. Otherwise, and also put in the status. But then we get to the if statements. If the condition is on time, put it on time. If it’s at risk, then put in at risk. Otherwise, say it’s in trouble.

And we’ve got one, two, three on the right. And then one, two, three on the left.

Okay.

There you go.

There you go, guys.

It’s working. So now it is showing all of those tasks or actually all of those projects that are on time.

I can see by complete or on time, at risk. If anything is in trouble, I’ll see that down here. But you can go through and do that same exact thing to change what you see for your status. As you might have some who are not sure, what does C-U-R mean? What is I-D-A or R-E-Q? So you can go through and change those, right? Now I’m going to do that, but I’m just going to copy one that I made a while ago.

Just to show an example of what you could do with your groupings.

Sometimes it could be a little bit long. This should hopefully work.

Just copy and paste.

Awesome. So yeah, this is working. So I just created some specific options here, like yee-haw, stay on target. That means it’s on target. Yee-haw means it’s current or it’s currently moving forward. We’ve got it’s done, so that’s complete.

Lots of different options.