Organize data in a crosstab

A crosstab displays data in a row-and-column matrix that has a spreadsheet-like appearance. The crosstab is used to summarize data in a compact and concise format, and display summary, or aggregate, values such as sums, counts, or averages. A crosstab groups these values by one set of data listed down the left of the matrix and another set of data listed across the top of the matrix.

A crosstab typically uses data from at least three fields, as follows:

  • One field populates the column headings in the crosstab. There is one column for each unique value in the field.
  • One field populates the row headings in the crosstab. There is one row for each unique value in the field.
  • One field populates the summary field of the crosstab. Report Studio aggregates the values in one field and displays these values in the crosstab cells.

ClosedExample

You can use Report Studio to insert a crosstab in a report design, select data for the crosstab, and specify the aggregate data to display. You can also define a bookmark for a crosstab, and then create a hyperlink from another report that links to the bookmarked crosstab element.

  1. From the Main Menu, select Administration > Application Setup.
  2. From the Setup page, select Common Setup > Unpublished Reports. The Report Management - Unpublished Reports page opens.
  3. Click Tap Create . Report Studio opens in a new tab with the report template displayed.
  4. If no report data objects are listed:
    1. Click Tap the layout pane (the block labeled Select and drag item to insert the available data). The Select Data glance (also known as a contextual callout) Provides information and actions in a dialog box for an item on the screen when the user right-clicks or taps the item. opens. Note that Data Objects is the only option available.
    2. In the Select Data glance, select the applicable report data objects from the Available Data box and move them to the Selected Data box. When finished, click tap OK. The selected data object are now listed in the Data column on the left side of the screen.
    3. If you need to change the listed data objects, click tap Manage Data manage data icon.
    4. To change the order of the data objects click tap the Data Set Order arrow or select Data > Manage Data from the tool bar.
    5. To review the possible columns, expand the applicable report data object and then expand the corresponding BaseDataSet.

When you lay out data in a crosstab, provide the following information in Crosstab Builder:

  • The data fields to display as rows
  • The data fields to display as columns
  • The summary fields to display in the crosstab

You move dimensions and measures from Available Data to Row, Column, and Summary fields.

To open Crosstab Builder, select Insert > Table > Crosstab. Crosstab Builder contains three tabs along the top: Data, Filter, and Format.

The Data tab is open by default when you open Crosstab Builder.

  1. Expand the Use Data drop-down list and select the desired report data object. You can only select one report data object.
  2. In the Available Data box, expand the BaseDataSet and do the following:
    1. Select one or more fields for the rows. Click the right arrow next to the Row box.
    2. Select one or more fields for the columns. Click the right arrow next to the Column box.
    3. Select one or more summary fields. If the data model includes aggregated measures, they appear in the Aggregated Measures folder. You cannot modify the summary function for an aggregated measure. Click the right arrow next to the Summary Fields box, then click OK.

To specify a filter condition, click the Filter tab and do the following:

  1. Specify one or more filter conditions
  2. You can also select an operator in Condition to set a filter condition for more than one value

To display aggregate values, click the Format tab.

You can display totals for each dimension that you add to a crosstab and for each level within a multilevel dimension. The rows and columns that display the subtotals and grand totals are highlighted in the crosstab. In the example crosstab you just created, no subtotals appear. Subtotals are displayed when you set a multidimensional data field as a row or column.

Each number displayed in a crosstab represents an aggregate total. Grand totals display the total sales of all products for each state, the total sales of each product, or the total of all sales across products and states. Subtotals display the sales of each product in each state, and so on. You specify the aggregate totals that appear in a crosstab.

  1. In the Grand Totals area, do the following:
    • Select Show Grand Totals for Rows to display grand totals for each row.
    • Select Show Grand Totals for Columns to display grand totals for each column.
  2. You can display subtotals for multilevel dimensions used in rows or columns if the Sub Totals area is highlighted.
    • Select Show Sub Totals for Rows.
    • Select Show Sub Totals for Columns.
  3. In the Page Break area, select Enable Page Break to specify pagination properties for the crosstab, and do the following:
    • In the Row Interval field, enter a value, such as 40.
    • In the Column Interval field, enter a value, such as 10, then click OK. Sample data appears in the crosstab, displaying grand totals for rows and columns.

  1. In Report Studio, click Save and Viewsave and view icon.
  2. Specify a file name, a folder location in which to store the saved file, a description, and a filet type (*.rptdesign), the click OK. The crosstab appears in the viewer.

To modify a crosstab’s properties, select and right-click the crosstab and then select Properties. Properties appears to the right of the crosstab

To delete a crosstab, select the crosstab in the report design and click Deletedelete iconfrom the toolbar.

Related topics

Operators in conditional formatting and filter condition expressions

Operators in relative time period conditions

Functions used in aggregate calculations

Operators used in computed column expressions

Functions used in computed column expressions