Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.
A common feature to most reports are headers and footers that describe the report, and supply additional information such as the page numbering or print date. In this lab we’ll look at ways to customize the header and footer.
We’ll start by creating a basic report, then adding the headers and footers to it.
Step 1. Add the report
As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.
Step 2. Set the data source.
Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.
Step 3. Setup the query.
In the query builder, we’ll be using one of our views. Enter this SQL statement:
SELECT [FiscalYear]
,[ProductCategoryName]
,[ProductSubcategory]
,[Region]
,[TotalAmount]
FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
and click next.
Step 4. Set the format.
For the report type we’ll use the simple Tabular format, so just click Next.
Step 5. Determine field placement in the report.
To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.
Step 6. Select the formatting Style
Once again we’ll go with Corporate for the style and click Next.
Step 7. Name the report.
Finally we’ll give the report a name of “Regional Sales by Subcategory Headers and Footers” and click Finish.
Step 8. Format report columns
To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)
Previewing the report shows our data. There’s a lot of it, so let’s say we are the sales manager and we want to apply filters so we are only looking at pieces of our sales.
Step 9. Add a header area.
To add a header area to the report, simply right click anywhere outside the report body and select “Add Page Header”.
Step 10. Add a title.
A blank, white canvas should appear above your report body. Here you can create a header. Go to the toolbox, and drag in a Text Box. In it enter “Regional Sales Report”. Click on the text box and grab the sizing handles to enlarge it. Sometimes this can be a little tricky, if you click inside the text box it assumes you want to enter or edit the text and puts you in edit mode. You have to click right on the edge of the text box area to make the sizing handles appear.
Now add some visual impact. Either right click to access the fonts or use the toolbar above the design area. Make the font bold, and bump it up a few sizes, 16 generally works well.
Step 11. Add page numbers.
Drag another text box into the area. This time instead of static text we’ll use an expression to put in page numbers. Position the text box in the upper right corner of the report.
Right click on the text box, and in the pop up menu pick “Expression”.
In the expression builder you have a blank slate, only the beginning = is supplied for you. Similar to Excel, all expressions must start with an = sign.
The expression builder is very full featured and powerful, you can do a lot of complex things with it. It uses a VB.Net like language. In this lab though we’ll do something similar and concatenate some static text and build in variables to form a Page x of xx expression.
After the = sign enter “Page “ then an ampersand “&”. Page is simply static text, and the & will be used to join together our return value.
In the lower half of the Expression dialog you will see a Category and Item area, these are designed to make it easier to build expressions. Click on the “Built in Fields” Category. On the right the Item area will populate with the valid fields. Click on PageNumber.
Return to the upper area where it says “Set expression for Value” and after the page number type in & “ of “ & . Then go back to the Item list and click TotalPages. Your Expression dialog should now look like:
Click OK to close the Expression builder.
Step 12. Format the page number.
Select the text box for the page number by clicking on the edge, then using the toolbar right align the page number box. Page numbers are typically quite small on the header, so let’s bump down the font to 8 point.
Step 13. Resize the header.
In this example our header isn’t very large, but when we added it SSRS gave us a considerable amount of space. Let’s resize this to something more appropriate.
Hover over the dotted line between the header and report body with your mouse. It should turn into the up/down sizing handle. When it does, click and drag it up.
As an alternative, you could click in the empty area of the header, then in the Properties pane of VS/BIDS enter an explicit Height value. This is useful for situations where you have specific requirements that the header must be of an exact size. This often occurs with things like pre-printed forms or paper with the letterhead already printed on it.
Step 14. Preview the header.
OK, all done with this part. Switch to the Preview tab to see the header in action.
Step 15. Add the footer.
Working with footers are identical to working with headers. Start by right clicking in an empty spot in the design area and pick “Add Page Footer”.
Step 16. Add content.
Drag a text box onto the footer. Expand it to take up the entire width of the report, then enter the Expression dialog as you did before, right click and pick Expression from the menu.
It’s common for a business to want to copyright their intellectual property, so enter this as your expression:
="Copyright " & Year(Now()) & " ArcaneCode."
Hint: If you select Common Functions, Date & Time in the Category area of the Expression builder, you’ll see many common functions. When you click on one helpful hints will appear to the right.
Since we have a lot of unused space, we’ll again shrink the footer like we did the header. This time though hover over the bottom of the footer to make the resizing mouse icon appear, then drag it up to shrink it.
Step 17. Test in the Preview pane.
Once again, return to the Preview tab, scroll down and the footer should look something like:
Other ideas.
The things you can do in the header and footers are nearly infinite. Images, such as your corporate logo can be used. Trademarks, warning notices of intellectual property, print dates, the report name and URL, and the list of parameters used to generate the report are all common things that may appear in the header.
Arcane Code 