Compared to the movies one that we tend to use for our examples so if you do want to follow along you'll need to use the link in the video description to download a script file.
You'll need to open up this script file in sql server management studio and then execute it to create a new database called hogwarts with a single table in there called school.
The school table contains a list of harry potter characters as you can probably guess if we just take a quick look at the data that's contained in there you can see that each person has.
Their own unique member id but they also contain the id of the person to whom they report their parent id so you can see that these four people here report to id number one.
So i've created a data source to connect to the hogwarts database i've created a data set called wizards which simply selects everything from the school table and then i've created a basic report.
Table that shows every single column from our wizards data set if we just quickly run the reports at that stage you can see we've got a list of all the people as shown in this list.
Here in sql server management studio at the moment the report is showing the list of people based on the order they're stored in the underlying table but we'd like to change that so that the.
Data in the table is organized according to the hierarchy group information stored in the parent id column to do that we can create a recursive group in the table.
So back in the design view we can right click on the existing details group in the row groups panel and choose to view the group properties we can change the name of the group.
- Flow Control Combining And Separating Bundles of Data
- Excel VBA Introduction Part 58.16 - SQL for Excel Files - Basic Union Queries
- How To Design A Responsive Portfolio Website Using HTML, CSS & JavaScript Part 7 BlogSpot
Let's call this one people - ID Card Make
And then we can add a group expression to group on the member id now of course because each member id is unique in this table this won't actually.Have much of an effect by itself or indeed any effect at all if we run the report will see exactly the same results to create the recursive group we need to go back to the design view right click.
On the people group and choose group properties and then on the advanced page we can set the recursive parent to be the parent id field.
At this point if we click ok we can run the report again and now the date is organized according to the parent id column rather than the original order they were stored in in the table.
Now although we've grouped the data in the table correctly it's not immediately obvious at a glance that has happened so just to help our end user make sense of the organization of data in the table i.
Want to make a couple of small visual improvements i want to change the indenting level of the text in the name cell according to the level of the hierarchy and then maybe also the.
Background colour of the cells to do both of these things we need to rely on the level function to calculate the level of the hierarchy now we've covered this in quite a lot of detail in.
The original video so i won't cover this in too much detail here but just as a quick reminder back in the design view i'm going to right click on the cell containing the member id.
Field and then choose expression and then i'm going to get rid of the reference to the value of that field and replace it with the level function so level open and close some round brackets.
If i then click ok and run the report
We'll see that we get the number indicating which level of the hierarchy each person belongs to so now we can take that number and for.Example multiply it against a constant to increase the indent level of the name value in the name cell so let's select that cell in the table and then in the properties window i'm.
Going to find the indent category and i'll find the left indent property click on the drop down arrow there and choose expression we can then create a calculation i'm.
Going to use the c string function cstr and then in some round brackets we're going to use the level function open and close some more round brackets and multiply that by a constant value.
Let's say 10. i can then close around brackets and then concatenate that using the ampersand with the letters pt in some double quotes so the end expression.
Looks like so if we then click ok and we run the report again we should now see a different indent level for each bit of text according to the hierarchy level.
Changing the background color of the cells is kind of similar using the level function back in the design view i can highlight the entire row of cells there and then i can find the background color.
Property click on the drop down arrow next to that and choose expression this is a slightly less or slightly more low-tech solution i suppose i'm just.
Going to check the three different possible values in this table if the level is zero one or two give me different colors so i'm going to use the switch function.
To do that so equal switch open some round brackets and then the first logical test i'm going to check if level equals 0 followed by a comma.
Then i'm going to pick cornflower blue as the color for that level i can then type in another comma and then i can say level equals one.
And then i'll pick a different color for that let's go with light steel blue and then another comma and then for anything else i'm going to use the constant value of true.
And then i'm just going to make the background color no color or transparent so we'll get three different colors for the rows based on what level of the hierarchy they belong to you i'll just.
Tidy up a little bit here as well i'm going to get the the name column increased in width a little bit we don't really need to see the member id column any longer so let's.
Delete that and also let's delete the parent id column and then if we run the report again that looks a little easier to read i think so on to the point of the video how do.
We aggregate the data in these recursive groups let's start with a simple example i want a new column that gives us the total exam score for all the students in the.
Same house and then at the top of the list for all the students in the school back in the design view we can insert a new column into the table to the right hand side.
And then if we just try the simple approach if we use the field selector to choose the exam score field that will provide us with the sum of the exam score because we've grouped our table.
Selecting a numeric field now provides a sum automatically but that won't quite do what we want if we run the report we just basically get the exact same value as when we.
Reference the exam score field if we want to make this work so that the totals are calculated for each recursive parent then back in the design view we'll need to modify the expression in.
That cell so i'm going to right click on that and choose expression then we'll need to provide two more arguments to the sum function if you use the common functions category.
Here in the in the expression builder and look in the aggregate category and then select the sum function you might be able to make out down here that there are three different forms of using the.
Sum function the first one there is the simplest where you just reference a field value the second one allows you to apply a scope to that field and then the third one allows you to apply a scope.
And say that it's recursive and that's the one that we need so to make this work we've got to type in a comma after the reference to the value and then pass in the name in some.
Double quotes of the group so i call my group people so in some double quotes type in the people group name followed by a comma and then the word recursive not in double quotes so the.
Final expression looks like so and that point if we click ok and then run the report again this time we'll get the values we wanted one thing that's quite important to.
Realize about aggregate functions in recursive groups is that the function includes any values on the current row now because none of our staff members have any exam scores that's not.
Immediately obvious here really these totals for the staff members are the totals for the students exam scores in their group but let's see what happens if we try to.
Do the same thing with the spells column i'm just going to head back to the design view and i'm just going to modify the column title here to say some exam score.
