Advanced reporting part 2

Hello everyone and welcome to advanced reporting. Today is day two of advanced reporting and if you are new here my name is Dan and I am the instructor for today’s course. If you have not signed up for a a test drive similar to the one that I used yesterday and the one I’m going to be using today to practice and create additional filters, groupings, reports, whatever you want then you can do that by just sending an email to educationinfo at workfront.com and then just include your name, your company, and email address. If you haven’t taken the report creation course which I highly highly recommend then I would recommend that you kind of drop this and and go take that report creation course and then sign up for this course again at a later time. That way you’re not feeling lost, you feel comfortable, or at least more comfortable. Now the other thing is that you’ve been in Workfront or at least have been using Workfront for about three to six months. So if you have not been using Workfront for about three to six months that is also an indicator that you might want to not take this course. If you have been with Workfront or using Workfront for that amount of time or even if you feel comfortable with it then you’ll be fine. Now today we’re going to be going through a little bit more detail as far as text mode. Yesterday was touching on text mode today we’re going to go into more detail based off of the different columns or views that we’re going to be using. So again one of the things that is going to really help you is that you are making sure you are focusing on the screen. You are seeing what I’m doing because there’s so much content that I need to provide that you may get lost. If you have any insights for that question then you can go ahead and share your insight in the chat window. Go ahead and complete the exercises that you see on the PowerPoint. Practice them maybe modify them a little bit to to match what your company’s needs are. And then finally that you have two monitors one where you can practice if you have a test drive or if you want to use your live environment or even the the sandbox or preview environment of your live environment then that’s that’s totally fine go ahead and do that. But then on your other screen the main screen you’re following along and watching what I’m doing. Let’s talk about what we did last time. First I I did a review of or at least a to get your brains working we started talking about how to create a report and we created a basic report that showed my late projects by group. And then I started showing you a little bit about the API Explorer and again to access the API Explorer you would log in or not necessarily even login you can just access it without logging in to support.workfront.com and then do a search in the search bar for API Explorer. And then click on that link for API Explorer and that will give you the access. Okay then I will copy this paste it in the chat window so everyone has access to that. Best recommendation is to bookmark this if you have not bookmarked that then go ahead and bookmark it otherwise you can also bookmark the entire just main support site that’s my suggestion that way you have that on hand whenever you need to. Right and so a little bit about the the API Explorer we looked at how to or where to find a specific object so if you are doing a project report you would search for that object and then if you’re looking for a specific field you would do a couple of options you can scroll down and find that field you can also use either command or control F and then type in the search bar for that field like this. Okay so if I am in the API Explorer and again if I wanted to look for maybe a certain project field I would filter for project and then instead of scrolling throughout all of this I’m going to use command F and search for the word status and you can see that there are 16 statuses but we just click in or press enter to get to the right status. Also underneath these these tables you have different types you have fields which are connected to the project and you have references which is the second thing we talked about where the reference is if it is looking at a an item outside of the project maybe it’s a program or portfolio or the projects group then you can reference that projects group and look at any field associated with the project group or whatever we are looking at. Okay and again the last thing that we talked about there is notice on the right side of the screen it has all of the information in camel case the first first word is a lowercase the second and following words are uppercase for the first letter just to remember that. Okay all right then we also talked about and statements we use the option for industry technology and green but if we use the basic mode or the basic standard builder then it’s going to remove all of our and statements so what we would need to do is change those and statements by adding in and colon one colon you can also use maybe call this as bean so and bean one bean and a quick way to find find this is if I go back rather than using and statements here I can switch them all to or statements and then just rename the or here to and and we also talked about wildcards you know we did unassigned tasks in my role using the dollar dollar user dot role IDs that gives you that option to look at not just a primary job role but also other job roles and then we also looked at some different date based wildcards where you can either look at now now which is exactly right now or today which is midnight of this current day and then finally we ended off with prompts being able to use custom prompts to help navigate or guide people to only select specific items so rather than using the the group name up here at the top or the plan completion date what we can do is just simply select rather than giving them all of these options we can actually simply select a specific date range so today what we’re going to be doing is just talking about views some text mode with views we’ll go through the basic structure of text mode because yesterday was really just an introduction to what the the camel case looks like or how to use text mode in in filters but with your views you’re gonna see a lot more data come at least be referencing more data in the text views area we’re gonna show you how you can create some columns which is wonderful because it gives you the option of consolidating data you may have a project and then the next column says owner by using shared columns you can consolidate that into one single column that says project name at the top and then below project name it says the project owner and like yesterday we’re going to be referencing different types of objects remember that if you reference an object it has to reference up in the hierarchy so rather than going from project to task to issue we would actually be going from issue to task or task to project or project to portfolio or program and then we’re going to dive into some deeper deeper information we’re going to start talking about how to how to use some calculated custom data with our custom forms so we’ll create a custom form add in a calculated custom field and start using that calculated custom field to gather information also we can do that same thing in the views area by just selecting calculated columns all right so just a little bit about a view or actually some of the essential components of the view so one of the things that you might see and I’m gonna find this so you have access to it but there’s a key right if I hover over this right now the description that means that there is a key for this anytime that you see that pop up that means that there is some sort of key associated with this right so you see that that’s planned completion date now let’s actually customize this view and you’ll see for instance here under the text mode that there is this description key and this is how your your description is going to show up right it’s actually referencing because it’s saying description key it’s referencing the value field which has a specific description associated with it now there’s another option where you can actually create your own own description you just remove the key portion and that will make it so that you can type in whatever you want so for instance if I come here remove key and say description let’s say this is is the droid you are looking for okay now because this is a standard view I do need to save this as a different view we’ll do standard with new description now when I hover over this you’ll see that this is the droid you are looking for so rather than referencing the description field key now it is referencing or at least using what I told it to do so that’s one way that you can manipulate your text mode view let’s see just making sure I didn’t miss anything the other components of a column view that is very important the name name key so name key again anytime that you see the word key here it’s going to be referencing the value field we’ll talk about value filled in a little bit but value field is going to tell you what the name of that field is so for instance let’s look at some of these right here we have name if we switch this to text mode and find the one that says name key we can see here that it’s saying name abbreviated right so it’s actually abbreviating it if we left this as just name key and then typed in the value field or description or something like that then you will you’ll notice that it will change a little bit if I remove name key in this case it might just stay the same that’s perfect but let’s say that we want it to be something else let’s change this from name key and we can use a couple of things we can do name equals let’s see project name we’ll do that and you’ll notice that it starts to make those adjustments based off of what I put in there now if I have name and then name key let’s do what was that called name abbreviated notice that the name key overrides my name but there is one more that’s called display name and display name is actually going to override both of these so it can say PRJ name okay so coming back here taking a look at these name key again that is what is going to be the label of the column which it’s basing it off of the the value field name you can manually change this and make sure that it is not using name key by removing the key portion and then display name this will override all of the name key or name okay next one query sort query sort is how you are a or whether or not you’re going to be able to sort this view or not and also what are you going to sort it by now with this particular field there this column which is projects it’s sorting it by name and that’s totally fine now if you don’t want users to sort by name you can remove this and so when I click on this save it will not let me sort this by clicking on it there’s no way to do that however if I customize my view again and I come here and I do add query sort and I do name then it will sort for me based off of that that name now when you are looking at other fields other columns for instance if it lets me if I am looking at other fields for instance if I’m looking at owner or description or start on or do on or anything like that they may have a different name in query sort that’s because of they are different fields and where am I getting that information from the query sort where am I getting it from the name key all of this and you’ll notice that each one of these name key come down here look at this it’s sort by name we’ve got name abbreviated if I switch this over and look at let’s actually do owner name okay now notice that owner name is actually going to be a part of something outside of the project because we’re looking at a specific owner name if I were looking at the project label here and I looked for owner which I can find I know there we go notice that it only gives me the owner ID and if I want to visually show the name of that person who owns this project then I would need to add in name and so in this case when we’re looking at name or owner name it’s actually referencing or using the reference option for owner and then coming down and using the owner’s name so for instance we come here look at our project referencing come down look at owner if I open that up notice that the owner now becomes a user owner and user kind of interchangeable here because they end up being the same thing but I would end up using the owner’s name which is shown right here now if we look at our text mode that’s where you’ll see that that reference so for instance looking at value field value or a query sort coming up here and looking at other options you know you’re going to get that information description key that’s owner oh you have you can also do some other options whether you want to see a list of owners now in this case there’s not going to be a list of owners from one project they can only have one but that’s an option I wouldn’t really worry about the links or lists or anything like that there’s really only a few few columns that are available here now DJ that’s a great question let’s actually try that what I’m gonna do is change this from saying owner and use user why do you think that this may or may not work personally I think it’s not going to work but why do you think so I remove everything else you it’s right it did call it a user but the way that it is being referenced into or in the project itself is by using the owner field at the project level it’s called owner but once we click on that owner then it will take me to the user so if I try username here it’s like save I’m not going to get anything but we’re going to change this switch this back over a way to kind of fix this right I can either click on and change the value field here we’ll talk more about it but I can also maybe remove it sometimes you’ll be able to switch this back over because you’ll see here user colon name is not valid standard mode so we’re going to type in owner name notice that the name is owner colon name if we switch this to text mode you’ll see that the name key is view related column what we could do is just do owner right and then we can save this view now this is the project’s owner but once I click on this person Jennifer Campbell I’m now looking at a user not a specific project owner it’s kind of confusing but just understand that when you’re looking at the project if this person is in the owner field and they are the project owner and then once you click on them you’ll be seeing them as a user does that make sense DJ absolutely absolutely now this person they could be a assignment and assignments can eventually become a user but at the very at a task or an issue level then they are an assignee okay can query sort that’s how you want it to be sorted and then we get into the most important this is your value field this tells the system where to look and capture or at least bring in the data and this is where you’re going to be using most of your your field changes or any change that you make so for instance anything that I see here here under the fields drop-down this is going to be my value field at least the name that I’ll be using for that on the right side of my screen or I can also click in here this is the value field the API key becomes the value field so here for instance this is referencing a project name if I’m referencing something then that means that the the report or view that I’m using right now is not a project level view it means that it is probably a task or issue level view but that’s where it becomes the project colon name and if I am referencing if I using a task report and I’m looking for the task name and I don’t need to have that that coal or that task colon name so for instance here under the project under its value field I only need the word name if I tried if I typed in project colon name it will give me that project colon name but it’s not as effective and it’s not it kind of changed things a little bit right here it’s not showing me in a quick example so for what I would need to do is come here select value field save now it shows me the example all right hey there’s other examples so for instance if you are using a just referencing the percent complete of a task or a project then you can use this this is your camel case view and then we also have for instance if you have three words set together then you have the lowercase then upper and then uppercase second and third let third ones now here is where we’re kind of going to get to later today but this is just an example of what you could use this for this is actually incorrect where the value field is is showing this this is actually incorrect we would not use value filled with some sort of expression but you can see how those those different fields look so if you are following along or at least using the the PowerPoint I would maybe cross this out and rather than saying value field use what’s called value expression I’ll type that in here okay next value format and we’re going to start walking through this I’m the type of person that does not get things right away unless I’m actually working on them or actually practicing with them so if you’re that type of person I’m with you totally with you but I also want to at least get these different components of a column view in your mind you know that way when you do see them next you’ll be like oh yeah I remember that so next we have our value format this is going to decide how the text or information is displayed it could be displayed as text number or date you can have currency based off of if it’s a double or if it’s an integer type well it’d be a currency as integer or something like that you can have different types of date formats and this is this can be set up also in the standard builder for instance if I come here and look at the start on go to advanced options this is my field format right so if I click here look at switch to text mode notice that the value format format is at date then that means that it’s only showing me the individual date okay now if I come here and actually switch it and say let’s do this one with all the time the date everything and if we switch this first I need to select done notice that the value format is now full at date so you can kind of practice look around see it some of the different options you’ll see here in the description that the value format is HTML that’s actually the one that we’ll be using most throughout this session because it is it’s this most simple right we have value format here that’s HTML value format here HTML percent complete this is a little different right so this is value format as percent now I’m I don’t have all of these listed out but if you do a search in the in support support dot work front calm and you wanted to find value format and that would give you maybe a couple of lists or a few lists of what different types of value formats there are right and the HTML that’s what we’re going to be using most today and then this is what we’re going to be doing I wanted to show you that work front itself actually has some templates and I want to show you how to use those templates so back to support dot work front calm and you start off here and then if you want to you can scroll down until you find reports right the report area that’s going to we’re going to skip all of this information and then eventually come down to the using custom view filter and grouping samples and then you can take a look at these so clicking on all of this opening these up it is specifically saying what view it is or if it’s a view sample if it’s a filter sample or if it’s a grouping sample right there’s two different pages here we can see all of them and the websites running a little bit slow but here you go those are all the available ones but we’re again looking at just views for now let’s look at let’s look at this one this is possibly a project view or a project used for projects actually looks like it’s for tasks this is great so this is a view that is on a task report that’s going to show me the task name the project name the program name and then the portfolio as well now how do we use this it can be kind of daunting when you’re like oh I don’t need do I need to cop type all of this in I need to find all of these fields no not necessarily so what I’m going to do I’m gonna go to a task view actually let’s go to a report remember my all tasks report and I’m going to change this to a new view I notice that each one of these fields is set up as a column and when I look at the text mode you’re not seeing anything specific right all I’m seeing you’re not seeing that column zero column one this is how workfront organizes it it’s different view columns the first column in your list is actually column zero and each additional column after that starts with column one two three four five all of that but in order for us to use one of these as a an example we wanted to make it as simple as possible so we have this option it’s gonna say column dot zero dot and then you’ll start to see okay this is the description name or description key you’ll see down here the value field that we’re looking for so let me point out the specific areas I want you to note so we talked about description key down here we talked about name key or resort value field and value format a little bit down here next one we have our column this is a description key for project our value value field is project we have query sort right now in this case it’s using object code but what it’s going to do is automatically switch that over to name which is totally wonderful that’s what we want so next what I’m going to do is just simply copy all of this down to the very last column I’ll take that into my first column here and then switch the first column into text mode okay I’ll select all of this and just delete it then I can paste all of my columns all right then once I save this you’ll start to see here’s the name project program portfolio and notice that it didn’t remove all of my columns because this might be column number six actually is one two three four five and when I’m looking at this this would be column dot four dot so let’s remove these so it looks exactly like the other there we go and then I also I wanted to show you here in the value field it did leave it as project colon object code that’s totally fine in this link for the value field itself this is where I wanted to really focus the description query sort value field value format HTML now we’re going to do this task with projects program and portfolio name so if I go to the project tab and I click on the project tab here I can see that it’s going to remove all of my columns all right and then it’s a very simple way to see okay is there any one of these that are not associated with a program or not associated with a portfolio just kind of a nice simple way to see how this works is you can see here that I have the parent tasks and I have the parent tasks that are parent tasks you might have parent tasks that are four levels deep or two levels deep you know if you remember what a parent task looks like let’s go to a project and you’ll notice that here are some parent tasks you have in the concept and indented task becomes a parent task and notice that this one has two parent levels and you might want to find out what those different levels are or how to make fine actually work with those different levels so this one is called parent tasks four levels deep I’m going to go ahead and select this all of these columns and I’ll create a new view actually that’s a new filter I apologize and then I can call this parents or levels deep again placing it at the first column that way it’s going to automatically start filling in all of those other columns once I select save now see name parent parent parent parent parent parent right and so for this particular project I don’t have too many let’s go to our task report switch this over to that view parents four levels deep and for the most part there’s not very many but if I did want to I could select to sort maybe by any that do have a specific parent and looks like it really is only going up to one level deep or maybe two levels deep awesome any questions on using those templates the templates are really going to help you and you’ll learn also from those templates because that’s how I’ve I’ve learned it too is by just copy pasting making some modifications to the template to see how that affects my report perfect thanks John okay now some custom columns if we wanted to create a shared column where we do that the parent or the project name and then share the the owner name along with that column then here’s what we can do we go up to our tasks and let’s actually go to our projects area to make this look right and then I’m going to combine these two fields first let’s actually switch this over to our standard view actually that didn’t really matter I guess just use this the first thing I need in order to combine a car or two different columns and if I want them to be up one on top of the other or at least stacked and what I need to do is have a blank column and what this blank column is going to do is actually specify that this is my my line line break right so if you know HTML you’ll notice that this value here is for a line break so really all I’m going to be doing is just telling what format I want the system to read this as it’s going to do that and then also I do need to have some sort of width and then specify I want you to share this column with the following column remember it’s always the following column so if I put in share column on this one it won’t share the column with the name yet all right so let’s switch this to text mode you click in the the text mode builder at least it to edit you’ll notice here that it says display name I can I can just remove that all together because I’m not going to be adding a display name first I’m going to say what the value is and I’m going to use the line break just a simple HTML which also means I need to use the value format now before I go any further here notice that the value format is not the same using the same functionality as the normal camel case on the left side of your screen we are actually using these different combinations this is not necessary to have camel case it might be something different like value field you’ll notice that that is not camel cased but anything after should be camel case especially when you’re using a value field next let’s give it a width I just like to use one because it really really doesn’t matter and in the width that is looking at by pixels by the way next I’ll say share call now if I did share column with the capitalization there it’s not going to work so that the the case is is definitely sensitive here and then I just say true now watch what happens to these two columns once I save this so it places basically that line break on top of the John Smith it removes the name because my first column does not have a name or a value field or a or anything like that but you’ll notice that these are combined you’ll see column dot one column dot two you can have 20 different columns combined into one if you needed to it’s a lot but it’s an option now the other thing that I might want to do maybe modify this line break or this value a little bit more maybe rather than using a line break I want to use a horizontal break so HR this is one of the things I like to so place that line right another thing I could do I could place that line and then I can add in some more you know I’m just making up a few things we’ll say that this is B and then we’ll do font equals red and then we’ll say that it’s just gonna say name or the font only save the font Alright, so I’ll be to do there’s a couple of things on color there we go okay so I’m making a little bit more you know understandable I can also change this rather than name maybe it’s owner but I’m pretty much done here right the next thing I need to do is come to the column I want to share this with and switch this just strictly over to text mode and I’m only going to do one thing I’m going to add share call and say this is true okay and then in our preview that gives us an example so share call columns project name and owner right so you can see how that can be a little useful by helping to clear up some of your space now the description column it actually ended up taking it up all of this space and that could be annoying so you can still manage that by by managing the widths that you’re using so for instance if I come here customize my view I can specify that notice that this says its width is 200 but it’s not necessarily using the width because I have to specify that let’s do column notice I’m putting in for the column use widths and I only need to do this in one column I could have done it at the percent complete I could do it in the description area and what this is going to do is force this the system to use my columns I’m actually going to change this and say 250 come up here switch this to 250 well 2500 that’s a lot right now there’s some other areas that you can look into that have to do with how wide the column is don’t worry about that for now just focus on width and use widths hey I’m not gonna see anything there but then if I switch to the description this is 200 the start on that’s 150 maybe I want this to be more like 100 and then finally this one let’s actually leave this as let’s make this remove the width see how that does save my view so now that I removed the width for the percent complete you’ll see that it it kind of takes Liberty able to choose whatever width it wants so that description is really really small now I might want to come back and make it an adjustment so we’ll take this swap this instead of saying 200 let’s do three actually let’s do 400 right so that’s how you can manage your widths how you can share your columns right so in this case we did add column for project and owner but we didn’t do one thing we didn’t add the project name and then owner so it shows that here so let’s do that one more thing come here to name and what I need to do is find where the column dot one has either name or display name or name key and what I’m gonna do actually is just use display name notice if I look in here there’s let’s actually open this up there’s no display name for this first column so let’s actually modify this I’m going to create select this column so that I’m specifying which column I’m talking about and then I can type in display name equals project name or we can do maybe project owner name it might be better okay so just a quick practice exercise what I’m going to do is actually combine the assignments column with the task name and assigned to column so it’s very similar but it’s it’s going to be task and assignment so I’ll go to the reporting area go back to my tasks do a new view we’ll change this and say that it is task assignment shared column now one of the things that said to do is combine assignments or actually change assignments to assigned I’m not going to do that right now what I’m going to do is actually add a column here and we’re going to specify that this is a value that equals break and then what I can say is this is my assignments okay next I’m going to go to the next field and say value format and what’s my value format going to be here guys absolutely Larissa thank you so my my value format is going to be HTML because again that’s our standard that helps us to understand or helps the system to quickly note which column this is or which item this is hey so now you can see there’s column dot one dot there’s column dot two dot right next all I need to do is come to my text mode for the first task first let’s actually add in here a display name and call this task assignments then I’m going to do share call equals true and save my view I’ll start to see my different assignments okay so it’s showing me those users who have multiple or those tasks that have multiple assignees to it and that’s nice for me to see the only difference here is it is not look allowing me to click on those assignments and specifically say okay I want to actually select grace or I want to select Susan here so that was why I would have used the or actually switch this over to assigned to and that would have given me some some better options or a link so let’s do that assigned to field here I’m gonna look for signs to name and so this was what I would be lacking the ability to actually select this I modified that entire entire item but that’s okay the other thing is this is useful as a report to be able to click on the assigned to name and switch this maybe instead of Andrew I want grace actually it’s showing up over here anyway awesome now the next thing we’re going to do is just talk about referencing referencing will go pretty quickly because we’ve already already used that right we’ve already been able to use referencing in our filters so it’s not really that big of a deal so let’s look at this one so in this case if we are going to go to our projects area and we wanted to look at who the project owners manager is then I can do that I can do that using a reference now if I look at the API Explorer if I start off at the project project object and then if I needed to reference the project owner you would take me to user but next I would want to look at the users manager now if I come down here notice that the manager is only using ID so I would need to actually reference the next set so I’m referencing not just one but two areas so I go back to manager notice that the manager also becomes the user as well so let’s show you what that would look like I’m going to use I’m going to just use a different view new filter actually not filter new view here we go all right so now let’s look at the owners manager we can start off by project owner and we can even go by you know who the owner name is if we wanted to we could start off with owner name and this is great because this is this will help us to at least get as far as owner name right now we could start off by project owner ID I’ll do another one here and also use the owner name okay so they’re essentially showing me the exact same things actually this one oh yeah there we go owner ID owner name they’re all the same this one is showing some sample text because if I save this I’ll get a bunch of gobbledygook right so customize this view again let’s change first the owner ID switch to text mode and what I could do is just simply change this from ID to bean or colon name I can do the same thing for the query sort and the value field okay then we save this and it’s going to show me the exact same thing right now it’s showing primary contact name right so it’s kind of different showing me something weird that’s okay it’s it’s still going to be the primary contact name is also the owner right so next what I need to do is is add to this so zooming in right here you see owner colon name I’m going to type in between let’s actually do this at the value field level first and all I need to do is type in manager colon and we will be able to see that right here so manager that’s the right API key and that’s a way to reference because if I put in here user the system would not know what user I’m talking about if I just put in here user for value field here since I’m looking at a project it wouldn’t know what I’m talking about if I put in value field manager it wouldn’t return anything to me because the the project itself does not have a manager field so I can use this copy right then I’ll save this now it says primary contact manager if I wanted to let’s actually I’m gonna make this change over here so same with text mode notice that it’s already giving me the owner colon name but there’s a few other things that it’s providing as well so owner colon name would you manager on colon owner colon name colon manager right then what I could do is just remove all of the rest of this and just say owner manager see these are the very the only real important ones if I wanted to I could also just remove display name or I could do maybe if I did name key what would it do interesting so we’re gonna change this we’ll just do name okay so notice here that the primary contact it’s now Jack Oliver the owner manager over here is not showing up so the the one that I have at the beginning the primary contact manager I can still change this let’s call it owner actually owner manager so looking at this we’ve got our query sort our name key our value field it’s using HTML I come over here I’ve got value field is just using manager that’s gonna be wrong so owner manager name now they’ll both be the same now you could go even further for instance just one more thing let’s make this one owners managers manager and so then I can come here click on this and type in manager colon manager colon all right so now we see Jennifer Campbell she reports to Jack Oliver who reports to Marcy and we can also see if Marcy reports to anyone one more time here let’s just copy this and one more manager here owners managers managers manager all right so I think that that’s as far as it goes on on the owner manager manager but we might have some more that’s a good question I believe there is a limit I’m just at this moment right now I’m not quite sure what that limit is but we can make can actually try more what I will do is I will look that up and get that information to you so I want to make sure that I know that too all right just typing that down all righty so hopefully you guys are understanding how you can reference things another way to reference is if we’re looking at not just one item but we want to look at the portfolio or the group owner or something like that we can do that as well so for instance if I wanted to find the group owner or even group name again that’s going to show that and then I can have actually not this let’s do more so portfolio switch this so that we can add in here program portfolio or even project portfolio because if we look at our task list that we created earlier that looks at portfolios programs and everything here I’ll show you what that actually is doing oops not that one okay so we have our name the next one is going to be project colon name then the next one is project program name then this last one I can actually just skip program and just do project portfolio name so there’s lots of different things that you can reference up to all right so then next guys what we’re going to do if you are okay with referencing which is pretty simple right or at least is pretty self-explanatory what I’m going to do is start using a custom calculated field all right does anyone have any experience using a custom calculated field and we’ve already done this all right so we’re going to be creating a custom form and we’re going to create a field called work balance it’s a project field project form and then we’re going to say what that work balance is we’re looking at the actual hours and subtracting the planned hours to give us the project’s work balance okay so to do that the first thing I would do is go to setup come to my custom forms and then just create a new form all right you can you can do this field on a existing form if you’d like but just for today’s purposes I’m going to do a new form and call it my calculations the only thing I’m going to do is start adding some calculated fields and notice that the first thing I need to do is is give it a name if I start typing in here that’s perfect so let’s do balance actually work balance and then it says what format it needs to be in I’m gonna leave this as text because it’s going to be looking at planned out actually let’s do this actual hours and minusing or subtracting planned hours okay now if you’ve used Excel or used any kind of spreadsheet where you created some different formulas then this will be very very similar the difference here is that it’s actually referencing fields within work front it doesn’t have all of the capabilities that you would with the different fields but you do have date expressions that you can use you have different mathematical such as number sort sub some some text if you wanted it to be left left or right and then some other ones such as case if if in in or is blank okay so let’s actually start doing that so the first thing that I need to do in my calculation just like it would be in in Excel I actually rather than using Excel I I wouldn’t use the equal sign okay it gives me a an invalid expression all I need to do is just start with the formula itself as a couple of options I can just do some and just double-click this it’ll type in what that sum is okay now the sum itself it could be a number minus a another number it could be a number plus another number if I use that comma but the other option is I could use sub and then put in here in that first section remember we need actual hours so in my fields notice that this is only going to be pulling from any of the project fields so I’m not referencing any item directly here yet there is a way to but I’m not going to do that yet so we need to type in hours or we can even just do actual hours what I want to do is make sure that my cursor up here is in the between the parentheses type or double-click there and then I’ll do a comma and place in my planned hours okay so actual hours minus planned hours next I’m going to go ahead and save and close and the next thing I need to do is actually apply that to my projects so let’s do that I’m going to look at all projects select them all edit them and then come down to where the custom form is and add my calculations custom form all right so I’ll add that save changes and then next so that I can see it I’m going to add the field so customize my view rather than group let’s do I did not know that it would let you do curly brackets around those two I thought it was simply like it does use curly brackets to if you’re looking at other areas of the tool for instance we’ll be using some curly brackets in the text mode for our column calculations we’re not really going to have to worry about that here but that’s good to know thanks Lily so you can use curly brackets all right so next it is called my work balance okay that way I can see if this is working and you’ll notice that it is but it’s showing me in minutes right the other thing I might want to do is actually add to this let’s remove a couple of things make it easier to see here let’s do our actual hours and our planned hours and we’ll call this our calculations view and so this is what we’re we should be seeing right some sort of form like this and we can look at what the actual hours is that kind of showing us here but first what I need to do is I go back to my calculation and divide that right so we’re going to divide my entire calculation by 60 to change that over to a hour okay clicking on here all I need to do here well there’s a couple of things I could simply add in at the very beginning look at divide we’ll do this first one and then we’ll divide it by 60 or what we could have done is just after the planned hours and then the parentheses just done this so divided by 60 okay and this should give me my hours but if I want to I can also make it a little bit a little bit cooler so let’s do concat and concat is going to allow me to add text here so I’m concatenating this entire entire formula or expression so let’s put in equals space hours and so that will give me what I want so now if we go back to our projects area if I refresh notice that when I refresh it’s not going to change anything the reason for that there’s two things one either I need to come down here and update any previous expressions and then save and close and apply then if that doesn’t make it work the other thing I need to do is select my my projects go to the custom form area and then say recalculate custom expressions so now it’s changing that it’s now showing the difference between that in hours okay now let’s look at this a little bit further we’re gonna actually look at it by referencing an object just need to make sure I have the right object oh you know what I shared that with everybody or at least meant to I shared it only with Larissa she was keeping it to herself okay thanks guys so next I’m going to reference now notice here when I’m cross-referencing items in text mode it uses that colon right but if I’m cross-referencing items in a custom form or custom field it’s going to use a period and it’s also using the main name of that field all right it’s not using camel case it’s just typical so here we’ve got project period name group period name project period company period name and we can even do a little bit more so here if we happen to have created a custom field let’s look at a few of these this is how it would look so in text mode actually see so if I use that custom field it would show that custom field and actually what it would look like guys is similar to this one of the things that people might use is to find what that maybe a portfolio owner or something like that they might use that as a as a custom data and this calculation specifically for just work balance I can do the same thing and use that for my tasks okay here’s a good one all right so we’re looking at the percent remaining of all of my my tasks right so I will do a custom form call this my task calculations do a calculated field and this time we’ll call this my percent remaining and one reason why you might want to do this is because you only ever see the percent that is complete currently so we’re going to say percent complete and subtract that from actually we’re going to take 100 and subtract that from percent complete and the way that I want this to look is with number and we can even do decimals and do a percent so coming down here under the calculation first we need to do that subtraction so we’ll start off with maybe either sub or some this time I’ll do some do 100 minus and then come down here find percent complete and double-click that close off that first expression and then what I can do is round it and I need to specify how deeply I want to round it I’m going to round it to the the nearest second decimal and after that I’m going to concatenate it some concat comma and then put in there a percentage symbol I believe I’ve got it all there there we go I didn’t change anything anyway so here’s what I’m going to be doing and save save and close then what I need to do is go to my reporting area select all of my tasks, but this time I’m not going to select any complete tasks. I’m going to do any incomplete tasks because if I selected any completed tasks, right now it’s not going to let me add a custom form or modify a custom form. So next, edit, custom form. Just in case, I’m going to recalculate custom expressions. And then what I’m going to do is add that percent remaining field to my view.

