To take you through the basic date setup for the data set and the basic matrix i'm going to right click my movies data source and choose to add a data set i'll call this one films.
And then using the query designer i'm going to pick all of the columns from the certificate table all the columns from the genre table and then from the film table i'm going to.
Pick the runtime minutes and the release date i'm also going to add a basic filter to limit the number of rows i'm working with so i'll add a filter here based on.
The release date field from the film table and then i'm going to use the operator that says is more than or equal to and for the value i'm going to use.
To the release date field so i don't care about the actual release date for the films i want just the year so i'm going to wrap the year function around that and then provide an alias we'll.
Call this one as release year okay so having done that we can click ok and then we can set up the basic matrix i'm going to start with just a single row group just a quick reminder of what.
The original video displayed so i'm going to right click in here and choose to insert a new matrix in the rows group i'm going to select the genre field.
And then i'm just going to modify the row group so there's not grouping on the name of the genre i'm going to base this grouping on the genre id which is a bit more efficient.
The sorting will still be based alphabetically on the genre name though i'll do a similar thing for the columns i'm going to select the certificate id and that will set the grouping based on.
- Python Part 17b - VBA using pywin32 Office
- Design Apple Website Hamburger Menu Animation Using HTML, CSS & JS Part 2 BlogSpot
- Create An Excel Delivery Application With Mobile App Sync, Scheduler & Dashboard FREE DOWNLOAD Office
The certificate id and also set the - ID Card Make
Sorting based on that field and then i'm just going to change the value displayed here to show the name of the certificate rather than its id number.Then for the data cell i'm just going to choose to show the runtime minutes and that will provide a sum next i'm going to just highlight all the cells in the matrix there and i'm going.
To make sure that i change the font from the default to any other and then back to the default font that's just to clear up this font font display book which you may have.
Encountered when you run a report not all the text in your table or matrix gets displayed and then if i run that there we go there's the basic matrix created.
In the original video that this question appeared on we looked at how we could show these values as a percentage of the column total just to demonstrate how that works i'm.
Going to add an extra row into the matrix which shows the grand total for each column so back in the design view i'm going to insert a new row into the matrix i've right clicked on the group.
Row there next to genre and i'm going to choose to insert a row outside the group above i'll just add a quick header here that says grand total.
And then next after that i'm just going to select the runtime minutes field again which generates the sum of runtime minutes and if i run the report again we can see that that gives you the grand.
Total of all the runtimes for that particular certificate what we then needed to do was divide each of these values by the grand total for the column.
I'm going to start by actually showing
You how you can display the gram total for the column in each of these cells so let's head back to the design view and i'm going to right click on the data.Cell which contains the original sum of runtime minutes for each individual genre and then choose expression the idea here is that i want to expand the scope over which the sum is being.
Calculated so it's not the sum for just that specific combination of certificate and genre i want the sum for the entire certificate and to do that i can specify the name of the certificate id group in.
The second parameter of the sum function so i can type in a comma just before we close the round brackets and then in some double quotes we can type in the name of that group certificate id.
If we then close the double quotes the m function looks like that and then if we hit ok and run the report we'll see the grand total for each certificate appears next to every single.
Genre in the entire column now ideally i only wanted that to appear next to genres which actually had a runtime for that particular certificate so let's just head back to the design.
View i'm going to right click on that same expression and choose expression and then we're going to add an if function at the beginning of this i'm going to say iif.
Open some round brackets and then on the next line i'm just going to quickly copy most of this function so some fields runtime minutes value and i'm going to pass that into the is.
Nothing function so i'm going to say if is nothing open some round brackets sum of runtime minutes and i'm going to close two sets of round brackets.
Followed by a comma and if that's true i'm going to show a value of nothing in that cell followed by another comma i'm just going to tidy up my indenting.
There and then finally i need to close an extra set of round brackets for the if function so if the original sum of runtime.
Minutes for that particular cell was blank then show it blank otherwise show the grand total for that column so if we hit ok and then run the report again there we.
Go we've got the grand total for that particular certificate next to every cell which would have originally shown a value to calculate the percentage of the.
Column total then we take the original sum of runtime and divide it by the value we've just calculated but before we do that i'd like to show you what happens when we add a second row group.
So i'd like to group each of the genres by the year in which the films were released i'm going to head back to the design view and i'm going to select the matrix.
And then in the row groups panel i'm going to right click on the genre group and choose to add a new parent group to that i'm doing this because i want to easily.
Be able to add a group header row so i'm going to check the box for a group header and then select the release year field from the drop down list there i can then click ok.
I'd like to show the grand total runtime for each year at the top of that column as well so in between the grand total and the actual value for the genre i'm going to select the sum of runtime.
Minutes field again and then i'm just going to add a new header there that says year total i'm just going to change the header there i'm going to take away the the.
Release year header from that cell and place it in this one instead okay a little bit of basic formatting just to help this read a little more clearly i'm going to change some of the.
Background colors and font colors here as well just to help read and see what's going on and then we're nearly there.
Okay so at that point if i run the report again we can hopefully see that each individual cell is still showing the grand total for the entire.
Certificate so it's not showing the grand total for that certificate and year combination that should be 291 for 2010 animation movies we're still seeing the grand total of all.
Uh universal films for the entire column so how can we change our expression so that each cell shows the total for the year rather than the grand total of the entire column.
You might expect we could just modify the scope parameter to refer to the year group but that doesn't quite work let's head back to the design view and i'm going to right click on the expression.
Cell and choose to view the expression and then let's change the certificate id group scope to refer to the release year scope instead so i'll change certificate id to refer to release year.
If we hit ok at that point i just noticed before as well that i wasn't showing the release year title i suspect that's to do with the font rendering books let me just quickly solve that by.
Switching back and forth between our two different fonts when we run the report now what we should see as well as the release year title.
Thankfully that every single cell in each year group is showing the same value so the grand total we're seeing here now in each cell is the total for everything.
