Start learning with our library of video tutorials taught by experts. Get started

SQL Server Reporting Services in Depth

Using page breaks


From:

SQL Server Reporting Services in Depth

with Simon Allardice

Video: Using page breaks

It's always worth bearing in mind that the Report Builder layout is not strictly WYSIWYG; it's not what you see is what you get--it can't be. You don't know what you're going to get until this runs. Now I've created a simple report with one table on it that's showing products grouped together into categories. Nothing magical here, but if I take a look at it, it looks like product name is a little too small. We could do with dragging that a little bit wider. So I'm going to go back into Design view and start to move this out, give myself a bit more room. See, because the assumption is that the most common way to view these reports is on the web, it will happily expand left or right and up to down as much as you need to.
Expand all | Collapse all
  1. 12m 6s
    1. Welcome
      1m 9s
    2. What you need to know
      1m 36s
    3. Exploring SQL Server Reporting Services components
      4m 17s
    4. Reviewing SQL Server versions
      5m 4s
  2. 49m 41s
    1. The elements of a report
      3m 10s
    2. Creating a report with Report Builder
      12m 11s
    3. Grouping table regions on a report
      6m 1s
    4. Joining data from multiple tables
      4m 33s
    5. Formatting report elements
      4m 34s
    6. Using functions in a report
      11m 0s
    7. Displaying data in a matrix
      8m 12s
  3. 24m 9s
    1. Filtering data and adding parameters to a report
      5m 35s
    2. Customizing report parameters
      5m 4s
    3. Sorting data in a data region
      4m 7s
    4. Applying interactive sorting
      4m 57s
    5. Creating a drillthrough action to connect reports
      4m 26s
  4. 49m 57s
    1. Introduction to charting in Reporting Services
      4m 16s
    2. Creating a column chart
      8m 35s
    3. Adding a generated average to a chart
      4m 5s
    4. Creating a pie chart
      8m 19s
    5. Using sparklines
      6m 38s
    6. Adding a sparkline to a drilldown matrix
      14m 34s
    7. Adding data bars
      3m 30s
  5. 21m 48s
    1. Adding indicators to a report
      7m 52s
    2. Using and configuring gauges
      5m 30s
    3. Using maps in Reporting Services
      8m 26s
  6. 38m 14s
    1. Creating modular reports with report parts
      4m 36s
    2. Adding and updating report parts
      4m 37s
    3. Using subreports and nested regions
      4m 28s
    4. Configuring headers and footers
      3m 9s
    5. Printing and exporting reports
      3m 45s
    6. Using page breaks
      5m 37s
    7. Creating and using shared data sources
      8m 11s
    8. Creating and using shared data sets
      3m 51s
  7. 27m 20s
    1. Organizing reports in Report Manager
      3m 1s
    2. Adding users and configuring report security
      5m 24s
    3. Configuring subscriptions
      5m 13s
    4. Creating a linked report
      4m 8s
    5. Using Report Designer in SQL Server Data Tools
      9m 34s
  8. 1m 2s
    1. Goodbye
      1m 2s

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
Please wait...
SQL Server Reporting Services in Depth
3h 44m Advanced Dec 13, 2012

Viewers: in countries Watching now:

Discover how to create, manage, and deliver interactive reports—not just to print, but to dynamically explore enterprise-level data—with Reporting Services in SQL Server. In this course, author Simon Allardice concentrates on using Report Builder to build and format reports from a variety of data sources, but also shows how to perform basic administration tasks such as granting user access and organizing reports in the Report Manager. Plus, learn how to add interactive sorting and filtering functionality to your reports, and create column and pie charts to better express your data.

Note: These tutorials are applicable to both the 2008 and 2012 versions of SQL Server.

Topics include:
  • Understanding the elements of a report
  • Grouping table regions
  • Joining data from multiple tables
  • Displaying data in a matrix
  • Customizing report parameters
  • Filtering and sorting data
  • Creating charts
  • Adding sparklines and data bars
  • Creating at-a-glance reports with indicators
  • Using Maps in Reporting Services
  • Configuring report security
  • Printing and exporting reports
Subjects:
Developer Databases
Software:
SQL Server
Author:
Simon Allardice

Using page breaks

