The data cube behind a navigation table can be configured by controls in the following dialogs in the Display Options group: Change Data Cube, Show MDX Query, and Sort Options. These options are described in the following sections.

Columns, Rows, and Filters

Click to open the Change Data Cube dialog.

The Change Data Cube dialog configures the information that appears in the navigation table. In the table, measures are organized as the columns on the right. The measures are aggregations (such as Store Sales) and each row is a summary of the measures in a different dimension, such as Store Sales by Store Country and Store State.

The navigation table can also be filtered by dimensions that are not displayed, for analyses like “Show Store Sales for June by Product and Store.” In this case, the filter would be Time = June, the rows would be Product and Store, and the columns would be Store Sales.

After making changes with the Change Data Cube tools, you must click OK for the selections to take effect.

Columns and Rows

The Columns section of the Change Data Cube dialog configures the measures of crosstab navigation tables. Measure columns are normally aggregations, such as STORE SALES, while measure rows are granularities of such aggregations, such as STORE SALES by STORE COUNTRY, STORE STATE, and STORE CITY.

The Dimensions section of the dialog configures the table’s rows. Dimension rows are hierarchy levels representing the granularity of the hierarchies, such as Canada, Mexico, and USA for STORE COUNTRY, and CA, OR, and WA for STORE STATE. The row position of the dimensions can be changed by clicking the triangles, similar to how column positions can be changed.

Measures can be selected, deselected, moved to a different column position, or moved to rows (pivoted).

To configure a measure:

The Measures dialog appears.

To move a measure within its section of the dialog:

1.

Click

Other icons throughout the dialog change, as well, to signal that moving is enabled.

The Group button changes to a Flat button. Click Flat to return to the expanded list.

To move a dimension into the Columns or Rows section of the Change Data Cube dialog:

•

Click

•

Click

The root level of the STORE dimension contains a special member called ALL STORES. Clicking the check box next to the member selects all hierarchy levels of the STORE dimension. Clicking displays the members in the next level of the STORE hierarchy. Select a lower-level member to limit the view to that data.

Filter

Dimensions can be used to filter the data in a navigation table.

To use a dimension as a filter:

1.

In the Change Data Cube dialog, click

The dimension appears in the Filter section.

The root level of the dimension appears. In this example, we’re looking at the TIME dimension.

The Group button changes to a Flat button. Click Flat to expand the list of measures.

Show MDX Query

The MDX Query Editor contains the MDX query that retrieves the contents of the navigation table. As you change the content of the navigation table, the MDX query is automatically updated. You can also change the contents of the navigation table by changing the MDX in the editor. Click to open the MDX Query Editor.

An MDX query consists of data sets, query scope, and filter specifications:

Click Apply to update the navigation table in the OLAP view. The system validates the query and updates the navigation table. Click Revert to discard all changes.

For a reference to the MDX query language, see http://msdn2.microsoft.com/en-us/library/ms145506.aspx.

Sort Options

The sort options include the following:

To use the sort options, first click to open the Display Options dialog:

Then make your selections in the Sort Options section of the dialog and click OK.

The following sections explain the options.

Sort across cube hierarchy

Generally, dimension members are sorted hierarchically, within their parent member. Take, for instance, a crosstab showing the store sales for all stores in the United States. The dimension hierarchy might be as follows:

All Stores is above USA in the hierarchy, which is above CA in the hierarchy, and so on.

In the above list, the STORE COUNTRY (USA) and STORE STATE (CA, OR, WA) are hierarchy boundaries. STORE CITY items (such as Alameda and Portland) are hierarchy members.

You can use the Sort across cube hierarchy option to change the navigation table so the member measures are sorted without regard to the hierarchies that contain them. For instance, you may want to list all of your USA stores based on their sales, highest to lowest, regardless of the states they are in, as illustrated below.

To do this, set up your analysis view to show the dimensions you want to display in rows, and the measures you want to display in columns. Click the Sort icon next to the measure you want to sort on. Then open the Display Options dialog as described above, and click Sort across cube hierarchy.

Click to toggle between sort across and sort within hierarchy boundaries.

Start sorting in descending order

By default, data are sorted in ascending order. To sort in descending order, select the Start sorting in descending order check box.

Display only the first N rows

Display only the first N rows limits the display to either the top-most or bottom-most n rows, depending on the sort order. You can specify an integer for n, which defaults to 10. Limiting the display to a certain number of rows can help with performance or narrow the view to highlight records at the extremes of your dataset; for example, it can be used to highlight the top 10 sellers or the bottom 10 prices. Clear the check box to disable the row limitation.