Okay, so now let’s actually change my view.

I’ll do it at the report level. Okay, so I’ve got percent complete already percent remaining though is the one that I want. All right, so something is not working accurately. So if it is showing that it’s zero, it’s not showing accurately. What I can do is flip this. So it is actually working. It’s just saying that if it’s at 0%, it’s not going to show anything. Okay, so down here where we had a few that were at like 90%, this is where it’s showing me the 66.67. Oh, you’re right. You’re absolutely right, Larissa. So I added it just to incomplete tasks and not my complete tasks. So that’s why we’re not seeing it here. If it were added to my completed tasks, it would show 0% that’s remaining. All right, so now that we know how to use that in our calculated fields, what do you guys see as a benefit of using calculated fields in a custom form? Because we’re also going to do that same exact thing but in a column form. So here we can see that percent remaining. DJ Shelton, you said it’s available to all reports. I think I understand what you’re saying there. So the field itself is available to all reports if you wanted to. That’s true. If a calculation is difficult, you’d only want to do it once. Absolutely. So let’s actually do another type of calculated field. This one is going to be a custom column calculation, I guess is what I’d say. So now looking at this, what I want to do is use that percent complete. And then what it’s going to do, if I switch this to text mode, I’ll remove a whole bunch of information. I don’t need the tile. The tile is actually what gives me that little tile there. Remove tile that format. How do I want it to show? Now if I save this, see percent complete by format double as presented. Let’s do one more link direct. So it’s still giving me that percentage complete, but that’s OK. Next what I’m going to do is just remove that value format, or that tile. Oh, that’s interesting. It left everything. Save. And then here’s what I’m going to do. Notice that we have the value field here. And if you remember, when I was showing the value field, I had one that said value field with a custom expression after it. That is actually not what we’re going to use here. We’re going to use an expression. So it’s called value expression. So basically taking what we did before, where we took the expression and did a percent complete, subtracted 100 minus percent complete, we can do that here too. So that’s the first part of my formula. Again, if I wanted to, I can round it. I can just concatenate it if I wanted to as well. That’s what I’m going to end up with here is just do a cat, then percent, and then close that off. Now I can remove this as well. Notice here though that there is no value format. So we’re going to add value format equals HTML. We’ll change the display name. We’ll use maybe just name and say it’s percent remaining. Now let’s zoom out and see what it looks like. So it’s not showing up again. That could be that there’s something wrong with my calculation, which is no problem. This is how you learn how to use your, or actually how to make those calculations. So if I take a look at this, I’m summing 100 minus percent complete. You know what it is? It’s one thing that I haven’t actually taught yet. Notice that because I haven’t, or the percent complete, I’m using it the way that it shows in the value field, but there’s one thing that I need to make sure I’m using on any of my value fields, any of my fields at all. When I’m using a column, I have to use that curly bracket to at least enclose that field here. So now that I have that enclosed, I’ll be able to see that that’s actually working. When you’re enclosing it, it is recognizing that as an actual field. It’s going kind of slow. There we go. So now we can start to see that percent complete as well. Same thing with our projects area. We can take that work balance and do it in a column. And as I’m doing this, guys, I want you to think about what is the difference between using a custom field versus a column, and what are the benefits, and what are the possible cons? I need to start this from scratch. Let’s do value format equals HTML. Then we’ll do display. Actually, we’ll just call this name equals work balance. To make it different, I’ll just do work balance. And then next, what we’ll do is do our value expression and zoom in. So first off, the first expression that, or if you remember, the expression is we’re taking actual hours minus planned hours. So in this case, what I’m going to do is go to the API Explorer, make sure I’m looking for project, and then search for actual hours. And here we go. Actual work required. And because I’m using this in an expression, again, I need to put this in encapsulated or put it in a curly bracket. That way it’s showing that this is a field. Do the same thing to start off my next field. I can actually put both of those brackets there. Now we’ll do planned hours. Notice that both of those are set up as an integer type. So I don’t need to worry about, or I shouldn’t need to worry about any kind of rounding or anything like that. Now I can do two options. I can do the minus symbol. I can do a comma and then put in a sub and put all of that into one expression. Let’s save this and see what it does. All right. So notice again, it’s putting that in minutes, but it’s giving me the information pretty quickly. Let’s change this view. Remember what I need to do is divide. This time I’m going to divide by 60 this way. I actually know I’ll do it just this way. I think I did it with the other option before. So I’ll divide by 60. Gives me the hours. One of the things I might want to do is see hours at the end. So let’s customize my view here. Let’s do concat.

