Export (0) Print
Expand All

Walkthrough - Adding Grouping, Sorting, and Formatting to a Basic Report

SQL Server 2000

This tutorial is designed to help you explore additional features in report design by building upon the basic table report created in the previous tutorial. In this tutorial, you will open the report project; add grouping and sorting to the table, add a new column to the table, add a subtotal, provide some formatting, preview the report, and publish the report to a report server.

Requirements

Your system must have the following installed to use this tutorial:

  • Microsoft SQL Server 2000 Reporting Services.
  • SQL Server 2000 with the AdventureWorks2000 OLTP database.
  • Microsoft Internet Explorer 6.0 or later.
  • Microsoft Visual Studio® .NET 2003 with Report Designer.

You must also have permissions to retrieve data from the AdventureWorks2000 database and to publish reports to the report server.

This tutorial requires that you have completed the previous tutorial, "Walkthrough - Creating a Basic Report."

Open the Tutorial Project

This tutorial builds on the Sales Order report that was created in the previous tutorial. If you already have this report open, skip these steps.

  1. Click Start, point to Programs, point to Microsoft Visual Studio .NET 2003, and then click Microsoft Visual Studio .NET 2003.
  2. On the File menu, point to Open, and then click Project.
  3. Navigate to the location of the project file that was created in the previous tutorial. The file should be located in the Tutorial folder.
  4. Click Tutorial.rptproj and then click Open.
  5. In the Solution Explorer window, double-click on Sales Orders.rdl to open the report. If necessary, click the Layout tab to open the report in Layout view.

Add a Group

You can add a group to the table that groups and sorts data by sales person.

  1. Click the table so that column and row handles appear above and next to the table.

    Note  Handles are gray boxes that appear above and next to the table when it is selected. You use handles to perform various actions on columns, rows, and the table itself. The handles that run across the top of the table are column handles. The handles that run down the side of the table are row handles. The handle where the column and row handles meet is the corner handle.

  2. Right-click on the handle of any row and then click Insert Group.
  3. On the General tab, for Group on, select =Fields!LastName.Value in the first row and =Fields!FirstName.Value in the second row. This will group the data by sales person name.
  4. On the Sorting tab, for Sort on, select =Fields!LastName.Value in the first row and =Fields!FirstName.Value in the second row, each with a direction of Ascending. This will sort the data by sales person name.
  5. Click OK. Two new rows, a group header and a group footer, are added to the table.

Add a New Column

You can add a column to the table to display the name of the sales person.

  1. Click the table so that column and row handles appear above and next to the table.
  2. Right-click on the handle of the first column (Order Date) and then click Insert Column to the Left.
  3. Click on the second cell in the new column and type the following expression. The second cell should be in the new group row that was added in previous steps.
    =Fields!FirstName.Value & " " & Fields!LastName.Value
    
  4. Click on the first cell in the first column and type Sales Person. This is the label for the column in the table header.

Sort the Detail Data

Add sorting to the detail data in the table to sort by order date.

  1. Click the table so that column and row handles appear above and next to the table.
  2. Right-click the corner handle and then click Properties.

    Note  The corner handle is the handle where the column and row handles meet.

  3. On the Sorting tab, for Sort on, select =Fields!OrderDate.Value.
  4. Click OK.

Add a Subtotal

You can add aggregate functions to the report. These steps add a subtotal by sales person.

  • Drag the OrderDate field from the Fields window to the fourth cell in the last (Total Due) column.

    Note  If the Fields window is not visible, in the View menu, click Fields.

    Note  Because this is a group row, the Sum function is automatically added to the expression to create a subtotal.

Apply Formatting and Style

There are additional tasks that you can perform to clean up the report and make it easier to read.

Date Format

The OrderDate field displays date and time information by default. You can add formatting to display only the date.

  1. Right-click the cell with the OrderDate field expression and then click Properties.
  2. For Format, select Standard, select Date, and then select the third example on the list (short date).
  3. Click OK.
Currency Format

The TotalDue field displays a general number. Add formatting to display the number in currency format.

  1. Right-click the cell with the TotalDue field expression and then click Properties.
  2. For Format, select Standard, and then select Currency.
  3. Click OK.
  4. Right-click the cell with the subtotal for total due and then click Properties.
  5. For Format, select Standard, and then select Currency.
  6. Click OK.
Text Style and Column Widths

You can also add style to the table headers to differentiate them from the rows of data in the report, and adjust the widths of the columns.

  1. Click the table so that column and row handles appear above and next to the table.
  2. Select the row handles of the first, second, and fourth rows (the row containing column header labels, the group header row, and the group footer row) and then click the Bold (B) button on the formatting toolbar.

    Note  To select multiple items, hold down the CTRL key and click on each item.

  3. Point to the line between column handles so that the cursor changes into a double arrow. Drag the columns to size.

Preview the Report

Use Preview to examine the report before publishing it to the report server.

  • Click the Preview tab.

Publish the Report

When the report is complete, you can publish it to a report server. Before you publish the report, you must switch to the Production configuration and set the location of the report server.

  1. In the Solution Explorer window, right-click the Tutorial project, and then click Properties.
  2. Click Configuration Manager.
  3. In the Configuration Manager dialog, in Active Solution Configuration, select Production.
  4. Click Close.
  5. In the Tutorial Property Pages dialog, in TargetServerURL, type the report server virtual directory; for example, http://servername/reportserver. (This is the virtual directory of the report server, not Report Manager.)

    Note  If the report server is on the same computer as Report Designer, you can use localhost as the server name, for example, http://localhost/reportserver.

  6. Click OK.
  7. Save the report project. On the File menu, click Save All.
  8. Publish the report. On the Debug menu, click Start.
  9. When publishing is complete, Report Designer opens Internet Explorer. Click Sales Orders to view the report.
See Also

Reporting Services Samples and Walkthroughs

Walkthrough - Creating a Basic Report

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft