So the video is all about creating conditional aggregate expressions we'll start by looking at how to nest an if function in a sum function and look at how to choose whether to sum.
The value of a field or a zero then we'll look at how to combine a count with an if function and look at how to use the nothing keyword rather than a zero to.
Make sure that we get the correct answer and for the final example we'll combine average and if functions so let's get started to get started i've created a new blank.
Report in report builder and the first thing we'll do is create a data source to connect the movies database if you'd like to follow along and want a copy of the movie's database you can use.
This video to help you get it installed and there's a link in that video's description that you can use to download the script file you'll need assuming you've done that part already.
Pointing to a microsoft sql server then click the build button to get some help with the connection string type in a shortcut to my localhost.backslash.
And then the name of the instance of sql server i'm using which is sql 2017 finally i can select my movies database from this drop-down list click ok a couple of times and that's.
The data source created next we'll need a data set so i'm going to right click on the movies data source and choose to add a data set i'll call this one films and then for a.
Bit of help we'll use the query designer to pick a few fields from the film table so from the tables list we'll go to the film table and i'm going to select the title.
Runtime minutes and oscar wins i can then click ok a couple of times and that's my data set created next i'd like to add a basic table to show the results of that data set.
So i'll start by tidying my report by
Removing the page footer and deleting this title placeholder text box then i can right click into the body of.The report and insert a new table and then i'll assign the three fields title then oscar wins and then finally runtime minutes.
So i'm very basic formatting to change the column widths i'll modify the font that i'm using so i don't encounter my font rendering bug that you may have seen yourself before.
A bit of basic formatting for the column headers and then i'd also like to display a grand total of the runtime minutes in a cell at the bottom of the table so.
I'm going to right click on the detail row insert a row outside the group below and then choose the runtime minutes field.
To create a sum of runtime minutes i'll add a basic title there that just says total and then just repeat some of my formatting so i'll go for the same background color and.
Bold text that i've got for my column headers and then if we just have a quick look at running the report to make sure that everything looks sensible we can.
Head straight to the last page and find we get our grand total right at the bottom of that column next i'd like to add two more totals to the bottom of this table.
I want to show the total runtime for films which have won at least one oscar and another to show the total run time for films with no oscar wins and to do that we're simply going to.
Nest an if function inside our summer
Function so back in the design view let's start by adding a couple of extra rows to the bottom of the table.Once i've done that i'll just add in some basic titles as well so i'll call this one a total for winners and then another one to create total for losers.
And then going to right click into the cell next to the total for winner's title and choose to build an expression so the main expression here of course is.
Just a sum function again so let's begin by saying equals sum and open some round brackets but rather than just referring directly to the field whose value we want to sum.
We're then going to begin an if function so i i f and then open up some more round brackets you may remember from the earlier video.
In this playlist how the if a function works there are three things to fill in your logical test the value of true followed by the value of false.
So the logical test in this case is whether the oscar wins field has a value of more than zero so i can go down to my fields category and then double click the oscar wins.
Field check if that is greater than zero follow that with a comma and if that's true i want to sum the value of the runtime minutes field so i.
Can double click runtime minutes one more comma and then i have to specify which value i want to sum if the oscar wins is not more than zero.
So a simple example here for the sum function i could just use a zero so if there are no oscar wins i'll add a value of zero to the sum i'll then need to close two sets of.
Round brackets and then while i'm here i'm going to select and copy that entire expression i can then hit ok and then right click in the cell below.
And choose expression and then simply paste that expression in but modify my logical test to check that the oscar wins value is equal to zero.
Having done that i can click ok again and then when i run the report and head down to the very last page i should find two new totals which if i added them up.
Uh roughly in my head should match the total without the if function you can use this same technique for any of the basic aggregate functions.
Available in report builder so for the next example let's count the number of films we have in total and then we'll count the oscar winners and the oscar losers separately.
Let's head back to the design view and i'm going to start by inserting a couple of new rows one for the total count and then one full account of oscar winners.
So i'll just add a couple of basic titles here let's call this one account and then this one count winners so adding the grand count is nice and easy i'm going to start by selecting the.
Runtime minutes field which will give me the sum of runtime minutes then i can click on that expression right click on it summarize by and then choose the count.
Function instead to count the number of oscar winners i need to nest an if function inside the count function and the simplest way to do this would be to.
View the expression we used to calculate the sum of winners copy the entire thing and then right click into the count winner's cell paste in what we've just copied and then.
Simply change the aggregate function from sum to count and this isn't going to work in quite the way you might expect let's.
Click ok and then run the reports again if we head back to the last page of the report we'll find that we don't have any difference between the total count and the count of oscar.
Winners and the reason for that is the value that we've used for the false part of our if function the count function simply counts.
The values that appear it doesn't care what the values are so we've provided a zero when there are no oscar wins zero is a value so the count function.
Counts zero as a value and tells us that in total there are twelve hundred films so what we need to do to make this work back in the design view.
Rather than using a zero for the false part of this expression if we head back to the expression builder change the number zero there for a value of nothing.
So nothing is the equivalent of null if you're familiar with nulls in sql while i'm here i'm just going to copy that expression and we might as well create the count.
For losers as well so let's insert a new row let's add a new row count losers right click into that empty cell choose.
Expression paste the expression in and then change our logical test to check where the oscar wins value is equal to.