hello my name is jason miller founder of aspen now solutions and we're about to unlock the power of servicenow like to start off by thanking all 2935 subscribers in over 80 countries globally if you believe in transferring knowledge to those who need it most please click subscribe your user data will not be transferred to anyone outside of aspen now without your express consent hey everyone uh it's been about four maybe five months since i posted a video and uh got puppies believe it or not um around thanksgiving time which is late november and uh just been really really busy um raising these guys and it's going pretty well before i get started today one thing i wanted to talk about was what's happening globally with covet 19 and just wanted to impress upon you how important our jobs are as developers and making sure that we respond to this global crisis by helping out our customers as much as possible there's probably a lot of people right now that are you know going through a lot of rough times especially from the employment angle too so just be thankful that you have a job at the end of the day because i think this crisis is really going to have a huge impact on the global economy and one of my missions is to spread knowledge to those who really need it and i think that in like probably the next two years it's really going to be important because the secular trend of automation and reliance on technology in our lives um is just accelerating an unprecedented pace so we'll see how that that turns out so today what i want to focus on is a recent reporting issue i ran into where i was trying to produce a report for a sla report of course to show um success by assigned to which was fine i was able to produce a report um whether it's like an sla success report and this is all just mock data right so i recreated it which by assigned to fine no problem however i was uh also trying to access data from a custom table and i was not able to and thus i had to create a database view so a database view is a view that we create that joins two tables um and basically it brings the fields together so we already have um a natural join between task and task sla because um task sla it extends task so if we just take a look at this report uh quickly we'll see here it's a multi-level pivot and then i'm able to bring in the hasbreach which is off a task sla and then we'll notice here this dot walking action off the task table so i started with task and then i dot walk to assign two so just to show how that's constructed here um i select the task and then the task fields right so just remember these green ones here there's all your reference fields so we have one for or we have one for task at the task sla level so if we take a look here we select it hit this little button and then we're able to access the fields that are there but what happens when you create a custom table that extends tasks those fields are not going to be here because parent the parent table is task and then the child table would be your custom table so i recreated or created another table called you aspen sla and it joins two tables together the task sla and then also there's another table that i have in my instance called aspen rp and if we take a look at this you know the customer really liked the first report but then wanted to see a breakdown by request urgency and and category so at that point i had to go and create the database view i guess another way i could have done it was i could have created a reference to that table on a task sla table but i really didn't want to go down that road so [Music] one thing uh here was that uh with the multi-level pivot when i configured it we'll notice that um one thing that's nice about that or this solution is that it combines the the two tables together and all the selections are here so you don't really need a dot walk one way you're gonna know that it's a database view is that you're gonna have these uh two created repeats and then the two created by um are also repeated too so this is telling us here that it's created by coming off the aspen table and then this one below is coming off the task sla table so then we can just take a look at the sla definition real quick it's a simple we're tracking how fast the assignment assigned to box gets filled in for when it's active um stop condition is assigned to is not empty so as soon as that happens then guess what the sla um it's either breached or uh um it's not breached so this is uh the original table here uh aspen rp that i created and we're we have some data in here but the the main three columns to focus on were the category the assigned to and the request urgency and now we'll take a look at the sla table and this is a combination so you'll see here the two creators that i mentioned before um and also uh three categories here and just one note on this is that we're not entering any data here this is just kind of like an output or visualization of the data so how do we create the database view well we're going to get started right now and you know i'm just going to move this toolbar to the bottom i'm doing my recording and zoom today because for some reason my quicktime player doesn't want to allow me to share a screen anymore so uh here's our database view table you have to be an admin to access it so i just typed in sc space view database views comes up under system definition and then here's the one that i created when you click new it's going to take you into a screen like this so you can give it a name a label plurals whatever and if we take a look at what i had created so here's the name of the table i put in the label there and then you're gonna have this related list come in below you know basically asking you which two tables do you wanna put together right or join together so the first one is task sla and one important thing is going to be your variable prefix so here you're going to see task sla table we can do field specific fields if we want to however i didn't want to go that route um because then i'm going to have to put in a bunch of fields i i really didn't want to waste time with that i just want to bring in um all of them and then the next part is we're gonna have to create one for our custom table which in this case is aspen rp and then notice here the variable prefix asp now we need to have a where clause so you know those of you who do a lot of scripting or especially with the business rules you're going to notice this construct here and that instead of like the um the dots like if we're dot walking we have these underscores so task sla dot task and what that's saying is we're on the if you're on the task sla table here's our task column so right now it's referring to this one but we'll notice here that this is a reference field so it is referencing that that um the task table right and saying okay which task do you want and then here we're saying aasp which is our variable prefix here dot sys id but they're using an underscore so this would be the formula that we use and again for those of you who do business rules you probably understand why we need the cis id the reason why is because this is a reference field right it's a reference to the task so we need to have that cis id in there if we don't have that my guess is it's not going to work we want to take a look at an example that's out of the box this sc task sla is a good one to use there's also incident sla so you can use those strictly for reporting so that way your customers don't have to really get bogged down with remembering the dot walk because a lot of times especially going back to like there being multiple um creatives right you will have to explain that to him like which creator does which however it is underlined or basically outlined in the parentheses there so if we go back here to our report when we select our rows um we'll see here these two are coming from the um the custom table now if i want to put in assign two i can also do that um and i don't know what that's going to render like but we'll hit run and then we'll see here we have our urgency our category and our assigned to we could also rearrange this if we wanted to um also and just a reminder with a report like this multi-level pivot it is clickable so if we hit i don't know we wanted to see you know which ones are the empty records here we click on these eight and it would bring us here um into this list view so that's our solution for today my name is jason miller founder of aspen now solutions and we've just unlocked the power of servicenow