Creating Pivot Tables

Measures

To display a list of measures, select Dimensions from the drop-down list in the Model Contents area. Then expand the Measures folder. This folder lists all measures in the subject area. For example:

You can have two types of measures, indicated by icons as follows:

	Standard measures
	Calculated measures, which are defined in terms of other measures

Dimensions

To display a list of dimensions, select Dimensions from the drop-down list in the Model Contents area. Then expand the Dimensions folder. This folder lists the dimensions and the levels, members, and properties that they contain; it might also list related subject areas, with their dimensions, levels, and so on.

Click the triangle next to any dimension name to expand it. A dimension contains at least one level and may also include a special member known as the All member. In the following example, the AgeD dimension includes an All member named All Patients, as well as the levels Age Group, Age Bucket, and Age.

Hierarchies

Each dimension also includes at least one hierarchy, which is the actual container for levels. The following shows an example (a modified version of the Patients subject area), with a hierarchy named H1.

`

By default, hierarchy names are not shown unless a dimension contains multiple hierarchies. Alternatively, a dimension can be defined so that its hierarchy names are always or never shown.

In the Patients subject area, each dimension contains only one hierarchy, so the hierarchy names are not shown. Hierarchies are purely informational; you cannot drag and drop them as you can with the other items in the Model Contents pane.

Level Members and Properties

If you expand a level, the list displays the members of that level. For example:

If a level also includes properties, this area shows the property names with a different icon. For example, the City level includes the Population and Principal Export properties:

Variations for Data Levels with Many Members

If a data level contains more than 500 members but fewer than 10,000 members, this area displays any property names, followed by subfolders that contain the members in groups of 100. For example:

In this case, the system does not access the member names until you expand one of these subfolders. Thus it is possible to see and use the properties without waiting for the system to access all the member names.

If a level contains 10,000 members or more, this area displays only the property names, if any, of the level.

The preceding discussion applies only to data levels. In contrast, time levels are always displayed in the same way.

Related Subject Areas

The Dimensions folder might also list related subject areas, which it shows in bold italics. If you expand a related subject area, you can access the dimensions, levels, and possibly the hierarchies of the other subject area, as follows:

Pivot Variables

To display a list of pivot variables, select Dimensions from the drop-down list in the Model Contents area. Then expand the Pivot Variables folder. This folder (if included) lists any pivot variables in the subject area. For example:

Unlike most other elements in the Dimensions section, pivot variables are defined in the Analyzer; see Defining and Using Pivot Variables.

Plug-ins

To display a list of plug-ins, select Dimensions from the drop-down list in the Model Contents area. Then expand the Plug-ins folder. This folder (if included) lists any plugins in the subject area. For example:

A plug-in contains one or more properties, each of which computes a value. This particular plug-in contains two properties. Plug-in properties can be used in the same way as measures.

Named Sets

To display a list of named sets, select Dimensions from the drop-down list in the Model Contents area. Then expand the Named Sets folder. This folder (if included) lists any named sets in the subject area. For example:

A set contains one or more items; typically each item is a member of a level defined elsewhere.

Named Filters

To display a list of named filters, select Dimensions from the drop-down list in the Model Contents area. Then expand the Named Filters folder. This folder (if included) lists any named filters in the subject area. For example:

Unlike most other elements in the Dimensions section, named filters are defined in the Analyzer; see Defining Named Filters.

Calculated Members

At the bottom of the Dimensions section, the Model Contents pane shows any special dimensions that contain calculated members. These special dimensions include only members, not hierarchies or levels. For example:

Calculated members are typically combinations of other members. For example, the Primary Colors member is a combination of the Red, Yellow, and Blue members of the Favorite Color level.

Other dimensions in your model might also include calculated members. If so, the member is displayed in that dimension with a different icon than other members. For example, if we redefined the two calculated members to move them to the ColorD dimension, we would see the following for that dimension:

Unlike most other elements in the Dimensions section, calculated members can be defined in the Analyzer; see Defining Calculated Elements.

Pivot Tables

To display a list of pivot tables, select Pivots from the drop-down list in the Model Contents area. Then expand the Pivots folder. This folder (if included) lists any pivot tables defined in the subject area. For example:

To use this area, expand folders and needed and click a pivot table name. The Pivot Preview area then displays that pivot table.

Quality Measures

To display a list of quality measures, select Quality Measures from the drop-down list in the Model Contents area. Then expand the Quality Measures folder. This folder (if included) lists any quality measures in the subject area. For example:

Quality measures can be used in the same way as measures.

Detail Listings

To display a list of detail listings, select Detail Listings from the drop-down list in the Model Contents area. This area then lists any detail listings defined in the subject area. For example:

For information on using this area, see Displaying a Detail Listing.

Items That Can Be Used in the Rows Box

You can use any combination of the following items in the Rows box:

• Levels. This is the most common scenario. When you drag and drop a level to Rows, the query creates a set of all the members of that level, and uses those members for rows. For example:

  Rows	Pivot Table
  Allergies level

• Members, including calculated members. You can drag and drop individual members to Rows. For example:

  Rows	Pivot Table
  10 to 19 member of Age Bucket level Green member of Favorite Color level asthma member of Allergies level

  Important:

  To drag and drop a member, you must click the member name rather than the icon to its left.

  Note that you can hide the row caption; see Specifying Pivot Options. This option is especially useful when you drag and drop individual members, because the name of the first member is also used as the caption for the rows, and this is not usually a suitable caption.

• Dimensions, including calculated dimensions. If you drag and drop a dimension to Rows, the query uses all members of the first level of the first hierarchy of that dimension. For example:

  Rows	Pivot Table
  AgeD dimension

• Named sets. If you drag and drop a named set to Rows, the query uses each item in that set as a separate row. For example:

  Rows	Pivot Table
  Sample Set 2

• Measures. If you drag and drop a measure to Rows, the system uses that measure as a row. For example:

  Rows	Pivot Table
  Avg Age measure Avg Allergy Count measure Avg Test Score measure

  If there is only one column (as in the case shown here), the data shown for the measure is the aggregate value for that measure across all records (or across all included records, if the pivot table is filtered).

  If the pivot table includes multiple columns corresponding to members, then the data in any column is the value for that measure for that member.

  If the pivot table includes columns that correspond to measures, the column definition takes precedence and the value shown is determined by the measure used in the column.

  Note that you can hide the row caption; see Specifying Pivot Options. This option is especially useful when you drag and drop measures to Rows, because the name of the first measure is also used as the caption for the rows, and this is not usually a suitable caption.

• Level properties. See Displaying Properties in a Pivot Table.

• Pivot variables. See Defining and Using Expression Pivot Variables.

• Plugins. For example:

  Rows	Pivot Table
  PatientCount plug-in property HighScoreCount plug-in property

• Quality measures.

You can also double-click a level, dimension, member, or named set in the Model Contents pane. If you do, the system removes any items currently in Rows and replaces them with what you double-clicked.

If hierarchies are displayed in the Model Contents pane, they are for informational purposes only; you cannot drag and drop them.

Specifying the Rows

To specify the rows in the pivot table, drag and drop items from the Model Contents pane to the Drop row here label in the Rows box. When you do so, the Rows box looks like this:

Now there are three places into which you can drag and drop additional items:

• If you drag and drop onto the bold item label (Age Group in this case), the Analyzer replaces the existing item with the new one.

• If you drag and drop onto Drop row here, the Analyzer adds additional rows to the table. For example:

  Rows	Pivot Table

  Note that you can hide the row caption; see Specifying Pivot Options.

• If you drag and drop onto the Add Breakout button after the bold item label, the Analyzer subdivides the existing rows. For example:

  Rows	Pivot Table

  Notice that now there are two labels called Drop row here. The non-indented one affects the outermost grouping. The indented one affects the inner grouping.

  When you nest levels this way, the system initially returns only the first 2,000 combinations of the levels. If there are more than 2,000 rows, the system displays the Show All button at the bottom of the pivot preview area. If you click this button, the system returns all the combinations, and you can page through them.

To remove an item from the rows, click the X button to the right of its label in the Rows box.

For information on the Advanced Options button , see Customizing Pivot Table Items.

Pivot Tables with One Measure

To display measures as columns, you can drag and drop them to the Measures box or to the Columns box. If the pivot table has multiple measures, the resulting pivot table (and underlying MDX query) is identical with either technique. If the pivot table has only a single measure, however, the two techniques generate slightly different MDX queries:

Pivot Tables with Multiple Measures

If you drag and drop multiple measures to the Measures box, you can specify whether the measures are used as columns (as in the default case) or as rows. To do so, click the Options button in the Measures box. The system displays a dialog box; for this discussion, use the options in the section Place measures on. Choose one of the following:

• Rows — Displays the measures as rows.

• Columns — Displays the measures as columns.

Then click OK.

This option makes it easier to redefine complex pivot tables, because you can rearrange the Rows box or Columns box and leave the measures alone.

For example, consider the following pivot table definition:

By default, the measures are used as columns (nested within the elements listed in the Columns box), as in the following example:

If you instead use the option to display the measures as rows, the pivot table looks like this:

This pivot table is equivalent to one defined as follows:

Controlling the Display of the Measure Headings

By default, when a pivot table displays a level on columns and displays measures, the pivot table may or may not include a heading for the measures, as follows:

• If the pivot table has only one measure, the measure name is not shown. For example:

  

• If the pivot table has multiple measures, the measure names are shown. For example:

  

You can control this behavior. To do so, click the Options button in the Measures box. The system displays a dialog box; for this discussion, use the options in the section Display measure headers. Choose one of the following options:

• If More Than 1 Measure — The system displays the measure name if there is more than one measure, but does not display the name if there is only one measure.

• Always — The system always displays the measure name.

• Never — The system never displays the measure name.

These options apply only when the pivot table also displays another item on columns, as in the examples shown here.

Displaying Both Properties and Measures

If you display a property as a column and you want to display a measure as another column, you must drag and drop the measure to Columns rather than to Measures. For example:

Adding a Summary Row or Column as a Pivot Option

To add a summary row, summary column, or both:

1. Click the Pivot Options button .

   The Analyzer displays the Pivot Options page, which is discussed in detail later in this book.

   This procedure discusses only the Summary option.

2. Click the Summary check box.

3. Select a summary option from the drop-down list. The options are as follows:

   • Sum — Displays the sum of the values.

   • Count — Displays the count of rows.

   • Max — Displays the maximum of the values.

   • Min — Displays the minimum of the values.

   • Average — Displays the average of the values.

   • % of Total — Displays the sum of the values of this column (or row), as a percentage of the sums of all columns (or rows).

   For information on overriding how the summary is computed for a given measure, see Specifying Alternative Aggregation Methods for a Measure.

4. (For summary rows only) Optionally, to cause the summary row to summarize all rows (including rows on later pages), select All Rows from the second drop-down list.

For example, the following pivot table includes a total row. Notice that it displays the total only for numeric values:

The following example shows % of Total, which is useful primarily when you use a level in Columns:

In contrast to the other forms of summaries, note the following:

• If you display the pivot table as a chart, the chart does not include the summary row or column.

• If you export to Excel, the summary is included only if it is a sum.

Adding the All Member as a Summary Row or Column

Your cube may include one or more All members. Each dimension can contain a All member, which is listed within the dimension, in the Model Contents pane, before any levels. For example:

An All member represents all records and is typically named something like All Ages or All Categories. Or, as in the example shown here, the All member might have a truly generic name.

You can drag and drop this member to Rows or Columns to add it as a summary row or column. For example:

In contrast to using the pivot options, this approach allows each measure to be aggregated in a different manner. The Patient Count measure is summed, and the Avg Age and Avg Allergy Count measures are averaged.

If you do not have any All members, you can add a custom aggregation.

Adding a Summary Row or Column as a Custom Aggregation

This section describes how to add a summary row as a custom aggregation. The technique is easily adapted for a summary column.

1. For the level used as the rows, add the same level again to the Rows box.

   

2. Click the Advanced Options button next to the second level in Rows.

   The Analyzer displays the Advanced Options page, which is discussed in detail later in this book.

3. Select Compute Aggregate and then select an aggregation function. You can use any of the following functions:

   • SUM — For each displayed measure, display the sum of the values of the members.

   • AVG — For each displayed measure, display the average of the values of the members.

   • MIN — For each displayed measure, display the minimum of the values of the members.

   • MAX — For each displayed measure, display the maximum of the values of the members.

   • COUNT — For each displayed measure, display the count of the members.

   • COUNT NONEMPTY — For each displayed measure, display the count of the members that have values in the current context.

   • AGGREGATE — For each displayed measure, display the aggregate value of the members, using the aggregation method defined for that measure.

   • MEDIAN — For each displayed measure, display the median of the values of the members.

   • STDDEV — For each displayed measure, display the standard deviation of the values of the members.

   • VARIANCE — For each displayed measure, display the variance of the values of the members.

   • PERCENTILE — For the given percentile value and for each displayed measure, display the measure value that is at that percentile value.

     For this option, you can specify a percentile value in the Percentile field; the default is 50.

4. Optionally type a new caption into Caption. The default caption is the name of the function.

When you use this option, the system first uses any other settings you specified on this dialog box.

For example, consider the following pivot table:

This pivot table is defined with the following items in Rows:

The second Gender item uses the following options:

• Compute Aggregate is Aggregate

• Caption is Subtotal