A couple of fancy things show you how to create collapsible sections in a table with groups which means the user can interact with it and then how we can create nested groups as well so let's.
Get started most reports and reporting services will be based on a simple table like the one shown here so you have several column headings parrot a name for naming Finland time and information.
Listed on different rows below headings then with this exercise is to move on from the basic table layout into groups tails and we can start by looking at how to show a table that's grouped by the.
Director name where each director appears on a separate page scrolling to different pages shows you the other information and once you've got that down we can actually look at how to.
Information contained in a group but the starting point for all these fancy things is still just a basic normal table so I see that first so to start with we've set up a basic.
Empty report and we've created the data source and data set as normal if we have a look in the report data window we need the data source and the data set which contains a director's name film name and.
Running time in minutes the next step is to start displaying information in a normal table so go ahead to the toolbox and double click the table tool I can then start.
Filling in the columns I want now the way I find the approach I find works best is to start from the inside of the group and work your way outwards so the outer part of the group is going to be.
The director name information for us the inner part the details are going to show each film's name and the film's running time in minutes that means I can get rid of this third empty unused column by.
Posts Related:
- Access 2021 Advanced Tutorial (Part 2 of 3)
- Understand and improve your processes with process advisor in Power Automate
- Delegate with Power Automate
Right clicking at the top of it and
Choosing to delete columns which will make the column widths a little bit wider so we can see the information so that point which is got a basic normal.Table the next stage is to group that data by the fields I want to group by and I can do this using the groups panel down at the bottom of the screen look in the row group section and you'll find.
There's a details item if you right click onto that option you'll find the ability to add a group in this case is going to be a parent group so that the group that I will create the directors.
Will become parents and the film's if I select this option I get a dialog box which allows me to set up the group settings I choose from this drop-down list which.
Feel that one's a group the data on this is going to be the directors name it also makes sense or at least it's easier to do at this point to add a header and a footer to the.
Group as well if I click OK I'll end up with a new column in the table containing the directors name information and I don't even see this from the app from the left-hand side of.
The table there's a little bracket symbol that spans three rows of data that's all a blank gray box here representing the header row a blank row the bottom showing the footer row and.
Then the symbol for details the dash - symbol representing the detail rows these are the ones that will repeat within each group so I quickly preview this should already be able to see the.
Information is organized roughly the way that I want all that remains now is to do a bit of tidying up and shifting things around the first change that I'm going to make is to delete the header.
Row for the entire table I'd only see
These pieces of descriptive text at the very very top so I'm going to right-click on the left-hand edge of the table itself and choose to delete that.Row the next thing is to make sure that I get my directors name rather than sitting in a separate column spanning the top of the the detail section and the easiest way by far to do that.
Is hover the mouse over one of these blank cells and just as you're normally use the field selector to choose director name so the moment I've got the director name in the in the table twice.
So what I'm going to do is delete this unused third column and now I have the information organized the way that I want the next thing is to merge these two cells together I can do this by.
Clicking and dragging across those two cells and then right click into any one of them and choose to merge cells a quick bit of formatting quick but a background color and fun color perhaps.
Just to make it stand out and at this point when I preview information look significantly different the next step this is a little slow your bit is to insert a row in between the directors.
Name and the details section so we can see the film name and film runtime minutes headers if I go back to the design view and then select something inside the table I can insert a row in.
Reporting services in one of two ways I can either right click next to the directors name and insert a row below or right click next to the film name and insert a row above now confusingly the.
Option that you choose to do those two things is significantly different it's easiest if I right-click now to the director name and then choose to insert a row.
Traditionally there are four options but you see there's only one way to insert a row below I can't choose to insert a row outside of the group below it must go inside the group below if I select that.
Option I'll get this new blank row with the ability to type in the the headers that I want I'm going to undo that temporarily and show you what would.
Happen if I insert a row above next to the film name notice these detail symbols like the - - - next in the film name row if I right-click on that row and choose to insert a row I've got two.
Ways to insert a row above I can either do it inside the group or outside the group now it's the context of the word group that's important here because I've right clicked on a details row the.
Context of a group is the details if I choose to insert a row inside the group then what I end up with is an extra details row I'll show you why that's a bad idea if I go back to the preview.
Because what it means is that I get a blank row above every single film name that's clearly not the right choice I go back to the design view and then I'm going to undo inserting now row if I.
Right-click next to the film name row again and choose to insert a row this time I'll choose outside the group above so it comes in without the detail symbol next to the row so there's only one.
Detail row still preview this one I can see that I get a blank row just at the top of the details section so all I need to do now is head in and type in a couple of basic headers fill name and.
Film one time the next thing we'll do is add a sip total to the bottom of each group at the begin with we'll add the total of the running time in minutes.
This is remarkably simple all you need to do is click into the cell you on the the total to appear in and again use the column selector button and choose the field you'd like to using the function.
By choose film run time in minutes I'll automatically apply the sum function to that field I look at the preview the report the bottom of each group now shows me the total running time in.
Minutes for each director if I head back to the design view I'll show you that some isn't the only function you're allowed to use if you right click on to the textbox containing that function.
There will be an option called expression if you select it you'll display the expression builder dialog box showing you how that formula has been calculated now without getting into.
Too much detail here we're going to go into a much more much more detail in a later video but I can show you that if you want to see the list of other functions available look in the bottom.
Left hand corner of this dialog box you see there's a common functions folder now when you're using a grouping grouping level in a table you're aggregating data as a category here.
Called aggregate we select that category here now you'll see a list of all of the other useful functions you can use at this point so we've just seen some and.
The use of one might be the AVG or average function you can simply overtype the sum function at the top of the dialog box with a new function you want to use if I choose ok and then choose to.
Preview the report this time you'll see that now we're seeing the average running time in minutes for each director you could do with them a little bit of tidying up with a bit of.
Formatting but we can do that fairly easily as well head back to the design view right click on the table cell again and choose textbox properties the number tab on the left-hand side of the dialog.
Box then allows you to specify a number format and choose how many decimal places to show I choose okay and finally preview the report one more.
