Reporting Services is a powerful and totally free component of SQL Server 2005 Express that lets you easily create reports based on the data in SQL Server Express databases. In "Using Reporting Services," when I showed you how to build a basic report using the Report Server Project Wizard template that's supplied in Business Intelligence Development Studio (BIDS), we were left with a very basic report that lists the contents of the SalesOrderHeader file from the sample AdventureWorksLT database.
To customize the report, you need to open BIDS using the Start, All Programs, Microsoft SQL Server 2005, SQL Server Business Intelligence Development Studio menu option. If you followed the steps in "Using Reporting Services," you'll see a project named Report Project1 in your Recent Projects list. Double-click it to open the project in BIDS Report Designer. If you don't see the project, use the Open Project link to browse to your saved project.
When you open the project, the Report1.rdl file is displayed in Report Designer. To create Reporting Services reports, you use Report Design Language (RDL), an XML-based format that Microsoft uses to store Reporting Services reports. If you look in the BIDS Solution Explorer, you'll see an empty Shared Data Sources node and a Reports node, which contains the Report1.rdl file. All the RDL specifications in Report1.rdl thus far were generated by the Report Server Project Wizard.
Double-clicking Report1.rdl opens it in BIDS Report Designer. Report Designer displays three tabs in the upper-right portion of the window: Data, Layout, and Preview. The Data tab lets you edit the underlying T-SQL query that retrieves the data for the report, and the Preview tab renders the report in the Report Designer window so you can see what the report looks like and print it. The Layout tab is the primary tool for changing the report's formatting.
First, let's change the generic header. Click Report1 to open the text box that contains the report header. Replace ”Report1" with the words "Sales Order Report" and click the "Center text" icon in the Report Formatting toolbar to center the title on the report.
We can add a page number and date to the report by right-clicking the Layout pane and selecting Page Footer from the context menu. We'll add the date to the bottom right corner of the report by opening Toolbox, clicking the Textbox icon, and dragging it to the lower left-hand corner of the Page Footer area. Size the text box by clicking it and using the resizing handles. Add the date and time by typing
=Now()
in the text box. Add the page number to the bottom left-hand corner of the report by using the slider bar to position the report to the rightmost slide of the Layout window. Then open the Toolbox, click the text box, and drag it to the lower-right portion of the Page footer area. Inside the text box, type
="Page: " & globals.PageNumber
Click the Preview tab to have Report Designer render your updated report. In the next issue of SQL Server Express UPDATE, I'll show you how to make your reports prettier and how to add report parameters.
