Using the DeepSee Analyzer
Customizing Pivot Tables
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

This chapter describes how to further customize pivot tables. It discusses the following topics:

Specifying Pivot Options
The Pivot Options dialog box provides many ways to customize the pivot table. To access this dialog box, click the Pivot Options button . The Analyzer displays the following:
Here you can do the following:
As you make changes, the Preview area is updated to demonstrate the changes. For example:
Customizing Pivot Table Items
The Analyzer displays Advanced Options button in the Rows, Columns, and Measures boxes, next to each item in those boxes. The same button is also available for the Rows and Columns boxes.
For example:
If you click any of these buttons, you see the following dialog box or a variation of it (the details are different for measures). The following figure shows only the top part of the dialog box:
The following sections describe changes you can make.
You can specify formatting in multiple places. If you specify different formatting, the following rules control which formatting options are used:
Displaying a Constant Row or Column
You can display a constant value rather than the selected item. To do so:
  1. Click the Advanced Options button next to the item.
  2. Click Value.
  3. Type a value into Value. For example:
  4. Click OK.
You might also want to specify a different caption; see Specifying New Captions,” later in this chapter.
Specifying a Spacer Row or Column
You can display a spacer row or column rather than the selected item or items. To do so:
  1. Click the Advanced Options button next to the item.
  2. Click Space.
  3. Click OK.
The following shows an example:
For this pivot table, the Rows definition is as follows:
Displaying an Different Set for Rows or Columns
You can display an alternative set of elements rather than the selected item or items. To do so:
  1. Click the Advanced Options button next to the item.
  2. Click Advanced.
  3. Type an MDX set expression into MDX Expression.
    For information, see Using MDX with DeepSee.
  4. Click OK.
Specifying New Captions
To specify new captions:
  1. Click the Advanced Options button next to the item or in the header of the Rows or Columns box, as appropriate.
  2. Type a value into Caption.
    To include the original name in the caption, use an asterisk (*) at the appropriate position.
  3. Click OK.
For example:
In this case, Caption is Group: *
Specifying a Format String
A format string controls how numbers are displayed and can also specify colors to use. To specify the format string for an item:
  1. Click the Advanced Options button next to the item or in the header of the Rows or Columns box, as appropriate.
  2. Click the Find button next to Format.
    The system displays a dialog box that includes the following fields:
    Here:
    In each of these, Format string specifies the numeric format, and Color specifies the color.
  3. Specify values as needed (see the details after these steps).
  4. Click OK.
Format String Field
The Format string field is a string that includes one of the following base units:
Base Unit Meaning Example
# Display the value without the thousands separator. Do not include any decimal places. 12345
#,# Display the value with the thousands separator. Do not include any decimal places. This is the default display format for positive numbers. 12,345
#.## Display the value without the thousands separator. Include two decimal places (or one decimal place for each pound sign after the period). Specify as many pound signs after the period as you need. 12345.67
#,#.## Display the value with the thousands separator. Include two decimal places (or one decimal place for each pound sign after the period). Specify as many pound signs after the period as you need. 12,345.67
You can include additional characters before or after the base unit.
The following table shows some examples:
Example formatString Logical Value Display Value
formatString="#,#;(#,#);"
Note that this corresponds to the default way in which numbers are displayed.
6608.9431 6,609
–1,234 (1,234)
formatString="#,#.###;" 6608.9431 6,608.943
formatString="#%;" 6 600%
formatString="$#,#;($#,#);" 2195765 $2,195,765
–3407228 ($3,407,228)
Color Piece
For the Color field, specify either of the following:
Specifying Cell and Header Styles
To specify the cell and header styles for an item:
  1. Click the Advanced Options button next to the item or in the header of the Rows or Columns box, as appropriate.
  2. Select values in the Cell Style and Header Style groups. These enable you to specify the style of the cells and the headers, respectively:
    To use these options, click each drop-down and select a value. For example:
    This style definition specifies that the header is in 12 pt, blue Verdana on a pale green background. The header is also bold and center-aligned.
  3. Click OK.
Sorting and Filtering Members
You can filter and sort the members of a level or named set in various ways. To do so:
  1. Click the Advanced Options button next to a level or named set or in the header of the Rows or Columns box, as appropriate.
  2. Specify the following options as needed:
  3. Click OK.
Specifying Alternative Aggregation Methods for a Measure
For any cell in a pivot table, each measure is aggregated from the lowest-level data. Aggregation methods include sum, average, maximum value, and others. By default, DeepSee uses the aggregation method specified in the measure definition. You can specify an alternative method.
Similarly, if you add a summary row or column, you specify how to aggregate the displayed values into a single number for that summary row or column. You can specify an alternative method for this as well.
To specify alternative aggregation methods for a measure:
  1. Click the Advanced Options button next to the measure.
  2. Specify either or both of the following options:
  3. Click OK.
