Using Tables to Display Query Results :: SAS(R) Web Report Studio 4.2: User's Guide

Overview of the Table Types

About List Tables

A list table is a two-dimensional representation of data, in which the data values are arranged in unlabeled rows and labeled columns. List tables are applicable when you use data items selected from a relational data source.

A List Table

For more information, see Using Tables to Display Query Results.

About Crosstabulation Tables

A crosstabulation table shows frequency distributions or other aggregate statistics for the intersections of two or more categories. In a crosstabulation table, categories are displayed on both the columns and the rows, and each cell value represents the data result from the intersection of the categories on the specific row and column.

A Crosstabulation Table That Is Based on Data Items From a Relational Data Source

In This Crosstabulation Table That Is Based on Data Items From a Multidimensional Data Source, Drilling and Expanding Are Enabled

A Crosstabulation Table That Is Based on Data Items from a Multidimensional Data Source, Drilling and Expanding Are Enabled

Tip

For crosstabulation tables that are based on multidimensional data sources, the hierarchy level names are displayed in the table, rather than the hierarchy names. In In This Crosstabulation Table That Is Based on Data Items From a Multidimensional Data Source, Drilling and Expanding Are Enabled, Year is a level in a hierarchy named Time, and Continent is a level in a hierarchy named Geography.

Insert a Table into a Report Layout

If the section query is based on a relational data source, you can display the query results in a list table or a crosstabulation table. If the section query is based on a multidimensional data source, you can display results in a crosstabulation table.

To insert a table into a report layout, perform one of these tasks:

Click the table tool on the layout grid toolbar to insert the table in the next available cell.
Drag the table tool from the toolbar into a specific cell.

The following table lists the available table tools:

Tool	Table Type
	list table
	crosstabulation table

Data items in the section query are given the following default assignments in a new table:

For list tables, all data items are assigned to the columns.
For crosstabulation tables, data items are assigned to functions in this way:
- If multiple categories or hierarchies have been selected from the data source, then the first category or hierarchy and all of the measures are assigned to the columns.
- If only one category or hierarchy is selected from the data source, then the category or hierarchy is assigned to the rows and the measures are assigned to the columns.

You can change the default assignments. For example, you might want to make these changes:

A list table is displaying values for an Employee Salary measure. You want to hide the salary information.
A crosstabulation table has gender information on the rows. You want to move the Gender category from the rows to the columns.

For more information, see Specifying How Data Items Are Used in Tables.

Tables also have default properties that you can change. For example, by default, tables do not have titles. To create a title, you open the Properties dialog box and enter text in the Title field on the General tab. For more information, see Specify Style Properties for Total and Subtotal Values.

Tip

In general, the defaults for properties that are related to style (for example, font and color) depend on the currently applied report style. For more information, see Modifying Report Properties.

For more information about how to make other table modifications, see the following topics:

The following chapters contain additional information:

Specifying How Data Items Are Used in Tables

Assign Data Items to Functions in List Tables

To assign data items to functions in a list table, complete these steps:

Select DataAssign Data or right-click the list table and select Assign Data to open the Assign Data dialog box.
Perform one of these tasks to assign data items:
- Use drag and drop features to assign data items to either Columns or to Hidden.
- Use the Move Items drop-down list to assign each data item either to Columns or to Hidden.
Data items that are assigned to Hidden do not appear in the table but can be used in filtering. For more information, see Hiding Data Items.

Assign Data Dialog Box with the Move Items Drop-Down List Selected
Click OK.

Assign Data Items to Functions in Crosstabulation Tables

To assign data items to functions in a crosstabulation table, complete these steps:

Right-click the list table, and then select Assign Data to open the Assign Data dialog box.

Note: For crosstabulation tables that use relational data, some data items are not supported and do not appear in the Assign Data dialog box. The data items that do not appear include measures that use the distinct aggregation type.
Perform one of these tasks to assign each data item:
- Use drag and drop features to assign data items to Rows, Columns, or Hidden.
- Use the Move Items drop-down list to assign each data item to Rows, Columns, or Hidden.
Data items that are assigned to Hidden do not appear in the table but can be used in filtering. For more information, see Hiding Data Items.
Click OK.

Create or Modify a Table Title

By default, new tables do not have titles. To create a new title or modify an existing title, complete these steps:

Right-click the table, and then select Properties to open the Properties dialog box.

The General tab is displayed by default.
Under Title, complete these steps:
1. Type or modify the content in the Text field.
  
  You cannot use these characters: < > & #.
2. Specify the font, font size, font style, font color, and alignment.
  
  A Table Title Specified in the Properties Dialog Box
Click OK.

In View mode, the formatted title appears above the table. In Edit mode, the title appears in the table object.

A Crosstabulation Table with a Title

Specify the Number of Columns and Rows to Display in a Table

To specify how many columns and rows the table should display, complete these steps:

Right-click the table, and then select Properties to open the Properties dialog box.

The General tab is displayed by default.
Under Table size, complete these steps:
1. Select one of these options for displaying columns in the table:
  
  Show all columns (up to system limit)
  
  Select this option to display all columns in the table, up to the system limit (which is managed by your system administrator). If necessary, the table will scroll to the right. This option is the default.
  
  Limit the number of columns displayed at once
  
  Select this option and type a value in the box to specify the number of columns that you want to view before scrolling is enabled.
  
  Note: If you type a value that is the same as the current system limit, then this option is reset to Show all columns (up to system limit).
  
  Tip
  In View mode, you can use your mouse to manually resize table columns.