There we go. Save. And we have hours. What do you see as the benefit of using the calculated field or the calculated column? What are the different benefits? There is a little bit more difficulty, I believe, depending on what you’re trying to do with your columns versus the fields. And one of the things that the field will help you with is it will tell you if your expression is incorrect or missing something, whereas the columns will not. Now, the other thing that is beneficial here is if the field needs to be updated and you make a change to that in the custom form area, it’s not going to update immediately on your current projects. You would need to go to those projects, edit them, and then make sure that you are recalculating your custom data here. Whereas if you’re using the column, it’s always going to be up to date to the particular time that you’re running this report. So, let’s do a couple more things here. Now that we’ve done a calculated custom field and a calculated custom column, let’s do a referenced calculated custom field. And we’re going to do it for the tasks. Here we go. Task calculations. We’ll do a new calculated field. And this one, we’re going to look at what percentage of the work of, actually, of project work belongs to one single task. So we might have a task that is an 80-hour task, and the project itself is 100 hours long. So we’re finding out what percentage of that work is associated with that task. So then what we’re going to do is actually take our task planned hours and subtract, actually no, not subtract, but divide by our project planned hours. And that will give us a percentage based off of how much this actually is for this project. So first, let’s start off with divide. And the way that I like to use this is, or at least to create any kind of calculation, is I start off with the middle calculation or the simplest calculation. For instance, divide. I’ll get all this data in between, but then it could make it so that my expression or the data that I see is like 44 point, actually, it would probably be more like 0.444444, all of this, without the text. So what I would want to do, a couple of things. In order for it to not be in that decimal form, first off, I would want to actually multiply that by 100. And then also if I wanted it, that’s going to make it so I’m getting the maybe 44 point, all of this. So next, what I would want to do is round this and round it. You can round it to wherever you want. Maybe it’s 0, so it’s nothing, or you can round it to the nearest second decimal. And then finally, it will look like this, 44.44, but that’s all it’s going to show. So maybe what I want to do is have it show with a percentage. So again, I would need to concat, add in my comma, and then put in percent and close that all. Just real quick, what I can do here is just double check. Do I have all of my formulas? Do I make sure that everything is set up right? Do I have parentheses that equal the left as well as the right? And then what I can do, for me, it’s nice and easy. I can just copy and paste that down here. But then where I’m in this area, this is where I’m going to place my actual fields. So the first field is my task planned hours. So it looks like that. The next field is going to be my project planned hours. But if I look for project planned hours, it’s not going to show up. So we’re going to add in planned hours again. There you go. But it’s only going to take my task planned hours and divide it by my task planned hours. So with a custom form to reference something, I’m not going to be using the colon like we have before. You use the colon when you’re using the value field in a column or in a filter. That’s what I was looking for. For expressions, any type of expression, you will use a period. Within this one, within this expression, it is going to ask for the name of the object you’re referencing. If you do have an issue like this, what I’m going to do is just remove that parentheses and put it back. And this is what I should be getting. And I’m going to use the colon to represent the task planned hours divided by projects planned hours will give me that percentage rounded to the nearest second decimal with a percentage sign. Now, I’m going to copy this because we’re going to use it later. I like to copy and paste, guys. I’d rather not have to keep it and keep using it over or keep creating it over and over. Okay. Create a task. This time, again, I’m going to edit the report itself. We’ll add in our work. Oh, wrong. We’re going to add in a work balance. No. Let’s do our planned hours. And then we’ll take our project planned hours. Okay. If I choose this next one, I think that might be a little better. There we go. Then let’s create our column. I’m going to do one more planned hours because this is going to be my placeholder or where I’m starting. Now, I did create a column that started off as completely blank with a custom calculated column, but in this case, I’m going to start off with a filled column. That’s going to give me all of this information, but we’re going to modify this. Now, the first thing that I should change or that I could change is this expression. And then I need to remove all of the work required afterwards. Then I will just paste what I copied over from the custom form. Now, the problem is that this is not in this format that you need for a column. First, what do I need to do? Okay. We’ll start off with this. So if I needed to, I needed to reference the planned hours, for instance, for the task. I’ve got one right here. I would be careful. Don’t use the name key because that is using a… Or it’s not using the camel case that you would typically use. But I can place that right here. And then what I want to do is reference. So typically when I’m referencing using the value field, it would look something like this, right? But in this case, when I’m referencing using an expression, I need to reference with a period. Remember, anytime that you’re referencing with an expression, you’ll use a period. If you’re using a value form or value field, that’s what I was looking for, you’ll use a colon. Now the other thing that I’m missing is my curly brackets. Remember that, guys. You need your curly brackets in order for these fields to actually show up. Now one thing about this though is I’m using… I’m referencing something, but I am not allowing the system to look… Or if I did this, it would not work because I am referencing a project, which needs to be in its own curly bracket, and then the planned hours, which also need to be in its own curly bracket. So if you’re ever referencing something, make sure that each side of that reference is in those curly brackets like this. Okay, again, this is just for expressions where you actually have to use the curly brackets, and then just for expressions where you use the period to reference rather than a colon. Just for fun, I’m going to just… I’ll copy all of this, or just the expression here, paste it for you to practice with. Next, I’m going to change the name so that we understand. Okay, so name key, let’s just use name equals project task percentage.