See also Adding a Summary Row or Column,” earlier in this book.
Customizing Double-Click Drilldown
By default, when you double-click a row in a pivot table, DeepSee drills down to the next lowest level in the hierarchy, if any (see Drilldown via Double-Click,” later in this book). You can customize this behavior. To do so:
  1. Click the Advanced Options button in the header of the Rows box.
  2. Expand the Drilldown Options group if that is collapsed.
  3. For Drilldown Expression, specify one or more MDX set expressions, one per line. For example:
    colord.h1.[favorite color].members
    gend.h1.gender.members
    To specify MDX set expressions, you can do either of the following:
    Typically you use set expressions of the form [dimension].[hierarchy].[level].MEMBERS, where dimension is the logical name of a dimension, hierarchy is the logical name of a hierarchy, and level is the logical name of a level. This expression represents the members of the given level.
    If these identifiers do not include spaces, you can omit the square brackets. Also, the expression is not case-sensitive.
    The first set expression controls what happens when the user double-clicks the first time. Specifically, when the user double-clicks the first time, DeepSee creates a new query that uses this set expression for rows and that is filtered to the given context.
    Similarly, the second expression controls what happens when the user double-clicks the second time, and so on.
    The Drilldown Expression option overrides any other drilldown behavior in this pivot table. That is, if a level is in a hierarchy, the hierarchy drilldown behavior does not occur for this pivot table.
  4. Click OK.
For an example, see Custom Double-Click Drilldown,” later in this book.
Note:
The same option is available in the Advanced Options button in the header of the Columns box. This option is ignored by default but is used if you pivot the table (via the Transpose button ) and then perform double-click drilldown.
Applying Conditional Formatting
You can apply conditional formatting, which can add color, text, or graphics to pivot table cells. To do so, you create rules that examine the values in the cells. This conditional formatting overrides any customization you added to the pivot table.
To do so, click the Conditional Formatting button in the toolbar. The system displays a dialog box like the following (in this example, one rule has been specified):
Here, you can do the following:
Rules are applied in the same order that they are listed here. If a later rule contains formatting information that is inconsistent with an earlier rule, the system uses the formatting specified in the later rule.
Applying an Overall Color
If you click a button in the section Color all cells proportionally according to value, the cells are colored according to their values. This works as follows: Each button displays a gradient of colors. To assign a color to a cell, DeepSee examines the range of values of the cells. If a value is at the bottom of this range, DeepSee uses the color that is shown at the top of the gradient button (a dark blue in the following example). If a value is at the top of this range, DeepSee uses the color that is shown at the bottom of the gradient button (a light blue in the following example). For other values, DeepSee uses an intermediate color. The following shows an example:
Adding a Rule
To add a rule, click the plus sign button. The system displays a dialog box where you can specify the rule details (in this example, one rule has been specified):
Here you can do the following:
The bottom of the dialog box shows a preview of the formatting.
Specifying the Print Settings
To specify the print options for a given pivot table:
  1. Display that pivot table in the Analyzer.
  2. Click the Print Options button .
  3. Click Page Setup.
  4. Click OK.
  5. Save the pivot table.
For comments on printing, see Printing Pivot Tables,” later in this book.
Note that when you create dashboards, you can specify print settings in the dashboards as well. See Customizing Print Settings for a Widget in Creating DeepSee Dashboards.
Specifying the MDX Query Manually
Sometimes it is useful to see and then modify the MDX query that DeepSee generates for a pivot table. To do so:
  1. Click the Query Text button .
    DeepSee then displays a dialog box that displays the query used by this pivot table.
  2. If you want to use a different query, click Manual Mode.
  3. Edit the query. See the following subsection for an example.
    If you had displayed a detail listing, the bottom area of this dialog box also displays the listing query that DeepSee used:
  4. Click OK.
When the Analyzer displays a pivot table that is defined by a manually edited or manually entered MDX query, the Query Text button changes to the following: . Also, the Rows, Columns, and Measures boxes are grayed out. You cannot use locally defined calculated members unless you also add the appropriate WITH clause to your query. You can, however, drag items to the Filters box; DeepSee applies these filters but does not modify the manual query text. That is, the base query and its filters are stored separately within the pivot table definition.
Tip:
You can also use the Query Text option to copy and paste the query (for example, to use in the MDX shell).
Modifying Details of the 80/20 Suppression Option
For example, if you had used the 80/20 suppression option, the MDX query might look like this (with harmless line breaks added):
SELECT 
NON EMPTY {[Measures].[Amount Sold],[Measures].[Units Sold],[Measures].[%COUNT]} 
ON 0,
NON EMPTY 
{TOPPERCENT([Product].[P1].[Product Name].Members,80),
%LABEL(SUM(BOTTOMPERCENT([Product].[P1].[Product Name].Members,20)),"Other",,,,"font-style:italic;")} 
ON 1 
FROM [HoleFoods]
For the TOPPERCENT and BOTTOMPERCENT functions:
  1. The first argument specifies the set of members to use.
  2. The second argument specifies the percentage.
  3. The third argument (omitted in the preceding example) specifies the measure to use for ranking the members.
To change the percentages, change the second arguments for TOPPERCENT and BOTTOMPERCENT. For example:
SELECT 
NON EMPTY {[Measures].[Amount Sold],[Measures].[Units Sold],[Measures].[%COUNT]} ON 0,
NON EMPTY 
{TOPPERCENT([Product].[P1].[Product Name].Members,90),
%LABEL(SUM(BOTTOMPERCENT([Product].[P1].[Product Name].Members,10)),"Other",,,,"font-style:italic;")}
ON 1 
FROM [HoleFoods]
For details on MDX, see Using MDX with DeepSee and DeepSee MDX Reference.