It's always worth bearing in mind that the Report Builder layout is not strictly WYSIWYG; it's not what you see is what you get--it can't be. You don't know what you're going to get until this runs. Now I've created a simple report with one table on it that's showing products grouped together into categories. Nothing magical here, but if I take a look at it, it looks like product name is a little too small. We could do with dragging that a little bit wider. So I'm going to go back into Design view and start to move this out, give myself a bit more room. See, because the assumption is that the most common way to view these reports is on the web, it will happily expand left or right and up to down as much as you need to.

So if I drag this out and keep dragging it out this way and then select this column, give myself a bit more room, if we go ahead and run it, it looks pretty good here. But if I view the print version of this, jumping over to Print Layout--I don't actually have to go and export to PDF, although I could-- Print Layout thinks about it, but it's now transferring this onto multiple pages. I'm only getting three columns here. I jump ahead and there is Size and List Price. Yeah, this is not exactly what I was looking for.

And the thing is, as you add elements to the page Report Builder, will not stop you from just getting wider and wider. So, if you're creating a report that you're targeting for printing, it's your responsibility to restrict the layout so that it always works. And your first challenge is always make sure it's not too wide. Now, by default, new reports are created in portrait orientation, and if you do need a bit more width, one thing you could do is change the orientation of the page from portrait to landscape. And now to do that, you need to find a blue area, not the actual report layout itself, but just off the side. Right-click it and go to Report Properties.

Here is the option to go to Landscape and I click OK, go ahead and run this again. Now I still have Print Layout selected so I can see this, and I can see it's made a difference, but I'm still losing a column off to the right-hand side. But with a little bit of moving things around. I should be able to fix that. I'm going to bring this back in a little bit and test this again. Much better. Now I've switched to landscape orientation and I'm actually getting all of this on the same page. So be prepared to do a little bit of testing before you're actually know for sure how this is going to work.

On that Report Properties section is information not only about the orientation but also about the margins, which by default are 1 inch on either side. Now the next most common need when we're targeting a report and printing is to have more control over page breaks. Say for example that what I wanted to do is start breaking these apart by category. I want to have each category showing up on its own page instead of just all in a row. Well, one thing that strikes people as odd occasionally is you will find there is no page break option in the Insert tab, because instead you add a page break to another component. Most commonly, you're going to add it to a data region.

In my case, I want to actually have a page break between each group in this table. I want each new category to start on its own page. Well first, I might try seeing if that's in the properties of the table itself, in the actual Tablix component. As always, it can be a little difficult to select. One of the most reliable ways with the Properties window open, I'll select Tablix and jump to the Properties pages. There are page break options here, but they affect the larger table by itself. So add a page break before, add a page break after.

That doesn't mean each individual category; it means the entire table--not what I want here. Though there are some useful options for, say, repeating the header rows on each page. So maybe I think perhaps it's on the actual row itself, the row that represents the actual category. What happens if I select the row and grab that? Well, there is no options there, and the only option I have is the general Tablix Properties again. So it's not obvious where I would select to do a page break for each category, but I can. It's actually in the Grouping section.

So from the View part of the Ribbon, I need to turn on Grouping, which is how it describes how this table is grouped together. In my case, I dragged on ProductCategory_Name. Here is where I apply the page break settings. If I right-click this, I'll find the section called Group Properties, and in that I'll get a Page Break option. So if select between each instance of a group, we have also at the start and also at the end, but that's fine. I'll click OK, and let's preview this. We have the first categories showing up, Landscape Orientation with all of its data, and then starting to go through each individual one.

Now, the other way to add a page break is not directly, but we can add one to a rectangle. So if when laying out a page we wanted, say, to have a large cover title, but to have a page break between this and then the table I could insert a rectangle. It doesn't have to have anything in it. Then right-click the rectangle, click it's Properties, and we have a Page break option here, so Add a page break after. Click OK. Go ahead and run it.

Now the first page is just cover page, and the next page is where our table begins. Now one of the downsides of working with page breaks is you really need to be focused on these for printed versions of your reports. If I switch back to the web layout, it becomes kind of annoying that for web use, which is what I'm looking at here, I first have to worry about pages before I can go and see the next amount of content. So for web use multiple page breaks can actually get quite annoying. But when you're focused on printing, this is the way to add them.

There are currently no FAQs about SQL Server Reporting Services in Depth.

Share a link to this course
Please wait... Please wait...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.
Upgrade now


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Upgrade now

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed SQL Server Reporting Services in Depth.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Welcome to the redesigned course page.

We’ve moved some things around, and now you can



Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked