Analytics > Designing an Analysis > Step 2: Reviewing Results and Creating Layouts > Showing Results in Pivot Table Views
Showing Results in Pivot Table Views
The Pivot Table view is an interactive view that allows you to rotate the rows, columns, and section headings to obtain different perspectives of the data. Pivot tables are navigable and drillable, and are especially useful for trend reports.
This topic describes the pivot table positions and provides instructions for performing the following tasks:
- Adding or modifying pivot table views
- Using multiple page drop-down lists in pivot tables
- Overriding the default aggregation rules in pivot tables
- Adding formatting to pivot tables
- Displaying running sums in pivot tables
- Showing items as relative values in pivot tables
- Using calculations in pivot tables
- Building calculations in pivot tables
Pivot Table Positions
When you add or modify a pivot table, the columns included in the analysis appear as elements in the pivot table template.
The following table describes the pivot table positions.
Position
|
Description
|
Pivot Table Prompts
|
Provides an interactive result set that allows users to select the data they want to display. The values from the columns that appear in the Pivot Table Prompts position are used as the initial filter criteria. The values appear in a drop-down list for selection. Based on that selection, a pivot table (composed of the Sections, Columns, Rows, and Measures defined in the pivot table) appears.
|
Sections
|
Populates the areas that divide the view into sections. For each value in the Section column, a unique pivot table appears, composed of the Columns, Rows, and Measures defined in the pivot table.
|
Columns
|
Shows an element in a column orientation. Pivot tables can contain multiple columns.
|
Rows
|
Shows an element in a row orientation. Like columns, pivot tables can contain multiple rows.
|
Measures
|
Populates the section of a pivot table that contains summary data. The elements in the Measures area are summarized based on the elements in the page, section, row, and column fields. Each value in the Measures elements represents a summary of data from the intersection of the source rows and columns.
The Measure Labels element, which appears in the Columns area by default, controls the position and formatting of the column heading for the data in the Measures section. It also provides totaling and ordering capabilities. If there is only one measure, this element can be excluded.
|
Excluded
|
Excludes columns from the pivot table results. Any column that is added as criteria for the report after the pivot table has been created is added as excluded.
|
Adding or Modifying Pivot Table Views
The following procedure provides the basic steps to add or modify a Pivot Table view.
To add or modify a Pivot Table view
- In the Results tab perform one of the following actions:
- To rearrange columns, hover the pointer over the column title until the drag bar appears above the title, then drag the bar to a new location.
- To add a graph view to the pivot table, click the graph icon in the toolbar.
After you have added a graph to the pivot table, you can change the position of the graph, change the graph type, and so on. For information about the graph view, see Showing Results in Graph Views.
- To add totals, perform the following actions:
- For totals in the Pages, Sections, Rows, and Columns areas, click the Totals button and make a selection:
- For no totals, select None. The Totals button appears with a white background (default).
- To show the total before or after the data items, select Before or After. For example, if you add a total on a row containing regions and specify the Before option, the total is shown before individual districts in the region are listed.
- For totals in the Measures area, click the More Options button for the row or column to be totaled, select Aggregation Rule, select a value, and make sure the option Report-Based Total is selected.
NOTE: If the option Report-Based Total is not selected, Oracle CRM On Demand calculates the total based on the entire result set, before applying any filters to the measures.
Depending on which totals are selected at the rows or columns level, the totals for the summary data represented by the Measures elements display as a column or row. Column and row totals include labels.
When the Totals button is dimmed, no totals will appear.
- To work with additional options for a column, measure, or row, click the More Options button and make a selection from the drop-down list.
- To format labels or values for a column, click the Totals button or the More Options button, and then select the appropriate format option.
- Save the report and then click Done.
Using Multiple Drop-Down Prompts in Pivot Tables
When you place multiple attributes in the Pivot Table Prompts area in the pivot table, you can create a multiple drop-down prompts page. Then, when users view the pivot table, they see multiple drop-down prompts for each attribute.
When the report is saved, the drop-down prompts will be available to users with access to the report.
Overriding Default Aggregation Rules in Pivot Tables
You can override the default aggregation rule for a measure, which is specified by either the original author of the report or by the system.
To override the default aggregation rule for a measure in a pivot table
- Click the More Options button for the measure whose default aggregation rule you want to override.
- Select the option Aggregation Rule, and then select the aggregation rule to apply.
Adding Formatting in Pivot Tables
You can apply green bar styling and cosmetic formatting to a pivot table. You can also customize the appearance of sections, rows, columns, measures, and the content that they contain.
For example, you can specify font, cell, border, and style sheet options for sections, values, and measure labels. For sections, you can include and customize the position of column headings together with the values in that section. You can also insert page breaks, so that every time a value changes in the section, the new section appears on a new page.
You can also add conditional formatting, which helps direct attention to a data element if it meets a certain threshold. To add conditional formatting to a column in a pivot table, see Editing Column Properties.
Adding Green Bar Styling and Cosmetic Formatting to a Pivot Table
Green bar styling shows alternating rows or columns in a light green color. Cosmetic formatting affects the overall appearance of the pivot table and also allows you to change the default green bar color.
To add green bar styling and cosmetic formatting to a pivot table
- In the pivot table view, click the Pivot Table View Properties button near the top of the workspace.
- To add green bar styling, select the Enable alternating styling check box.
To change the way the styling is applied, make a selection from the Alternate list.
- To add cosmetic formatting, click the Format button.
- In the Alternate Row Format dialog box, to change the default green bar color, select a new background color for the cell format.
For more information about cosmetic formatting, see Applying Cosmetic Formatting in Analyses and Dashboards.
Adding Formatting for Sections and Section Content
Section and content formatting options allow you to do the following:
- Include and customize the position of column headings together with the values in that section.
- Insert page breaks. Every time a value changes in the section, that new section will appear on a new page. This is useful for data-driven detail reports.
- Apply cosmetic formatting to a section and its content.
To add formatting for sections in a pivot table
- To format the appearance of a section, do the following:
- Click the Edit View button in the pivot table view and click the Section Properties button.
- In the Section Properties dialog box, make your selections, and click OK.
- To format the appearance of the section content, do the following:
- For each field you wish to format, click the More Options button and choose Column Properties from the drop-down menu.
- In the Column Properties dialog box, make your selections and click OK.
For information about applying cosmetic formatting, see Applying Cosmetic Formatting in Analyses and Dashboards.
Adding Formatting for Rows
Row and row content formatting options allow you to do the following:
- Apply cosmetic formatting to rows, row headings, and row values.
- Use a row in pivot table calculations but suppress its display in results.
- Define a new calculated item for use in a pivot table.
- Duplicate the row in the pivot table.
- Remove a column from the pivot table view.
To add formatting for rows
- In the pivot table view, click the More Options button for the row and make a selection from the drop-down list:
- To apply cosmetic formatting to row headings or values, select the appropriate option.
- For information about applying cosmetic formatting, see Applying Cosmetic Formatting in Analyses and Dashboards.
- To hide a row from the output, select Hidden.
- To define a new calculated item, click New Calculated Item.
For more information on defining calculated items, see the Building Calculations in Pivot Tables section of this topic.
- To duplicate the row in the pivot table, select Duplicate Layer.
- To remove the column from the report, select Remove Column.
- The column is removed from the pivot table and all other result views for the report.
NOTE: The Data Format settings for columns included in the Measures area inherit the settings for columns in the section.
Displaying Running Sums in Pivot Tables
Numeric measures in a pivot table can be displayed as running sums, where each consecutive cell for the measure displays the total of all previous cells for that measure. This option is a display feature only that has no effect on actual pivot table results.
Typically, running sums would be displayed for duplicated columns or for measures for which the option to show data as a percentage of the column has been selected, with the last value being 100 percent. Running sums apply to all totals. The running sum for each level of detail is computed separately.
Column headings are not affected when the running sum option is selected. You can format the column heading if you want it to indicate that the running sum option is in effect.
The following usage rules are in effect for running sums:
- A running sum is incompatible with the SQL RSUM function (the effect would be a running sum of the running sum).
- All running sums are reset with each new section. A running sum does not reset at a break within a section or continue across sections.
- If a measure does not display in a single column or in a single row, the measure is summed left to right and then top to bottom. (The lower right cell will contain the grand total.) A running sum does not reset with each row or column.
- Rolling minimums, maximums, and averages are not supported.
To display a measure as a running sum
- In the Measures area, click the More Options button for the row or column to be summed and select the following option:
Display as running sum
Showing an Item’s Relative Value in Pivot Tables
You can dynamically convert a stored or calculated measure on a pivot table into a percent or an index. This shows the relative value of the item, compared to the total, without the need to explicitly create a calculation for it.
For example, if you are using a pivot table to examine sales by region, you can duplicate the sales measure and view it as a percentage of the total. This allows you to see the actual sales, and the percentage of sales, that each region accounts for.
You can view the measure as a percentage between 0.00 and 100.00, or as an index between 0 and 1. Deciding which method to use is at your discretion.
To show an item as a relative value in a pivot table
- In the pivot table view, click the More Options button to make the column show as a relative value.
The following step is optional. When you duplicate the measure in the pivot table, you can display both the total for the measure and its relative value. This eliminates the need to add the column twice on the Criteria tab to display the total and its relative value in the pivot table.
- To duplicate the measure, choose Duplicate Layer.
The measure appears a second time in the pivot table, with the same name. To rename the measure, click More Options and choose Format Headings, then enter the new name in the first field.
- Click More Options and choose Show Data As, choose either Percent of or Index of, and then choose the appropriate submenu option.
NOTE: The option Show Data As is available only for items that are stored or calculated measures.
The options for Percent of and Index of are:
- Column
- Row
- Section
- Page
- Column Parent
- Row Parent
- Layer (If you choose layer, then you must also choose a column in the report by which to group the percentages.)
Using Calculations in Pivot Tables
You can use calculations in a pivot table to obtain different views of the data. The calculations allow you to override the default aggregation rule specified in the system, and for an existing report, the aggregation rule chosen by the author.
The following table describes the calculations that you can use in pivot tables.
Calculation
|
Description
|
Default
|
Applies the default aggregation rule as defined in the repository or by the original author of the report.
|
Sum
|
Calculates the sum obtained by adding up all values in the result set. Use this on items that have numeric values.
|
Min
|
Calculates the minimum value (lowest numeric value) of the rows in the result set. Use this on items that have numeric values.
|
Max
|
Calculates the maximum value (highest numeric value) of the rows in the result set. Use this on items that have numeric values.
|
Average
|
Calculates the average (mean) value of an item in the result set. Use this on items that have numeric values. Averages on pivot tables are rounded to nearest whole number.
|
First
|
In the result set, selects the first occurrence of the item.
|
Last
|
In the result set, selects the last occurrence of the item.
|
Count
|
Calculates the number of rows in the result set that have a nonnull value for the item. The item is typically a column name, in which case the number of rows with nonnull values for that column are returned.
|
Count Distinct
|
Adds distinct processing to the Count function. This means that each distinct occurrence of the item is counted only once.
|
Formula
|
Opens a toolbar that lets you select mathematical operators to include in the calculation.
|
Server Complex Aggregate
|
This setting forces the aggregation rule to be determined and calculated by the Analytics Server, rather than the Pivot table. It issues an 'AGGREGATE(x by y)' statement which the Analytics server interprets to mean: use the most appropriate aggregation rule for measure 'x' to get it to level 'y'.
|
None
|
No calculation is applied.
|
For more information about SQL functions, see Using Functions in Analytics.
Building Calculations in Pivot Tables
You can build calculations for items in the Pages, Sections, Rows, and Columns areas.
To build a calculation for an item in a pivot table
- In the Sections or Rows area, click the More Options button for the measure on which you want a calculation performed.
- Select the option New Calculated Item.
The Calculated Item window appears.
- Assign a name for the calculation in the Display Label field.
- To build a calculation other than a formula, select from the following options:
- To build a formula, select the Custom Formula function in the Function drop-down list and enter the formula in the Selected field.
NOTE: A formula creates a dynamic custom grouping within the pivot table. All measures referenced in a formula must be from the same logical column and must be present in the results. Formulas can be inserted into, or combined with, other calculations.
The mathematical operators become visible. The operators are shown in the following table.
Operator
|
Description
|
+
|
Plus sign, for an addition operation in the formula.
|
-
|
Minus sign, for a subtraction operation in the formula.
|
*
|
Multiply sign, for a multiplication operation in the formula.
|
/
|
Divide By sign, for a division operation in the formula.
|
$
|
Dollar sign, for acting upon the row position of an item in a formula.
|
(
|
Open parenthesis, to signify the beginning of a group operation in the formula.
|
)
|
Close parenthesis, to signify the ending of a group operation in a formula.
|
- In the Selected field, build the formula by typing or clicking measure names, and clicking operators to insert them into the formula.
- Use parentheses, where appropriate.
- When the calculation is complete, click OK.
If any errors are detected, a message will appear. Correct the error and click Finished again.
Examples of Calculations in Pivot Tables
The examples and explanations in this section assume that you have a basic understanding of SQL and its syntax. The examples are hypothetical. Not all possible calculations are shown.
Example 1. This example obtains the value of the current measure, such as dollar sales, for each of the products SoftDrinkA, SoftDrinkB, and SoftDrinkC, and adds the values together.
sum('SoftDrinkA','SoftDrinkB','SoftDrinkC')
This is equivalent to selecting Sum from the Function drop-down list, and then typing or clicking ‘SoftDrinkA’,‘SoftDrinkB’,‘SoftDrinkC’ to add them to the Function field.
Example 2. This example obtains the minimum current measure, such as dollars in sales, for SoftDrinkA or SoftDrinkB, whichever is lower.
min('SoftDrinkA','SoftDrinkB')
In Example 1 and Example 2, each functional calculation is performed for each item in the outer layer, such as the Product layer. For example, if Year and Product are laid out on an axis, and one of the preceding calculations is built on the Product layer, the results will be computed per year.
Example 3. This example obtains the values for each item in the outer layer, such as Year and Product, and adds them together.
sum(*)
Example 4. This example obtains the current measure, such as dollar sales, of the item from the first, second, and third rows, and sums them.
sum($1,$2,$3)
Instead of specifying a named item, such as SoftDrinkA, you can specify $n or $-n, where n is an integer that indicates the item’s row position. If you specify $n, the measure is taken from the nth row. If you specify $-n, the measure is taken from the nth to the last row.
For example, for dollar sales, $1 obtains the measure from the first row in the data set, and $-1 obtains the measure from the last row in the data set.
Example 5. This example adds sales of SoftDrinkA, SoftDrinkB, and SoftDrinkC.
'SoftDrinkA' + 'SoftDrinkB' + 'SoftDrinkC'
This is equivalent to the following calculation:
sum('SoftDrinkA','SoftDrinkB','SoftDrinkC')
Example 6. This example adds sales of SoftDrinkA with sales of diet SoftDrinkA, then adds sales of SoftDrinkB with sales of diet SoftDrinkB, and then returns the maximum of these two amounts.
max('SoftDrinkA' + 'diet SoftDrinkA', 'SoftDrinkB' + 'diet SoftDrinkB')
|