Start learning with our library of video tutorials taught by experts. Get started
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.
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.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.