SSRS Report Builder Part 9.7 - The RunningValue Function

welcome to this wisear report builder tutorial in this video we'll explain how to use the running value function the video is all about calculating running totals and other aggregates and we'll start with a basic example of a running total for a single column we'll then show how to calculate running averages and running counts briefly describe the difference between running count and row number we'll then look at how you use the running values functions using expressions and then for the final part of the video how to use running values in groups so that your accumulated averages and sums will restart at the beginning of each group so let's get started to get started i've created a new report in report builder and the first thing i'll do in here is create a data source to connect to the yzl movies database as a quick reminder if you don't already have that set up you can use this video to help you get it installed and there's a link in that video's description you can use to download any files you'll need assuming you have done that part i'll head back to report builder right click on my data sources folder and choose to add a data source i'll call this one movies and i'll make sure to use an embedded connection which points to a microsoft sql server and then i'll click the build button to help construct my connection string i'll type in a shortcut to my local host so dot backslash followed by the name of the instance of sql server i'm using which is sql 2017 having done that i can click on the drop down arrow towards the bottom of the dialog box select my movies database click ok a couple of times and there's my data source created next i can create my data set by right clicking on the movies data source and choosing add data set i'll call this one films and then click the query designer button at the bottom to get a bit of help building up the select statement from the tables folder i'll expand the film table and i'd like to select the title the budget dollars box office dollars oscar nominations and oscar wins i'm also going to apply a filter by clicking the add filter button over here and i only want to see films in this list if they have won at least one oscar so i'll change the field name to oscar wins the operator 2 is more than or equal to and the value i'll type in the number one and then i can click ok to create my select statement click ok again and there's my data set created next i'll create a table to display some of the columns i've selected in my data set i'll start by getting rid of this placeholder title text box and then i'll right click into the page footer and choose to remove that as well then i can right click somewhere in the report body and choose insert table i'll assign the title the oscar nominations and the oscar wins columns to the table to begin with just a quick little bit of formatting required to make sure that when i run the report i can actually see all the rows of data you may be familiar with this little bug when you run a report for the first time and it doesn't display most of the data in your table this annoying little bug a quick way to solve it if i highlight all the cells in the table in design view and then switch away from the default font to any other and then simply back to the default font again or any other font you might want to use if i then run the report it does actually display all the information if i head back to the design view i can then change some basic formatting for the column headers just to make those stand out and then that's the basic form of my table created the next thing i'd like to do is add an extra column to the table which keeps track of the running total of oscars won by all films so to do that i can head back to the design view i'll need to insert an extra column into the table first so i can right click in the existing column and choose insert column to the right then i can right click into the new detail cell and choose expression to launch the expression builder the function i'm going to use to do this you'll find in the common functions category in the aggregate section and the function's name is called running value so i'll just double click on the running value function to insert that and open up the round brackets then i've got three things to specify first of all i need to specify which column or which calculation i'm trying to calculate the running value of secondly the aggregate function i want to use so whether that's a sum or an average min max count etc and finally what scope i want to calculate the running value over the first thing's fairly straightforward i want to keep the running value of the oscar wins column so i'm going to head back to the fields category and then just double click oscar wins then i can type in a comma and specify the name of the aggregate function i want to use if you're not sure what those aggregate function names are have a quick look back at the aggregate category and essentially anything which sits above running value in this list is valid for the running value function so i can find a running average i'm running min or max or a running sum etc in this case i want the name of the sum function and i can either type that in or i can double click the sum function and then i'll also need to get rid of the open round bracket that's appeared ordinarily when you use the sum function you then pass in the name of the column you want to find the sum of but here for running value is just the name of the function you require you might be able to make that out in the example section in the bottom right hand corner there so i'll take away the extra open round bracket that's appeared i need another comma and then the final thing to specify is the scope over which i want to calculate the running value if i don't want to specify a precise scope i can enter the value nothing and that will mean that it will use the default or outermost scope so what that will essentially do is calculate the running value for everything in the film's data set in this particular case if i then close the round brackets the entire expression will look like so and then if i click ok have a look at the results of the x of the expression by running the report and i'll find the running total of oscar wins in this branch new column there are a few other options we can use to specify the scope of the running value function just to demonstrate what a couple of those options are i'm going to head back to the design view and then rather than using the value of nothing for the scope let's enter the name of the data set which in this case is called films i'll right click back on the same expression and then choose to launch the expression builder again and then rather than having the value nothing here i want to enter the name of the data set when you enter the name of a data set it has to be entered as a string so you need to enclose it in some double quotes and it's case sensitive as well so do make sure you match the case of the data set name that you've created the end result looks like this this makes absolutely no difference to the result here in this particular case entering the value nothing essentially sets the scope to be the scope of the data set name so if i run the report the end result is exactly the same it's just accumulating the value of the oscar wins column in this extra column in the table an alternative way to do that again is to enter the name of the data region that the function operates within so in this case it could be the name of the table that this column belongs to so back into the design view to find the name of the table there are several ways to do it i could click into a cell in the table first and then right click on one of the gray boxes around the outside and choose to view the tablex properties dialog box the name of the table will appear at the top of the general page you can modify that to a more descriptive name if you like but i'm going to leave mine as tablex1 so again if i wanted to modify my expression i can head back to the expression builder and then rather than entering films i can enter tablex 1. again case sensitive so make sure you match the case of the table name having done that i can click ok and then run the report again and again it doesn't affect the result it's the exact same accumulated value of the oscar wins column but just a couple of other options for entering the scope for the running value function let's have a look at some of the other aggregate functions we can use with running value i'd like to add another column which shows the running average oscar wins for the films in the table so let's head back to the design view and i'd like to add an extra column to the table first so i can right click and insert a column to the right then i'll right click into that empty text box and choose expression i'll head back to the common functions aggregate category double-click the running value function head back to the fields list and then double-click oscar wins i can then type in a comma and then if i want to find out the names of my aggregate functions again i can head back to the aggregate category and the average function is the first one at the top of the list again i can either type that in myself or double click on its name and then backspace off the extra open round bracket there i'll type in a comma and then i can enter one of three things either nothing films or tablex1 it doesn't really matter which i'll enter nothing again for this first example the entire expression should look like so so really the only thing we've changed from the initial example we looked at is the name of the function that we're applying if i click ok and then run the report i'll find this column keeps track of the average oscar wins for everything in the table i think a little bit of formatting and modifying the column headings would be sensible at this point so let's just quickly click back on the design view i'll select the cell containing the expression i've just created and then i'm going to change the num the format to the number format which will provide me with two decimal places and then i'll change the column headings i'll call this first one i'm running total oscar wins and then the second column i'll call this running average oscar wins and then a quick look at the results that looks a little bit neater the running average column is really just taking the running total for that row and dividing it by the number of rows encountered up to that point so the first running average is essentially 3 divided by 1 the second is 6 divided by 2 17 divided by 3 etc if you wanted to you could actually keep track of the running count of rows in a couple of different ways let's have a look at how we can do it using the running value function first if i head back to the design view i'll insert a new column into the table and then right click into that new column and choose expression i'll head back to common functions choose aggregate and then double click the running value function it doesn't really matter which field i pick this time to to count the number of values in but just for consistency i'm going to insert the oscar wins column again i can then type in a comma head back to the aggregate category and the function i'll use this time is called count so i can double click on that and then backspace off the extra open round bracket one more comma and then i can just specify the scope again for consistency i'll just enter the value of nothing for the scope close the round brackets the entire expression looks like so and then if i click ok and run the report again we'll see the running count of rows in the extra column that we created now that's a fairly common thing to want to be able to do we've used the the count of the rows in a few other videos in this playlist um there's actually a slightly easier way to do this rather than using the running value there's a dedicated function to calculate the running count of rows in a data region let's head back to the design view and then i'm going to right click onto the cell that i've just created and then choose to view its expression i'm going to take away everything we've done up to that point so far so i'm back to the original equals operator in that box then head to common functions miscellaneous and you'll find there's a function in there called row number and the row number function does exactly what we've just done it it returns a running count of all the rows in the specified scope but in a much simpler way if i insert the row number function all i have to do is specify the scope for which i want to calculate the row number again i'll go for nothing so the same scope we used for the running value function click ok run the report and i get exactly the same results just in a much simpler way the running value function doesn't always just have to refer directly to a column it can also include expressions let's head back to the design view and i'm going to replace this expression for the row number that i've just created with the value of the budget dollars column and then next door to that i'm going to add in the box office dollars what i'd like to do then is add an extra column which calculates the running total profit for the films so it's not the value of either of these two columns individually is the value of the box office minus the budget so i can right click and insert a new column to the right i can then right click into that new cell and choose expression and then i can go to common functions aggregate double-click running value and then head to the fields list i'll insert the box office dollars field first and then simply subtract from that the value of the budget dollars field so rather than just referencing a single field you can create an expression i can then type in a comma say which aggregate function i want to use i'll just type in the name of the sum function this time followed by another comma and then the name of the scope which i'll enter the value of nothing again close the round brackets and the entire expression should look like so if i then click ok and then i'll add a quick column header here i'll call this one running total profit and then we can run the report and we'll see the value of the profit accumulating or um reducing depending on whether the film made a profit or a loss of course but the point of this is showing you that you can add an expression to the value that your running value function uses we can use the same approach to calculate the running average of profit let's head back to the design view and then rather than writing out the whole calculation from scratch let's cheat a little bit i'm going to right click on the existing expression and choose to launch the expression builder then i'm going to take the entire expression and copy that by pressing ctrl and c then i can insert a new column to the right hand side and then right click into that new cell choose expression and then paste in my new expression replacing the existing equals operator there rather than using a sum i can simply replace that with a vg instead so the entire expression looks like so it's the running value of box office minus budget taking the average over the scope of nothing if i then click ok and then add a quick title running avg profit and then run the report and the end result eventually will show us the um the running average of profit so the running average we've just calculated essentially takes the running total of the calculation so that's the running total profit divided by the number of rows encountered so far so the running average profit on the second row should be exactly half of the running total profit um the value on the third row should be one third of the total profit etc so that works quite nicely that's exactly what we wanted for that particular example but it's not always appropriate depending on the type of calculation you're trying to create let's show you this with a simple example first i want to find the um the win rate for my films based on the number of oscars they've won and were nominated for so we're going to take the wins and divide that by the number of nominations and then i want to find the running average um win rate for all the films let's start with the basic win rates calculation first if i head back to the design view i'm going to insert a new column to the right of the oscar wins and i'm going to add a basic expression in here without using the running value function i'm just going to divide wins by nominations so in the expression builder i can head to the fields list double click oscar wins divide that by oscar nominations so the entire expression is nice and simple just like so i'll click ok i'll apply some basic formatting to that as well i'll apply the percentage format and then increase the number of decimal places a couple of times give it a quick column header i'll call it win rate and then run that report and there's my individual win rate for each film so now let's take the running average of that win rate expression if we head back to the design view we can insert a new column i'll insert that to the right of my win rate column and then i can right click into that cell choose expression and insert my running average function of my running value function i should say from the aggregate category i'll recreate the same expression i've just created so i'll go back to the fields list double click oscar wins divided by oscar nominations type in a comma the function i'll use is avg followed by another comma and then i'll do that using the nothing scope and then i can close around brackets so the final result of the expression should look like so if i click ok and then i'll apply some basic formatting as well just to make it a little easier to read when we run the report and i will find a column which on on the surface looks kind of plausible it looks at a glance sensible like the the overall average win rate for my oscars but that's not quite what it's doing it's the same as the running average profit it's taking the sum of the expression so far divided by the number of rows encountered so far so the the running average as we we're calling it for the king kong row is the same as 100 plus 75 so 175 divided by two so we're really getting the average of the averages which isn't quite what we wanted what i want to get is the sum of oscar wins so far divided by the sum of oscar nominations so far so i'm going to do that by heading back to the design view and i'm going to insert a new column to the right of the one we've just created and then we'll create another calculation which will demonstrate the difference between the two so i'll right click into that cell choose expression and the first thing i want to do is take the running sum of the oscar wins so i'm going to insert the running value function from the aggregate category again i'll go to the fields list double click oscar wins type in a comma type in the sum function and another comma and then refer to the nothing scope close around brackets so that's the running total of oscar wins i'll then divide that by the running value of the oscar nominations field type in a comma and then the sum another comma nothing and then close the round brackets so the entire expression in fact if i just make these appear on separate lines so i can see this a little more clearly hopefully we've got the running total of oscar wins divided by the running total of oscar nominations if i click ok and then apply the same formatting i'll apply percentage formatting with a couple of decimal places when we run the report you'll see there is a subtle but significant difference between the two columns we've calculated so the actual running average isn't just the average of the averages or the average of the the rates or ratios it's the overall total oscar wins divided by the overall total oscar nominations that same principle holds true for more complex examples of expressions next let's say we wanted to calculate the running average profit margin for our films i'll just start by calculating the profit margin for each film individually in a new column just as we did with the win rate earlier on in fact i'll start by tidying up this little section here let's head back to the design view i'm going to delete the first column which contained the incorrect version of the running average win rate so i'm just going to delete that one entirely and then re-label the correct version of that calculation as running avg in our spell running correctly running avg win rate and then i'll add a new column to the end of the table so i'll right click insert a column to the right and then launch the expression builder to calculate the profit margin to do this in a set of round brackets i'm going to refer to the box office dollars field subtract from that the budget dollars close the round brackets and then divide the result by the box office dollars and that will provide us with the profit margin if i click ok i'll apply some basic formatting so i'll apply the percentage formatting and then increase the number of decimal places and then label the column as profit margin and then run the report to check out the result and there we have it the individual profit margin for each film so again to calculate the running average profit margin isn't as simple as passing that expression into the running value function and that using the avg function if we do that then all we'll get is the average of the percentages we've calculated so far and that's not quite right what we want to do is take the running total box office minus the running total budget divided by the running total of box office so to make that work let's head back to the design view we'll add yet another column to the end of the table running after space here a little bit so i can right click insert a column to the right and then scroll along to find my new blank column right click and choose expression so the first bit in a set of round brackets i want to take the running total box office minus the running total budget so i'm going to insert the running value function from the aggregate category i'll refer to the box office dollars field followed by a comma the name of the function is sum so i'll just type that in another comma and then refer to the nothing scope and i'll close around brackets once for that running value function then what i want to do is subtract from that the running value function again so i can insert running value but this time referring to the budget dollars field so i can double click to insert the budget dollars another comma the function called sum another comma and the scope will be nothing again i can then close around brackets for the running value function and close another set of round brackets for that first half of the expression so that's essentially the the overall the running total net profit i then want to divide the result of that by the running total box office rather than writing that out again i'm just going to copy and paste the running value box office dollars some nothing so the end result should look like this get that's all centered on the screen so you can see the entire thing almost okay so the entire expression looks like so if i click ok to confirm that apply the percentage formatting again increase the number of decimal places and then i will type in a quick title running avg profit margin when we then run the report we'll get a new column showing the the average profit margin which isn't simply dividing the current profit margins by the number of rows encountered another potentially useful thing we can do is compare a running total against some kind of target so let's say for example we wanted to compare the running total of oscar wins against the sum of oscar wins for the entire table to make that work let's head back to the design view and i'm going to insert a new column next door to the running total of oscar wins so i'm going to right click at the top of that column insert a new column to the right i can then right click into the new cell and then choose expression and i want to start by calculating the running total of oscar wins so we know how to do that by now fairly easily we can go to the common functions category choose aggregate insert running value head to the fields list insert the oscar wins column type in a comma followed by the sum function another comma and then the name of the scope we want to operate over so i'll go with i'll go with nothing again which as we've seen multiple times essentially refers to the entire data set now i'd like to divide that running total with the actual total for all the oscar wins in the entire data set so i can divide that by and then i can insert the sum function again so i can type in sum and then open the round brackets this time and then i need to insert the oscar wins field now by default what that would do if i just close around brackets there the sum would operate under its default scope which would be each individual detail row in the table we've seen this in previous videos the sum of oscar wins on a single detail row is the same as just the value of oscar wins for that detail row i want to calculate the sum for everything in the film's data set so rather than just closing the round brackets there i'm going to type in a comma and then specify the scope for the film's data set so in some double quotes i can type in the name films and then when i've closed a double quotes i can close the round brackets so the entire expression should look like so if i then click ok i'll apply some basic percentage formatting with a couple of decimal places i'll add a quick column header progress to total and then run the report and we'll get a new column which shows you the increasing value of um of oscar wins towards the total of oscar wins for the entire table if i quickly switch to the last page the last film should hit 100 next i'd like to look at how the running value function works in a table which contains groups so to make that work we'll go back to the design view and then we'll make a modification to the data set to include a new field that we can group by so i'm going to right click on films and choose query and then i'm going to go to my genre table and i'm just going to include all the columns from the genre table by ticking the box next to the table name so i'll get genre id and genre i can then click ok and i'd like to create a brand new table to demonstrate this so i'm just going to select my existing table and shift that one down the page somewhat then i can insert a brand new table into the report body by right clicking and choosing in search table i'll assign the title and the oscar wins columns just to begin with making sure that i can see all the data when i run the report i'm going to highlight all the cells and change from the default font to any other and then just back to the default font and then i'd like to add a basic group so i'm going to use the groups panel at the bottom i'm going to right click on details one choose add group parent group i'll group by genre initially so i want to be able to sort by and view the name of the genre and i'll include a group header and a group footer once i've clicked ok i'm then just going to modify the group expression so that it groups on the genre id so i can right click on the genre group that i've just created choose group properties and then switch from genre to genre id you may find better performance by grouping on a simple numeric field like an id number rather than on a long text description particularly when you have lots of rows in your data sets so i can click ok a little tiny bit of extra modification here i'm going to copy these two column headers from the top of the table into the group header that i've just created and then delete the top row of the table entirely and then just change the column width a little bit and then some very basic formatting for the column headers and the the row headers there and finally run the report just to see what that looks like now let's calculate a running total oscar wins for the films if we switch back to the design view i can right click into the empty column at the end of that table choose expression head to common functions aggregate and double click running value i'll head back to the fields list and double click oscar wins followed by a comma and then the name of the function which is sum and then another comma now what we've done previously in most cases is reference the scope of nothing so that uses for a running value function that expands the scope out to the outermost level so basically it's the name of the data set that we're providing here by specifying nothing if we click ok and we look at the results of that we'll see that the oscar wins continues to accumulate past the end of each group so it essentially just carries on accumulating the oscar wins all the way through continuously to the end of the table what if we wanted to reset the count or the other sum at the beginning of each group in order to make that work we can specify the name of the group as the scope for the running value function so the name of the group is what we've just created down here it's called genre in this case if you wanted to change that you could right click on the group choose group properties and modify its name there just like you can modify the name of a table i'm not going to bother i'm going to leave that that scope name the same the group name is genre i can right click into the expression i've just created choose expression and then change nothing into the name genre don't forget it's a string of text that you're passing in this time so you'll need to enter the name in some double quotes and again it's going to be case sensitive so having done that if i click ok run the report again we'll see this time the sum of oscar wins resets at the beginning of each group and with a value for oscar wins for the first film in that group so there we go some basic examples of using the running value function of course you could use the group scope for any of the other examples we've used the running value function for in this video but at this point i think i'll let you play around with those for yourselves hope you found some of that useful thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 5,565

Keywords: report builder, ssrs, reporting services, sql server, rdl, paginated report, runningvalue, running total, running average, running count, aggregate, total, average, count, sum, running sum, wise owl, function

Id: JLqvjUula9U

Length: 32min 9sec (1929 seconds)

Published: Wed Jan 27 2021