Reporting Services (SSRS) Part 6 - Grouping in Tables

welcome to this wise L tutorial in this session we're going to teach you how to apply grouping to records in tables in reporting services in this session we'll look at how to apply a group to a table by creating a parent group we'll show you how to add subtotals to groups to see the average sum count etc we'll show you how to sort records effectively within groups and then finally we'll do 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 create slightly more fancy group tables where you see all the information actually collapsed in a nested tree and you can click on the expand symbol X with heritage name to show the 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 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 time I'll end up with a nice consistently formatted average running time the next thing I'd like to change about my table is the sorting of the records in it you might have noticed already that when you apply a group to a table it's automatically sort alphabetically by the grouped information so the directors names are already alphabetical order but inside each group the individual films within it aren't sorted alphabetically so that's what I'd like to change I can do that by going back to the design view now previously in another video we've shown you to sort the results in a table you can use the tablet's properties dialog box so you can right click at the top left-hand corner of the table and choose tablet properties and you'll find this a sorting tab at the left-hand side but confusingly when you get here it's actually empty although we've already seen that their columns whether the table is sorted by the directors name there's no information that dictates that now when you've got a group to table the tablet properties dialog box is really the best place just sort the table so I'm going cancel out of this one and instead I'm going to use the groups panel down at the bottom if I right-click on the director name group I can choose to view its properties and on the dialog box that appears there there's an extra sorting tab so I select this tab I can see why my table is automatically sorted in alphabetical order of derrida's name I'm not going to change this what I do want to do however is add some sorting to the details within each group so again I'm going to cancel out the dialog box for the director group properties instead head to the details item in the row groups panel if I right-click on this item and choose group properties again I can head to the sorting tab and choose to add a sort so it sorts by film name in A to Z order if I select okay head back to the preview of the report I should see now that my films are in the order I want the next step is to change how the table presents all of the information to us so you remember from the preview right at the start of the video we looked at two alternative options we can either add a page break between each director group and then you have to scroll between pages to see the other information or you can get the fancy version when you have the little expandable nested tree the page breaks are probably as you'd imagine slightly easier to deal with so I'll show you that version first if you go back to the design view of your report adding a page break at the bottom of each group is all done in the group's panel if you right-click on the director name group again and choose group properties you'll find as a page breaks tab on the dialog box if you select this one you can simply choose to add a page break between each instance of a group well ignore the other two tick boxes for now if you choose okay and then preview the report you'll simply see that each directors details now appear on an entirely separate page that's really straightforward making this table work so that you can collapse and expand the details in each group is a little bit more effort but start with I'm going to head back to the design view then I'm going to remove the page breaks that I've just added so I'm going to right-click the director name choose group properties and on the page breaks tab choose not to add a page break between these in terms of a group and then choose ok the next step is to control how this row of data the details row is collapsed and expanded and I know that I want to be able to select or click on the director name heading in order to collapse and expand the group so it's quite important to know what this text box is actually called the easiest way to do that is to select the text box in the table and then use the properties window it will tell you that the name of this text box is currently called the director name or 1 now that you know that piece of information you can change another property at this details row but you must do this from the groups panel if we select the details row here in the group's panel you'll see that the table actually does select something in the table as well if I click on the word director name you'll see that the table changes what it selects again but selecting the details row in the table using the mouse is not the same as selecting the details row in the group's panel I can show you that actually if I make the properties window pinned in place have a look at the list of properties that you can see at the moment this is where the details item selected down here if I go to the table and select the same cell apparently the same cell I got a completely different set of properties so it's important that I slept with details item in the grid panel to expose the properties that I want now in the properties window you might be displaying your properties either categorized or alphabetically if they're categorized and you'll find the two properties that you're interested in at the bottom of the list in the visibility section I'm going to make these details row change their hidden property to true and then the toggle item is the item that you'd like to be able to click on to make the rose visible again so if I click on the drop down arrow I should find in here an option for director name one which as we know is the name of this text box so this point we're but actually about half way there let me show you what I mean by that if you go to the preview this time I will find that the details rows of my groups are hidden and I have a plus symbol next to each directors name which I can expand the group with however I'm still getting the column headings and the subtotal row displayed we've sorted in the end results we can actually hide all of those things so to make that work we need to go back to the design view again now annoyingly the reason that we have in termed them as yet is because the rogue groups panel isn't actually displaying all of the information that it could at this point to expose all of the information you want to see have a look in the drop down arrow at the top right hand corner of the group's panel click on to that option and you'll find this one item you can select in there call that balanced mode and when I select that I get a bunch of extra options available now in the grades panel as you can see we've just modified the details item just above and just below the details item and two other rows that we want to hide the referred to as static rows if I select the static row just above details in the group's panel you can see that it highlights the first cell in the header row so I want to make that hidden true and make its toggle item direct to name one I can do that and likewise for the static row at the bottom or below the details item that refers to the footer row of the group I can make that hidden equals true try again and I can make the toggle item for this director name one as well finally when I go back to preview the report I can see that all of the items with any direct agree I collapsed when I click on the plus symbol it will expose everything as a final thought for this video now that you've seen how to group records by one column it's not too difficult to work out how to apply multiple groups so I've actually changed the dataset quickly for this report to include the country name and what I'd like to do is use the country name as an outer group for the details that I've already created so everything that I've done so far is absolutely valid all I'm going to do is group all of this information by the country name so if I right-click on the director name group in the panel at the bottom of the screen and choose to add a group which again will be a parent group and this time I'm going to choose to group all of this information on the country name I'll include a header and a footer as well and then choose OK and hopefully you can see that exactly the same thing happens now as I did when I first grouped by director name I get an actual column getting the country name column and I see an extra one of these brackets symbols on the left hand side so I essentially have nested groups it's going to do a couple of quick things to tidy up as I did earlier on I'd like to see the country name spanning the group rather than sitting in its own column so I'm going to select country name again merge those cells together and then delete the initial first column it might make the font size a little bit bigger for this particular column and maybe make the background color a little bit darker as well while I'm here this has been bugging me for the whole video I should have been done much earlier on if I won't have a quick look at the preview at this point what I should see now is that although my directors details are still groups in exactly the same way I get to expand and collapse sections I can also see that the direct groups themselves are now stored inside groups for countries I think one last thing I would probably do here is a sign a page break at the bottom of the country group so the country appear on its own separate page so again if I head back to the design view and then the same way as I did earlier I can right click the group heading so this time it's the country name group and choose properties I can choose to add a page break between each instance of the group if I choose okay and then preview the report one more time I can now see pls country occupies its own page and still having the ability to expand and collapse the director groups so there you go there's a summary of how to group records in a table in reporting services if you've enjoyed this training video you can find many more online training resources at ww-why 0 UK

Channel: WiseOwlTutorials

Views: 319,037

Keywords: microsoft, reporting services, grouping, tables, subtotals, wise owl

Id: keBOCdMfelU

Length: 21min 16sec (1276 seconds)

Published: Thu Aug 09 2012