Express Essentials: Using the Report Design Layout Tab

If you've been following Express Essentials, you know that I've been covering the fundamentals of using SQL Server 2005 Express Reporting Services. (For links to the earlier articles, go to www.sqlmag.com/articles/index.cfm?articleid=98868 and see the Related Articles box.) I started by building a simple report, then I refined it by doing some basic but important changes, such as adding reporting headers and footers and just a bit of formatting.

Then I used the Report Designer's Data tab to join two tables and selected just the columns that I wanted to include in the reports. Although it's possible--even easy--to select all of the columns from the tables involved and then just omit the ones you don't want on the layout page, that's definitely not a good design approach. When designing database applications (and reports qualify as "database applications"), you should always strive to retrieve only the data that you need and not a bit more.

Allthough Report Designer is pretty good at generating a layout based on the data you select, it doesn't automatically update that layout when you change the data. After making changes to the Data tab, you need to manually update the Layout tab by removing the columns that are no longer present in the data set that's being returned.

To update the report's layout, run Business Intelligence Development Studio and open your report project using either the Recent Projects list or the File, Open, Project/Solution option. After opening the project, click the Layout tab. The layout will initially look pretty messy, with a lot of columns and a small report body section. Give yourself some design room by closing the Output window at the bottom of the screen, then dragging the splitter bar (just above the Page Footer) down a couple of inches. That should make the design surface more readable.

Next, pare down the report columns to just SalesOrderID, OrderDate, DueDate, Status, Total Due, Customer ID, and CompanyName. Delete all unwanted columns by right-clicking the top of the column and selecting Delete Columns from the context menu. For this example, I deleted Revision, ShipDate, Online, SalesOrder, Purchase, Account, Ship To, Bill To, Ship, Credit Card, Sub Total, Tax Amount, Freight, Comment, rowguid, Modified. Obviously there were a lot of unneeded columns. Save the layout by clicking the diskette icon.

Now we can bring in the CompanyName column from the joined table. In the column header, right-click the Company ID column and select Insert Columns to the right. Type "Company Name" in the header, and in the Fields section enter

=Fields!CompanyName.Value

as the figure below shows. Save the report. In my next article, I'll explain how to finish the formatting and generate the report.