Reporting Services sets SQL Server Express apart from other "lite" database products. Reporting Services requires SQL Server 2005 Express Edition with Advanced Services and either Microsoft Visual Studio or the graphical Report Designer tool available in Business Intelligence Development Studio (BIDS). In "Designing Reports with Reporting Services," I explained how to make basic changes to report layouts by customizing the reporting header and footer. Now let's take that sample report and remove the unnecessary columns.
When I built the sample report, for simplicity's sake I selected just the SaleOrderHeader table from the AdventureWorksLT database. Selecting the entire table was a good way to get started. However, it returns much more data than I really want and flies in the face of the fundamental database application design principle that you should always retrieve only the data that you need. To pare down the data in the report, let's use the Start, All Programs, Microsoft SQL Server 2005, SQL Server Business Intelligence DevelopmentStudio option to start BIDS. Then open the saved report project, which was named Report Project1 by default.
With the report project open, click the Data tab. In the tables pane, select the check boxes for the SalesOrderID, OrderDate, DueDate, Status, CustomerID, and TotalDue columns. Limiting the report to just these six columns will make our report much more readable.
Next, because the customer ID won't be meaningful to most users, let's join to the Customer table and pull in the customer name as well. Right-click in the table pane and select Add Table from the context menu. In the Add Table dialog, select Customer and click Add; Report Designer will automatically join the tables on the CustomerID column. Select the CompanyName check box in the Customer table window to tell Report Designer to automatically rebuild the required SQL statement in the SQL pane. Then, to sort the report by customer, scroll through the Columns pane until you see CustomerID, as the figure below shows.
Use the Sort Order field's drop-down box to select the value 1, which tells Report Designer to do an ascending sort by Customer. Then save the updated project by clicking the Save icon.
In the April 21 SQL Server Express UPDATE, I'll show you how to update the layout to make use of the new data selection.