OVERVIEW
This tutorial shows how to create a report using the Crosstab tool. In order to fully understand the concept of crosstabs, we need to create physical printouts and look at them in different ways; therefore, you'll need access to a printer, some scissors, tape, and a stapler for this tutorial. By the end of this tutorial we will have created the following products:

‧ A poster-style crosstab using 'down then across' pagination
‧ A poster-style crosstab using 'across then down' pagination
‧ A document-style crosstab using the standard style
‧ A document-style crosstab using the repeated captions style

Task 1 Select Data
1 Create a new report.
2 Hide the Data Tree and the Report Tree if they are visible.
3 Access the Query Designer.
4 Select the Customer and Orders tables.
5 Click the Fields tab and select All Fields.
6 Scroll down to the Amount Paid field and select it, wait a second, then click again.
7 Change the name to Sale Amount.
8 Click the Calcs tab and double-click on the Company field.
9 Maximize the Query Designer, then choose Expression from the Function drop-down list and enter the following expression:
Extract (Month from SaleDate)

Note: This expression takes the month from the sale date, allowing us to compute totals on a monthly basis.
10 Change the Field Alias to SaleMonth.
11 Click OK.

Task 2 Create a Crosstab
1 Access the design workspace.
2 Select Report | Data. Select None for both Pipelines. Click OK.
3 Place a crosstab component in the detail band.
4 Use the Edit toolbar to assign the crosstab to the Customer pipeline
5 Select File | Page Setup. Access the Paper Size tab and set the orientation to Landscape. Click OK.
6 Right-click over the crosstab and select Configure. The Crosstab Designer will be displayed:

7 Read the instructions at the top of the Crosstab Designer.
8 Select the SaleMonth field (at the bottom of the list) and drag it over the new row cell. Look for the black, triangular indicators that show where the dimension will be created:

9 When the indicators appear to the left of the new row element, drop the field into the diagram. The diagram should look like this:

10 Click OK.
11 Press Ctrl + S and save the report under the name Crosstab.
12 Preview. A blank page is displayed because no values have been assigned to the crosstab.

Task 3 Design the Crosstab
1 Return to the design workspace, right-click over the crosstab, and select Configure.
2 Drag the Sale Amount field over the new value cell. When the indicators appear, drop the field into the diagram. The diagram should look like this:


Note: The number 1000 represents the format of the calculated value. The Grand Total indicates that the last row of the crosstab will show the total sale amount for all months.

3 Select SaleMonth and use the highlight color palette to set the color to green. Notice that the element turns fuchsia instead of green. This is because the cell is selected. Deselect it by clicking another element and it should turn green.
4 Select Grand Total and set the color to yellow.
5 Select Sum of Sale Amount and set the color to fuchsia.
6 Select the 1000 under Sum of Sale Amount and set the color to red.
7 Select the 1000 to the right of the grand total and set the color to gray.
8 Close the Crosstab Designer.
9 Preview. The colors allow you to see where each element prints. The values in red represent the sale amount per month. The value in gray is the grand total for the year. The numbers in green represent the months. The yellow and fuchsia sections show where the headings, or captions, print. Now let's add some more values. Right now we have the sum of sales. Next we'll add the average and number of sales per month (count) to the crosstab.


EXTEND THE CROSSTAB DESIGN

Task 1 Add Values to the Crosstab
1 Access the Crosstab Designer.
2 Select all colored sections and set the color to none.
3 Drag the Sale Amount field over the new value cell and release it.
4 Select the second Sum of Sale Amount (after the new value cell).
5 Locate the drop-down list box on the toolbar.
6 Select Average from the drop-down list.
7 Once again, drag the Sale Amount field over to the new value cell and release it.
8 Select the second Sum of Sale Amount (the one below the average).
9 Select Count from the drop-down list. The diagram should look like this:

10 Click OK.
11 Preview. The crosstab includes new values. Advance to the second page. The grand totals are on this page. The report tells us the sum, average, and count for the sale amount per month.

Task 2 Set the Format of the Values
1 Access the Crosstab Designer.
2 Select the 1000 under Sum of Sale Amount.
3 Right-click and select Display Format. Select the first menu option with a dollar sign.
4 Select the 1000 under Average Sale Amount.
5 Right-click and select Display Format. Select the first menu option with a dollar sign.

Task 3 Calculate Totals by State
1 Drag the SaleMonth cell over the new column cell and release.

2 Drag the State field over the new row cell and release. The diagram should look like this:

3 Click OK.
4 Preview. Select Whole Page on the preview toolbar. The crosstab is reformatted. Notice that the months go across the top of the report instead of down the side. This is because we made SaleMonth a column. The crosstab shows us the sum, average, and count for the sale amount per month for each state. Unfortunately, the preview screen doesn't give us a complete view of the entire crosstab. After we lay out the header band, we'll print out and assemble a complete view of the crosstab.

Task 4 Lay Out the Header Band
1 Return to the design workspace and place a label in the upper left corner of the header band.
2 Set the caption to Annual Sales.
3 Set the font to 12 bold.
4 Place a System Variable component in the lower left corner of the header band.
5 Set it to PrintDateTime.
6 Place another System Variable in the lower right corner of the header band.
7 Set it to PageSetDesc.
8 Align the tops of the system variables.
9 Select Ctrl + S to save the report.
10 Preview. Notice that the page number prints on each page.

UNDERSTANDING CROSSTAB LAYOUTS

Task 1 Control Pagination: Down then Across
1 From the Preview screen, print out all four pages of the crosstab.
2 Return to the design workspace and right click over the crosstab.
3 Select Pagination. Notice that the pagination defaults to Down then Across. This setting refers to the order in which the pages print.
4 Retrieve your printed pages and lay them out like this:

5 Cut off the 1/2 inch bottom margin of pages one and three. Cut off the 1/4 inch left margin of pages three and four.
6 Tape the pages together, using the crosstab grid to align them properly:

7 Label this crosstab 'Down then Across'.

Task 2 Control Pagination: Across then Down
1 Right-click over the crosstab component and select Pagination.
2 Click on Across then Down.
3 Preview.
4 Print all four pages of the crosstab.
5 Retrieve your printed pages and lay them out like this:

6 Cut off the 1/2 inch bottom margin of pages one and two. Cut off the 1/4 inch left margin of pages two and four.
7 Tape the pages together, using the crosstab grid to align them properly:

8 Label this crosstab 'Across then Down.'

Note: As you can see, pagination controls the order in which the pages print when the crosstab cannot fit on a single page.

Task 3 Use Repeated Captions
1 Click the Design tab.
2 Right-click over the crosstab component.
3 Expand the Style menu option. Notice that the default setting is Standard. This means that the captions do not repeat. Both of the crosstabs we put together are set to Standard because we did not want to see the captions on every page. However, if we print the crosstab as a document, we may want the captions to repeat for clarity.
4 Select Repeated Captions.
5 Preview and print all four pages of the crosstab.
6 Put them in order by page number and staple the pages together.
7 Flip through them. Notice that the captions for the state and the month appear on each page.
8 Right-click over the crosstab and set the Style to Standard.
9 Preview and print.
10 Staple the pages together.
11 Flip through them. Notice that the captions from page one do not repeat on page two. When we print a crosstab as a document, we can use repeated captions to keep track of values that appear on subsequent pages.
12 Close the Report Designer and save the changes. Congratulations, you've completed all of the tutorials.