Creating a Crosstab

Crosstab mode allows you to create a crosstab report out of your base database tables using the Report Designer. To display the Crosstab tab in the Report Designer, select one of the following options:

•   Select the menu option ModeĆCrosstab.

•   Select the Crosstab icon from the menu bar.

The Crosstab tab is shown below.

 

   Figure 147: Crosstab Tab

On the Report Designer window, the layout includes the Table, Column, Joins, Conditions, and Procedures tabs, as described in Chapter 4: Getting Started with Report Designer on page 119. Any data you entered in these pages is kept when you select the Crosstab tab. You may edit this information at any time by returning to those tabs.

Choosing the Crosstab Report Type

This section describes the crosstab types available from the Crosstab Style tab in the Properties window. Before you start to configure your crosstab using this tab you must plan the layout.

Click the Properties button on the Crosstab tab to display the Properties window. The Properties window with the Crosstab Style tab selected is shown below:

 

   Figure 148: Crosstab Properties Window Crosstab Style Tab

You can choose between Default, Drill Down, Master Detail I, Master Detail II, and N-Tier types. For descriptions of these report types, see Choosing the Tabular Report Type on page 171.

If you get an error when drilling down in crosstab reports, your administrator may not have enabled crosstab drill-down. Ask your administrator to check the setting of the SET_DRILL_PATH configuration variable.

Adding an Axis

To add an axis to a crosstab, select a column in the Available Columns: list and click the Add button next to the horizontal or vertical axis lists. The column is added to that axis.

Choosing Levels Along an Axis

The first column on which you create an axis is the first level. Subsequent columns appear beneath the previous ones, and form consecutive levels below the first level. You can change the order of the levels by rearranging the order of the columns. Choose a column name and click the up and down arrow icons next to the axis name.

Creating a Break Level

You can create a break level in the Vertical Axis: list by highlighting a level name and clicking the Break button.

Editing a Level

By default, the name of the database column is the header for the level in the crosstab report. You can edit the name by highlighting a level name and clicking the Edit button associated with the list. You can type a new name in the Column Heading field.

Adding a Measure

To add a measure, highlight a column name in the Available Columns: list. Make sure that the correct function is chosen from the Measure Function: drop-down list and click the Add Measure> button.

Sorting Dimension and Measure Columns

By default, the axis dimension and measure columns are sorted in the database order. To sort data displayed in any axis or measure column, double-click the column name. The Column Headings window is displayed, as shown below.

 

   Figure 149: Column Headings Window

The Column Headings window is used to select the way the column is sorted. You can also choose to sort in Ascending and Descending order.

 

   Note:


    When sorting by a date column, you must click the sorting option that matches the way the display of the column is set up in the database. If you want to know the exact way a column of data is displayed, preview (without sorting) to see the values displayed.


Adding a Formula

You can create custom formulas out of measures. For example, a sales manager could determine the average compensation of employees using the following formula:

AVG(EMP.SAL) + AVG(EMP.COMM)

To create a formula, click the Add Formula button. The Crosstab Formula Properties window is displayed, as shown below.

 

   Figure 150: Crosstab Formula Properties Window

To create a formula:

1.   Double-click a measure in the Available Measures: list to add it to the Formula field.

2.   Click one of the arithmetic operators (+, -, *, or /) or one of the parentheses to add it to the formula.

3.   Continue adding measures and arithmetic operators until the formula is complete.

4.   Enter a heading in the Formula Heading field. This is used as the column or row heading on the crosstab.

5.   Select a format (whole number, decimal, dollar value) from the Numeric Format: drop-down list.

6.   If you would like the formula’s values to be summed in the crosstab horizontally and/or vertically, select the appropriate checkboxes in the Summary Options section.

7.   When the formula is correct, click OK. The formula is added to the Measures list on the Crosstab page.

To edit a formula appearing on the Crosstab page, double-click the formula. The Crosstab Formula Properties window is displayed and you can edit the formula as described above.

Adding a Crosstab Title

To add a title to your crosstab report, type it in the Crosstab Title field at the bottom of the Crosstab page.

If you have a parameterized crosstab, you can make the parameter value appear in the crosstab title. Type the name of the parameter exactly as you entered it on the Conditions tab with an ampersand (&) before it. When you run the crosstab report, the parameter value that you use when you run the crosstab appears in the place of the parameter name in the crosstab title.

You can add one or more parameters in the title, including those that take multiple values.

Avoiding Illogical Groupings of Columns

The crosstab report puts all columns of level n under each column of level n-1, whether all columns of level n logically belong under a particular column heading of level n-1 or not. For example, if you use the following levels on the horizontal axis: region (level 1), and state (level 2), then all the states recorded within your database tables appear under each region of the country. For example, Georgia appears under the East, Central, and West region column headings in addition to the South region. The only difference is that under the East, Central, and West regions, all the cells within the Georgia column have the value zero for the measure chosen. In order to avoid such illogical groupings in your crosstab report, make the levels appear on the vertical axis and make sure that you have not selected the property Show rows having only zero values. Georgia now disappears from all region columns except the South region, as the row headed Georgia has only zero values under all regions except South region.

Setting Crosstab Properties

Click the Properties button on the Crosstab tab to open the Crosstab Properties window. The selection of the crosstab format from the Crosstab Style tab is described in Choosing the Crosstab Report Type on page 225. For descriptions of all other tabs in the Crosstab Properties window, see Crosstab Properties on page 273.

Creating a Custom Call

The Custom Call feature allows external actions to be triggered upon report creation. Click the Custom Call button on the Chart tab to display the Custom Call window.

For a description of the Custom Call feature, see Creating a Custom Call on page 242.

Previewing a Crosstab

You can preview your crosstab by clicking the Preview Crosstab button. If your crosstab report does not include any parameters, a web browser window opens with a crosstab report in it. If you have been looking at a preview of another crosstab report or another version of this crosstab report, the window with the report in it may not open in the foreground. You may need to select it.

An example crosstab report, using the Default format, is shown below.

 

   Figure 151: Example Crosstab Report

Previewing Crosstabs with Parameters

If your crosstab report includes parameters, the Enter Parameter Values window appears. You can either click the Load all values from the database button to use all values available for the parameter, or you can click the Reset button to restore the predefined list of parameter values. You can also type additional values in this window. Make sure that you highlight all the values that you want the report designer to use when running your crosstab.

Note on Exporting Crosstab Reports

If you export a crosstab report to the HTML+CSV format, the CSV output appears as an unformatted table. To view the output in a spreadsheet, export it as just CSV.