SSRS Report Builder Part 6.5 - Page Breaks and Headers in Table Groups

welcome to this wise our report builder 2016 tutorial in this video will explain how you can control page breaks and headers in table groups we'll start with a quick recap of creating a groups table including detail rows and then move on and explain how you can make sure column headers are repeated on each page of the report we'll explain then how you can add page breaks between each instance of a group and also how you can add page breaks before and after the entire group section of a table at the end of the video we'll look at how you can quickly export a report to excel and also how you can make sure that the excel worksheet names are created automatically using the values of the group so let's get started to get started I've created a new blank report and the first thing I'll add to it is a data source which connects to the Wisel movies database if you don't already have a copy of that database you can follow the instructions in this video which explained exactly how to install it and you'll find a link in the video's description which will give you access to a script to help with that assuming you've done that already let's right-click the data sources folder in the report data window and choose to add a data source if you have a previously used shared data source which points to the movies database feel free to use that one what I'm going to do is create a brand new one just in case you haven't done that before I'll call it movies and use an embedded connection then I'll click the build button and enter my server name which in my case is SQL 2016 training and then I can select the movies database from the drop-down list I can then click OK a couple of times and there's my data source created now I can create a data set by right-clicking on that data source and choosing add data set I'll call the data set film genres because that's what my dataset is going to include the ultimate aim here is to create a table which shows a list of genres and below each one the list of films belonging to it so let's head to the query designer and pick the relevant fields from our list of tables we can go to the genre table first and select the ID and the genre so of course the ID we'll use to perform the grouping and then the genre is what we'll use to display to the end user will then go to the film table and pick any fields we'd like to display so let's keep things simple let's have just the title the runtime minutes and the Oscar wins we can then click OK and ok again and then we can start building the basic table as I described in the previous video I'm going to build my table using a details first approach let's start by removing the title text box from the report and then right click and insert a new table the details first approach means simply that you starts by adding in the values for the detail rows so for each detail row I would like to see the film title the runtime in minutes and the Oscar wins having done that I can now apply a parent group which groups those details by each genre in this case I can do this by right clicking the details item in the group's panel click Add group choose parent group I'm going to group by these Jean ID and then choose to add a group header and a group footer if I then click OK I'll get my new items inserted into the table so I'll have a new column showing the genre ID and a group header and a group footer this isn't the best arrangement for this table as it stands so let's spend a little bit of time rearranging things to make things look more sensible if I run the report at this stage it won't look particularly elegant so let's spend a bit of time playing around to make it look a little nicer I'm going to start by removing the new column that's been inserted in favor of placing a banner heading which spans these columns showing the name of the genre so let's remove this first of all by right-clicking and choosing delete columns I'd like to place a new header cell or and you had a row in the group so identifying the correct row making sure I'm right-clicking on the current group header I can right click and use insert row inside group above so the important thing there is that we expand this program versus this bracket symbol indicating that that new row belongs to the group what I can then do is merge those three cells that I've created there together so I can select them and then right click and choose merge cells I can then use the field selector button to choose the genre field what I'd like to do is copy or even cut these existing column headings into the second row in the group header so that I can see each column heading for every single genre group so let's select those three cells and I can press control X to cut and then click into the first cell in this header row in the group and press ctrl V to paste I'll just edit the title for the runtime column so I can remove the word minutes so it's just as a runtime and then what I'll finally do is add an overall table header in this static row at the very top I'll merge these three cells together and then I'll type in a quick title films by genre if we apply some simple aggregates as well let's have a sum of runtime minutes by selecting the runtime minutes field from the bottom of that column and then also select Oscar wins for the Oscar wins column I'll type in a quick row title there call it total or totals for example and then all that remains just to make this look a little more readable is some basic formatting so let's change some background colors and font colors I can change the background color of the table header so very dark shade of blue I just might use my common blue and white color scheme I feel free to go with something a little more tasteful I'm sure you're far better at this part and I am but anyway let me quickly go through some basic formatting just to make things a little more readable and a little easier to understand when we perform the techniques with there the headers and footers so nearly there we can just do a little tiny bit more formatting and then just finally on the aggregates at the bottom there as well I'll change the column width for the film title as that's going to be there the widest column and then having done that let's give the report a quick run and see what it looks like so the main things I'm aiming to control in this particular video and making sure that we get the group header section repeated on each new page of the report if I click the next button to go to the next page I currently have absolutely no idea which group I'm in or what any of these columns mean I'd also like to make sure that when we reach the end of a group well you can see the totals that'll be automatically insert a page break before moving on to the next on row so let's start by making sure the column headers repeat at the top of each page if we head back to the design view let's look in the group's panel to see how we can do that first of all we'll need to switch the groups panel into Advanced Mode so looking in the top right hand corner of the group panel you should find this little drop-down arrow which you can click on and then choose Advanced Mode what this does is exposes all of the individual static items which belong to the table in the report so you should easily be able to make out the details item we've looked at this in previous videos if I select the details item you can see that it correspondingly selects that item in the table remember from previous videos clicking on an item in the group's panel is not the same as selecting the text box in the table and you can tell that by what's displayed at the top of the properties window if I click on the details item in the group's panel I've selected a tablet or table X member where I just select the text box itself in the table although it looks superficially the same it definitely is not what I've selected here is a text box it's very important for controlling this technique or for performing this technique that you select items in the groups so there are two rows that I want to make sure appear at the top of each page the genre group header and then the individual column headers I can identify those by selecting the static items first of all the one immediately above the details item to make that repeat on a new page I can scroll across to the properties window and find the repeat on new page property I can change the false to true simply by double clicking on it and then I can repeat that for the group header as well so making sure I select the static item here not the Djem ID group itself that's somewhat misleading that would select the entire group I just want to control the static item which corresponds to its header if I set the repeat on a new page property to true when I run the report again and if I switch to the next page I can see that this time each new page shows me which genre I'm currently looking at next I'd like to put a page break between each genre in the list so let's head back to the design view and this time we can right click on the genre ID item in the group's panel and choose to view its group properties if we head to the page breaks page I can choose to place a page break between each instance of a group an instance of a group for example would be western films so Western is one instance of a genre group and science fiction is another instance and drama is another instance and so on so this means I'll get a page break between each different genre I'll come back and explain the other two checkboxes in a moment but for now if I click OK and then run the report I should see this time if I start scrolling through different pages I'll find that where I have a very short group somewhere for instance there we go I'm at the end of the drama group so here I get the page break before moving on and beginning the next group at the start of the top of the next page so we've achieved the result of placing the page break between each instance of a group the other two checkboxes we saw in the dialog box for page breaks allow you to control the positioning of items that sit above or below with the entire group in the table if I switch back to the design view the only item we currently have in our table which sits above the genre group it's the overall table header films by genre let's add in a table footer as well as just so we can demonstrate how to separate the footer from the from the rest of the table let's right click into the bottom row of the table and choose insert row outside group below so this creates a new static item which you should see appears in the group's panel and it also appears below the existing genre group so it doesn't appear within the parenthesis or the bracketed area in the table rows I can choose to apply some different formatting to this let's make this a little more obviously like the overall table header and then let's call this something like the table totals and then we can apply some basic aggregate cities as well so let's have a sum of room time minutes and there's some of Oscar wins now currently if I run the report I'll find that the overall table header sits at the top of the very first page immediately followed by the first instance of my genre group likewise if I scroll to the very last page I'll find that my table totals sits immediately after the very last group the add the awful genre named for obvious reasons if I switch back to the design view and then head to the properties dialog box full of genre ID group I can right-click and choose group properties then on the page breaks page I can choose to place a page break at the very beginning of the first instance of the genre group and also after the very last instance so if I click OK now and then run the report I'll find it anything that sits above the genre grouping the table occupies its own separate page at the very start of the report and then finally right at the very end I'll have the overall table totals I'm not sure this is the best arrangement for this particular table but it's a simple and easy way to show you what those extra options mean the final thing I'd do for this particular table is apply some sorting so that we can see both the detail rows and the genre groups themselves sorted alphabetically as we've discussed in previous video is the best place to do that is in the query of the data set so you could add an order by clause of the data set query that's best for performance you don't always get that choice though for example if you were using the stored procedure to populate your data set all you'd be able to do is select the procedure giving you the set of results and you don't have the option to add to that query in this data set properties dialog if you don't have that option then what you can do is apply sorting using either the tablet or the group properties dialog boxes let's add sorting to our our group of properties so if I right click on my details group first of all and then head to the group properties dialog I can head to the sorting page and add a sort in ascending order of title and then I can do the same thing for the genre ID group if I right click on the genre ID group item in the group's panel choose to have group but I'll choose to view group of properties then head to the sorting page I'll find that a sort has already been added that was created at the point I created the parent group all I'd like to do is change the field that's used to apply the sorting from genre ID to genre instead if I then click OK and then run the report I can now see when I go to each page of the report that both the films within the group and the group themselves are sorted alphabetically quite a longer group the action group as is the adventure group but you can see that these are now changing as we click through it's worthwhile quickly mentioning that page breaks you add in the report design can also affect results when you export the report if I choose to export my report for example to Excel I'll save the new file on my desktop when I've done that if I navigate back to my desktop and double-click to open up this file what I should see is that I get a separate sheet wherever a page break curse so I get a really useful sheet films by genre at the start but then sheet 2 immediately jumps into the acting genre and then that shows me all of the the afternoon films at the end of that page XI 3 then goes on to the adventure group and so on and so on and so on what would be nice would be to create a page label or a sheet label which displays the name of the genre in order to do that if I close down this copy of the Excel workbook and then head back to the design view of my report what I can do is select my genre ID group and then look into the properties window on the right-hand side and find the group section if I expand the group section what I'm looking for here is the property called page name this is the property which affects the the labeling of any exported versions of the report it looks as though I can select a simple fields from the drop-down that's but unfortunately that's not quite the case so what I'm gonna do here is just have a quick jump into the expression builder if you click the expression option I think we've seen this in previous videos what this allows you to do is build up a calculation or an expression that's evaluated when the report runs all I'd like to do is select my genre name field on my genre field so to do that I can find the fields category in the bottom left hand corner and then just find the genre field and double click on it it's important that I double click this to have their their syntax inserted into the expression box of fields exclamation mark genre dot value if I then click OK and then run my report again this time when I export the results I'll go for Excel and then I'll just replace the existing version of the file so I can choose save and then yes I want to replace it when I navigate back to the desktop and then open them this file this time I should see that each sheet has a sensible label so I can clearly see which genre belongs to which page okay so that covers the basics of creating page breaks between each instance of a group and controlling the group headers appearing on each page as well I hope you found a couple of those ideas useful join us for the next video where we'll explain how you can create interactive collapsible and expandable groups in a table I hope you'll join into that one thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 35,439

Keywords: report builder, ssrs, sql server, sql, table, group, page break, export, excel, header, footer, worksheet name, microsoft, tutorial, training, online course, wise owl

Id: HbseMMxV6nE

Length: 17min 4sec (1024 seconds)

Published: Sun May 19 2019