Print      Open PDF Version of Online Help

Reports > Step 2: Creating Layouts > Showing Results in Pivot Tables

Showing Results in Pivot Tables

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 report appear as elements in the pivot table template.

SHOW ME

The following table describes the pivot table positions.

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

1. In Oracle CRM On Demand Answers, in the Create Layout page, perform one of the following actions:
   • To add a new pivot table view, click Add View, and then select Pivot Table.
   • To edit an existing pivot table view, click the Edit View button for the pivot table view.

     The workspace shows the options and settings for the view.

2. To view the pivot table template and buttons for working with each column individually, select the Show Controls check box.

   The workspace shows the pivot table template.

3. Drag and drop the report columns, which appear as elements in the pivot table, to the desired positions in the pivot table template.
4. To see a preview, select the Display Results check box.

   You can click the Display Results link to refresh the results.

5. To add a Chart view next to the pivot table, select the Chart Pivoted Results check box.

   For information about the Chart view, see Showing Results in Charts.

6. To change the location of the chart relative to the pivot table, select the location from the Chart Position list.

   You can also choose to see only the chart and hide the table by selecting Chart Only from the list.

7. To sort the results, click the Order By button for the column on which you want to sort.

   The button changes to indicate the sort order:

   • An up arrow indicates ascending sequence.
   • A down arrow indicates descending sequence.

     For more information about sorting, see Sorting and Reordering Columns.

8. To add totals, perform the following actions:
   1. 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.
   2. 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 Answers calculates the total based on the entire result set, before applying any filters to the measures.

      Depending on the position of this element, 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.

9. 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.
10. To format labels or values for a column, click the Totals button or the More Options button, and then select the appropriate format option.
11. Save the report.

Using Multiple Page Drop-Down Lists in Pivot Tables

When you place multiple attributes in the Pages area in the pivot table, you can create a multiple drop-down list page. Then, when users view the pivot table, they see a drop-down list for each attribute, rather than a concatenated list of attributes placed in the page heading (the default).

For example, if you place Region and Brand in the pages area, a Region drop-down list allows the user to select a particular region, and see the data for only that region, rather than seeing Region concatenated with Brand.

To create an independent drop-down list in an Oracle CRM On Demand Answers pivot table

1. Drag and drop your target attributes into the Pages area.
2. Click the More Options button on the second (or any subsequent) attribute in the Pages layout area.
3. Select the option Start New Page Drop Down.

   The drop-down list for the attribute appears above the preview of the pivot table.

You can create drop-down lists for other attributes in the Pages area. When the report is saved, the drop-down lists 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

1. Click the More Options button for the measure whose default aggregation rule you want to override.
2. 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

1. In the pivot table view, click the Pivot Table View Properties button near the top of the workspace.
2. To add green bar styling, select the Enable Alternating Row Green Bar Styling check box.

   To change the way the styling is applied, make a selection from the Alternate list.

3. To add cosmetic formatting, click the Set Alternate Format button.
4. In the Edit 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 Changing the Appearance of Reports.

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

1. To format the appearance of a section, do the following:
   1. In the pivot table view, click the Section Properties button.
   2. In the Section Properties dialog box, make your selections, and click OK.
2. To format the appearance of the section content, do the following:
   1. Click the Content Properties button.
   2. In the Content Properties dialog box, make your selections and click OK.

      For information about applying cosmetic formatting, see Changing the Appearance of Reports.

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 Changing the Appearance of Reports.
  • 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

1. 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 Define Criteria page to display the total and its relative value in the pivot table.

2. 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.

3. 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.

For more information about SQL functions, see Using Functions in Analyses.

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

1. In the Sections or Rows area, click the More Options button for the measure on which you want a calculation performed.
2. Select the option New Calculated Item.

   The Calculated Item window appears.

3. Assign a name for the calculation in the Name field.
4. To build a calculation other than a formula, select from the following options:
   • To build one calculation, select the function to work with from the Function drop-down list, and click on one or more items in the Values list to add them to the Function field.
   • To build multiple calculations for multiple items, type the functions and click on the item names to add them to the Function field.
   • If you are averaging a column with a type of integer, change the formula for the column to cast it to a double (floating point) type. For example, if the current formula is x, change it to CAST(x as double).

     NOTE: Averages on pivot tables are rounded to the nearest whole number.

5. To build a formula, select the Formula function.

   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.

   1. In the Function field, build the formula by typing or clicking measure names, and clicking operators to insert them into the formula.
   2. Use parentheses, where appropriate.
6. When the calculation is complete, click Finished.

   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')