A Cross-Tab analyzes a relationship between two Data Items and creates a table which displays row and column headings based on the two Data Items selected. The body of the table is a matrix showing a calculated field based on a third Data Item which is specified in the Request. Cross Tabs are much easier to understand after you create one and see what it looks like rather than trying to read about it.
Lesson Goals: You are going to create a cross-tab report that shows average sale price of cars by make for each year between 1995 and 1999. Car makes are displayed vertically (down the rows) while year of manufacture is displayed horizontally (across the columns).
To create a Cross-Tab report:
- From the Request Manager, click New. The Select Type of New Request dialog box appears.
- Choose Cross-Tab and click OK. The Workbench window appears.
- Type xxxLESS5 (substituting your initials for xxx) in the Name box.
- Type "Average Sales Price of Makes from 1995 to 1999" for the Title.
- Click the Select Data Items tab.
- To build the Cross-tab Request, you will select an item for your Rows, then your Columns, then for each Data Cell.
- Drag Cars Make from the Choose From List to the Down the Rows box. The Rows on your Cross-Tab Request will reflect the make of the car.
- Drag Cars Year to the Across the Columns box. The Columns on your Cross-Tab Request will reflect the car year.
- Choose the calculation in Each Data Cell box.
- Choose Average in the Show the drop-down box and select Cars Sale price in the Choose Data Item list.
- Click the Filter Results tab.
- Click Add.
- Select Cars Year in the Data Item drop-down box.
- Select Between in the Relationship drop-down box.
- In the Values box, type the beginning and ending model years: 1994,1999. Be sure to put a comma between the beginning and ending model years.
- Save this filter condition by clicking on the a on the right side of the line.
- Click the Save icon in the Design Preview toolbar to save your Request.
- Click Close.
- Highlight the Request and click the Preview icon on the toolbar to preview the Request. As you can see, Makes are listed down the left in the rows. Years are listed across the top in the columns. And the body of the output shows the Average sales price of cars for each make for each year from 1995 through 1999.
- Click the X on the upper right corner of the Request Preview window to close the window.