Okay, so now here is how much percentage that particular task is compared with the project. This is how many hours the task is planned, this is how many hours the project is planned, and we can start to see that as it goes through. If it’s 0%, obviously it’s going to show up as 0% of that project percentage. Okay, one more thing I want to show you guys is a value expression where you’re looking at a difference. This one I’m just going to show you based off of what I’m seeing here, and it’s going to be a projects area view. So I want to look at the difference between the start and the finish date. I want to see how many days it’s been since we started, and then how many days since it’s been finished. So let’s do that. Here’s my view, create a new column, and this one we’ll do planned completion, we’ll do actual completion, and this is the last thing I’m going to show you guys. So planned again, this again, the reason I’m showing the, or adding another planned completion here, is to make sure that I’m starting off with at least a placeholder. So again, I’m going to zoom in, change a few things, let’s make sure that the value field is actually expression, and then we’ll start off with what’s called our date diff. So with date diff, it looks at how many days between two days, and because we have our planned completion date, we’ll start off with that one, put that in our curly brackets, and then because it’s a date diff, it’s doing a subtraction, so next I want to do maybe our actual completion date, or actually let’s change this around a little bit, let’s do instead of actual completion date, let’s do planned start date. So how many days is this supposed to be long? Right. So now, I’m going to go ahead and remove this one, we can change our value format to HTML, and then we’ll change the display name, let’s call this our project duration, and then we can remove the rest, everything else is fine. So notice I don’t really need that many column, or actually rows, to get what I need to, so it’s about 55.333333 days, what I could do again is actually create or add a concatenate, a round, and then that would make it so that my date diff is a little cleaner. So we’ll add that in really quickly here. Do concat and round, and at the end, two, and then days.

Ah, I missed something. That’s no big deal. So I can go in and make those changes, but for now guys, if you want to, you can go ahead and take off, I appreciate, really, really appreciate your time today, you guys are awesome, you had some great questions, I see my mistake right there. Thank you, thank you Lilian, got it. So now you’re able to see how many days that duration actually is. Tomorrow what we’re going to do is dive into groupings, custom groupings, or at least advanced groupings.