2. Select one of these options for displaying rows in the table:
  
  Show all rows (up to system limit)
  
  Select this option to display all rows in the table, up to the system limit (which is managed by your system administrator). If necessary, the table will scroll down.
  
  Limit the number of rows displayed at once
  
  Select this option and type a value in the box to specify the number of rows that you want to view before scrolling is enabled. This option is the default.
  
  Note: If you type a value that is the same as the current system limit, then this option is reset to Show all rows (up to system limit).
  
  New Table Sizes Specified in the Properties Dialog Box
Click OK.

Specify the Border Color of a Table

To specify the color that is used for the border of a table, complete these steps:

Right-click the table, and then select Properties to open the Properties dialog box.

The General tab is displayed by default.
From the Border color palette, select a color for the table border.

New Border Color Specified in the Properties Dialog Box
Click OK.

In New Border Color Specified in the Properties Dialog Box, red was selected as the new border color. In View mode, all of the borders for this table change to red.

A Crosstabulation Table with a Title and Red Borders

Specify Style Properties for Headings, Subheadings, and Cells

All tables have headings and cells. Crosstabulation tables also have subheadings.

Headings are the category, hierarchy level, and measure labels. Two examples are State and Retail Price.
Subheadings are the values of the categories, hierarchy levels, and measures. For example, Alabama and North Carolina might be subheadings for a State heading.
Table cells contain data values. For example, $228.88 and $46.43 might be values for a Retail Price measure.

To specify style properties such as font, alignment, and color for headings, subheadings, and cells, complete these steps:

Right-click the list table, and then select Properties to open the Properties dialog box.

The General tab is displayed by default.
Select the Text tab.
Under Headings, Subheadings (crosstabulation tables only), and Cells, specify a font, font size, font style, and font color. For headings and subheadings, specify an alignment. To select a background color, use the Background fill color palette.

New Headings and Subheading Colors Specified in the Properties Dialog Box
Click OK.

In New Headings and Subheading Colors Specified in the Properties Dialog Box, navy blue was selected as the new heading color and green was selected as the new subheading color. In View mode, the headings and subheadings change to the new colors.

A Crosstabulation Table with a Title and Colored Headings

Specify Style Properties for Total and Subtotal Values

To specify properties such as fonts and colors for total and subtotal values, complete these steps:

Right-click the table, and then select Properties to open the Properties dialog box.

The General tab is displayed by default.
Select the Totals tab.
Under Totals and Subtotals (crosstabulation tables only), specify a font, font size, font style, and font color. To select a background color, use the Background fill drop-down list.

New Color for Totals Specified in the Properties Dialog Box
Click OK.

Note: You can choose to show or hide totals in a table. For more information, see Show or Hide Totals and Subtotals for Crosstabulation Tables.

A Crosstabulation Table with Totals in Color

Copy the Formatting of a Selected Table

To copy the formatting of a selected table to other tables in the report section, complete these steps:

Right-click the table, and then select Properties to open the Properties dialog box.

The General tab is displayed by default.

If the report section has more than one table, the Apply formatting to existing tables in the section check box is available to select no matter which tab is displayed.
Select the Apply formatting to existing tables in the section check box.

The title, heading, subheading, cell, total, subtotal, and border styles are copied to all of the tables in the section. Only the relevant information is copied. For example, the subheading style does not apply to list tables.
Click OK.

Change the Currently Selected Table Type

To change the currently selected table type, complete these steps:

Right-click the table, and then select Properties to open the Properties dialog box.

The General tab is displayed by default.

The Table type drop-down list is available to use no matter which tab is displayed.
For the Table type radio buttons, select either List or Crosstab, depending on the current table type. However, you cannot change the table type if the current table meets either of these conditions:
- a crosstabulation table that uses multidimensional data cannot be changed to a list table.
- a list table that does not include at least one category and one measure cannot be changed to a crosstabulation table.
Click OK.

The data item assignments and properties are changed to match the new table type. For example, not all data items are supported in crosstabulation tables that are based on relational data. So, if you change a list table to a crosstabulation table, then any measures that use the distinct aggregation type are removed. That is, they do not appear in the Assign Data dialog box.

Move Columns and Rows in Tables in the View Mode

The following tasks can be performed in View mode:

Task	Action
Move measures from rows to columns or from columns to rows	Use drag and drop features to move rows or columns. Right-click on a measure heading, and then select Move Measures to Rows or Move Measures to Columns.
Rotate a table so that the columns are moved to the rows and the rows are moved to the columns	Right-click on a row or column heading, and then select Rotate Table.
Move a category or hierarchy level from a column to a row or from a row to a column	Use drag and drop features to move a column to a row or a row to a column. Right-click on a category or hierarchy level heading, and then select Move <column name> to Rows or Move <row name> to Columns.
Move a column to the left or right	Use drag and drop features to move columns. Right-click on a column heading, and then select Move <column name> Left or Move <column name> Right. (Sometimes a move to the left or right is not valid, depending on the current location of the column.)
Move a row up or down (in crosstabulation tables that have more than one row)	Use drag and drop features to move a row. Right-click on a row heading, and then select Move <row name> Up or Move <row name> Down. Note that the top row cannot be moved up and the bottom row cannot be moved down.

Example Menu Selection for Moving Rows to Columns

Resize Columns in a Table

You can use drag and drop features to make the columns in a table larger or smaller.