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.
Whenever you make a new report in Report Builder it might first look like you have a header and a footer section, but you've actually just been given a footer, because in a normal report this is a title, not a header, so it will only show at the top of the first page. But we have a footer that is activated, and that will show on every page. Now, you can toggle headers and footers on and off from the Insert tab. Add a header, remove a footer, and vice versa. Both the Footer and Header sections have their own properties, so if I right-click in the footer area, I have footer properties here where I have such options like do I print on the first page, do I print on the last page? I'm just going to leave those as is.
Now, by default, the footer you given contains a text box with this expression that will just show the date and time that this report is generated. But most of the things you would add to a footer or header are simple, often just text. So first, you would insert a text box. This could just be regular typed text and can be formatted as such, but you can also add an expression. But even for that, the first thing you would do is insert a new text box. So, I'm going to draw one here. If I was comfortable with directly writing expressions, I could just type it in directly.
But what I'm going to do is right-click the text box and select the Expression option. I'm going to construct an expression here that says what page this is of what page total. So the first thing I'm going to write is just the word Page in double quotes because that's going to be a string, just regular text, and put a space after it and then the closing double quote. I'm going to join that together, to concatenate that, with a built-in variable that's accessible to us. If I look at built-in fields here, I have all these options here: Execution Time we're already using, Language, OverallPageNumber, OverallTotalPages.
I'm going to use OverallPageNumber. In fact, that's designed to be used in a header or footer and usually recommended that if you do it, you use it in a footer. I'll double-click that, and we have it entered up here into my expression Globals!OverallPageNumber. Another ampersand. We'll join this with an 'of' with a space on either side, and I'll tie that to OverallTotalPages, and click OK. So now I have that expression typed in the bottom here. I'd like to make sure that everything along the bottom is actually using the same font.
I'm just going to highlight all of them. I'll set them to Arial 8 points, and in the Align section, I'll align the Tops. I could go ahead and run this, but all we're going to get is page 1 of 1 right now. I actually really need a bit more data to work with. Probably the easiest way is to just drop in one of the report parts I created a little earlier. This should give us at least some data to play with. And if I expand one of the columns, it will push it out a bit. So let's go ahead and run it. Scrolling down, I can see that, for example, we've got page 2 of 57.
So, we're getting the full count of it on each page. Now, the number of pages this report will create will be different whether you're viewing it on the website or exporting it as a PDF or a Word document, but Reporting Services will figure the correct one out. It's all to do with how that report is rendered, which we're going to talk about next.
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.
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.