Column to get started with that i've already created a blank report and i've created a data source to connect to my movies database.
Just to take you through the basic setup of the data set i'll right click on movies and choose to add a data set i'll call this one films and then using the query designer i'm.
Going to pick every column from the certificate table we'll use that to create some row groups and then from the film table we'll select the runtime minutes field we'll use that to create.
Our aggregates either a sum or an average and then i'm also going to pick the release date field which we'll use to create some column groups.
Use the more than or equal to operator and then set the value to 2010-01-01 i can then hit ok to create the basic query it's not the release date itself that.
I'm particularly interested in what i'd like to do here is just make a quick modification to that field i want to add the year function around the film release date and then.
Create an alias for that column as release year so i'd like to create column groups based on the year that the films were released in having done that i can click ok and.
There's my basic data set created now let's add the basic matrix to display those results i'm going to right click into the body of the report and choose insert matrix.
And then in the rows group i'm going to select the certificate id field first so that will set the grouping and the sorting for that field then i'd just like to change what gets displayed.
- Publisher 2019 for Beginners Tutorial Office
- How To Create A Dynamic Appointment Scheduler In Excel Part 1
- How To Create A Mobile Inventory Manager App And Sync With Excel And AppSheet
Rather than show the certificate id i'd - ID Card Make
Like to show the certificate field for the columns group i want to select the release year and then i don't need to change anything about that.And then for the data cell i'd like to select the runtime minutes and then rather than showing a sum i'd like to display that as an average so i'll select the sum expression right click on.
It and choose summarize by avg having done that i'd like to apply some number formatting to that cell so i'll select the cell and then from the number format drop-down list choose number that.
Will just make sure we get two decimal places rather than the large number of decimal places we'd ordinarily get and then i just want to quickly make sure i don't encounter my font rendering.
Bug where not every value in the matrix will get displayed when you first run the report so i've selected all the cells and i'm just going to quickly change away from the default font and.
Then back to the default font just to make sure i can actually see some text when the matrix gets displayed when we run the report i'll just tidy up the formatting of these columns a little bit.
And then if we run the report we should see a basic matrix showing our simple data next i'd like to use the previous function to return the previous value.
For each cell in the matrix just so we can keep an eye on the original values though i'm going to copy the original matrix and then paste it in side by side with.
The first one rather than showing the average run time then i'm going to right click on that cell and choose expression and i'm going to wrap that existing expression within.
The previous function
So i'll just wrap the previous function around the existing average runtime minute so the final expression looks like so.Having done that if i click ok and then run the report again we'll see that we have changed the values shown in the matrix you can see that the the u certificate films show no.
Values whatsoever in that row basically all of the other values have been shifted down one row so the pg films are showing the value for you the 12 films are showing the value for pg.
And so on so we have technically returned the previous value but it's the value for the previous row not for the previous column which is what i wanted.
If we want to get the value for the previous column we need to specify the scope for the previous function so back in the design view the default previous function is using the previous.
Certificate group i want to return the value for the previous release year group so i need to know the name of that group i can see it here it's release year 2..
If i right click on that expression and choose expression in between those two close round brackets at the end i can specify for the second parameter of the previous.
Function in some double quotes the name of the previous group so release year two so the final expression should look like so.
Having done that i can click ok if i just move the position of that matrix to make things a little easier to read i think when we run the report again we can see that now we've shifted.
Everything along one column to the right basically rather than one row down as previously rather than just showing the value for the previous year it would be more.
Useful to show a comparison with the previous year so i want to take the value for the current year for that certificate and subtract from it the previous year's value so to do that back.
In the design view we can go back to the same expression right click choose expression and then i'm just going to quickly copy the average runtime minutes value function.
And then place that just after the equal sign at the beginning of the expression and then subtract from that the value for the previous year if we click ok at that point and then.
Run the report again we'll now see that we're showing the comparison from the previous year so we have a very quick look at the universal films for 2011 we'll see that those are.
11.75 minutes longer on average than films from 2010 and if you have a quick look at the actual values displayed there for those two years that's looking pretty accurate.
One thing to be slightly careful of when performing this sort of comparison is that blank cells or nothing will be treated as a zero so looking at 12 rated films in 2015 it.
Appears that there weren't any of those so here we get 151 minus nothing or minus zero so the end result is positive 151. we've got the opposite problem for 18.
Rated films in 2016. here we've got zero minus 168 so the end result is minus 168. if you wanted to exclude either or both of those situations where either the.
Current value is nothing or the previous value is nothing we can use an if function to get rid of those so back in the design view we can right click on the cell containing our.
Expression and choose expression i'm just going to take the current expression down a couple of lines and then at the beginning of this this expression after the equal sign i'm.
Going to write an if function so i i f open some round brackets on the next line i'm going to use the is nothing function to check if the average run time for the current value is.
Nothing so we can just copy and paste the average fields runtime minutes.value into the is nothing function then we can use an or operator and check.
Again using the is nothing function if the previous value is nothing it's going to change the width this dialog box so we can see hopefully everything on one single screen here.
I'm just going to copy the previous function and then paste that inside the round brackets for the is nothing function at the end of that line i'm going to.
Type in a comma and if either of those two conditions are true i want to return nothing as the result in the cell i can add one more comma otherwise i just want to return the result of the.
Expression we've already calculated i just need to close one more set of round brackets we might just about be able to see the entire expression there we go something like that.
Having done that we can click ok and then run the report again and this time we're going to see blanks where there was either no previous value to compare with or no current value to.
Compare with you can use this same technique when you have multiple column groups in the same matrix and to demonstrate this let's add a new field that will allow us to break.
Each year into four quarters let's head back to the design view first and i'm going to right click on my films data set and choose data set properties and then in the select list i'm going to.
