Siebel Analytics User Guide > Working with Siebel Analytics Views in Siebel Answers >

Showing Results in Pivot Tables Using Siebel Analytics Pivot Table View


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 section provide the general steps to create a pivot table and describes additional pivot table formatting options. It contains the following topics:

General Steps for Adding or Modifying a Siebel Analytics Pivot Table View

When you add or modify a pivot table, the columns included in the request appear as elements in the pivot table template. See Table 19 for a description of pivot table positions.

To add or modify a Siebel Analytics pivot table view

  1. In Siebel Answers, create or modify the request with which you want to work, and then perform one of the following actions:
    • To add a new pivot table view, click the Results tab and choose Pivot Table from the views drop-down list.

      You can also add a pivot table view by clicking the Pivot Table button at the compound layout view or at the Criteria tab.

    • To edit an existing pivot table view, click the Results tab, choose Compound Layout from the views drop-down list, and then 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 option to show header toolbars.

    The workspace shows the pivot table template.

  3. Drag and drop the request 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 option Display Preview.

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

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

    For information about the chart view, see Showing Results in Charts Using Siebel Analytics Chart View.

  6. To sort the results, click the Order By button.

    The button changes to indicate the sort order:

  7. To add totals, perform the following actions:
    1. For totals in the Pages, Section, Row, and Column areas, click the totals button and make a selection:
      • For no totals, choose None.
      • To show the total before or after the data items, choose 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, choose 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, the Siebel Analytics Server will calculate 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.

  8. To work with additional options for a column, measure, or row, click the More Options or Formatting button and make a selection from the drop-down list.

    For more information about the cosmetic formatting you can apply to headings, see Applying Cosmetic Formatting to Results and Dashboards.

  9. To format labels or values for a column, click the Totals button or the More Options button, and then select the appropriate format option.

    For information about formatting columns, see Adding Formatting in Siebel Analytics Pivot Tables.

  10. When you are done, you can save the request with the pivot table view.
Table 19. Siebel Analytics Pivot Table Positions
Position
Description

Pages

Provides an interactive result set that allows users to select the data they want to view. The values from the columns that appear in the Pages 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. For more information, see Using Multiple Page Drop-Down Lists in Siebel Analytics Pivot Tables.

Sections

Provides initial filter criteria. 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, represents the label position for the Measures columns. 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 request after the pivot table has been created, is added as excluded.

After displaying a pivot table, if you then exclude a column with the pivot table view designer, Siebel Analytics Web redisplays the pivot table view using the existing data. If you exclude a column in the request definition for the pivot table view, Siebel Analytics requeries the database to update the data.

Using Multiple Page Drop-Down Lists in Siebel Analytics Pivot Tables

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

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 a Siebel Analytics 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 Siebel Analytics Pivot Tables

The default aggregation rule for a measure is specified in the Siebel Analytics repository, or by the original author of the report.

To override the default aggregation rule for a measure in a Siebel Analytics 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.

    The chosen aggregation rule for the measure is indicated by a check mark.

Adding Formatting in Siebel Analytics 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.

For information about formatting columns and adding navigation as part of the column format, see Using Column Formatting Functions in Siebel Answers.

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 Applying Conditional Formatting to Column Content in Siebel Answers.

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. At the pivot table view, click the Table View Properties button near the top of the workspace.
  2. To add green bar styling, click the green bar styling check box.

    To change the way the styling is applied, make a selection from the drop-down list.

  3. To add cosmetic formatting, click the alternate formatting button.

    The cosmetic formatting dialog box appears.

  4. To change the default green bar color, choose a new background color for the cell format.

    For more information about cosmetic formatting, see Applying Cosmetic Formatting to Results 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

  1. At the pivot table view, click the Section Properties button.

    The Section Properties dialog box appears.

  2. To format the appearance of the section, make selections from the Section Properties tab.
  3. To format the appearance of the section content, make selections from the Content Properties tab.

    For information about applying cosmetic formatting, see Applying Cosmetic Formatting to Results 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

  • At 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 to Results and Dashboards.

    • To hide a row from the output, choose Hidden.
    • To define a new calculated item, see Building Calculations in Siebel Analytics Pivot Tables.
    • To duplicate the row in the pivot table, choose Duplicate Layer.
    • To remove the column from the request, choose Remove Column.

      The column is removed from the pivot table and all other result views for the request.

Displaying Running Sums in Siebel Analytics 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 continued 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 choose the following option:

    Display as running sum

Showing an Item's Relative Value in Siebel Analytics 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 Siebel Analytics pivot table

  1. At the pivot table view, click the More Options button for the item you want to show as a relative value.

    NOTE:  The following step is optional. When you duplicate the measure in the pivot table, you can see both the total for the measure and its relative value. This eliminates the need to add the column twice on the Criteria tab to see the total and its relative value in the pivot table.

  2. To duplicate the measure, select the option Duplicate Layer.

    The measure appears a second time in the pivot table, with the same name. If you want to rename the measure, click More Options and select Rename.

  3. Select the option Show Data As and choose 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 shown in the following table. For Percent of choices, the number of decimal places is dependent upon the type of measure.

    Percent of or Index of
    Result

    Column

    Shows the percentage of the column, or index value, that this value constitutes.

    Row

    Shows the percentage of the row, or index value, that this value constitutes.

    Section

    Shows the percentage of the section, or index value, that this value constitutes.

    Page

    Shows the percentage of the page, or index value, that this value constitutes.

    Column Parent

    Shows the percentage of the column parent, or index value, that this value constitutes.

Using Calculations in Siebel Analytics 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 Siebel Analytics repository, and for an existing report, the aggregation rule chosen by the author.

Table 20 describes the calculations that you can use in pivot tables.

Table 20. Siebel Analytics Pivot Table Calculations
Calculation
Description

Default

Applies the default aggregation rule as in the Siebel Analytics 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.

None

No calculation is applied.

Internally, Siebel Analytics Web processes pivot table calculations as SQL SELECT statements, and performs the indicated functions on the result set. For more information about SQL functions, see Siebel Analytics Server Administration Guide.

Building Calculations in Siebel Analytics 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 Siebel Analytics pivot table

  1. In the Pages, Sections, Rows, or Columns 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, choose 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. See Examples of Calculations in Siebel Analytics Pivot Tables for examples of the kinds of calculations that you can build.
    • 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 nearest whole number.

  5. To build a formula, choose 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. See Examples of Calculations in Siebel Analytics Pivot Tables for examples of what you can do.
    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 Siebel Analytics 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')

Related Topics

Overview of Siebel Answers

Performing Common Tasks When Working with Siebel Analytics Views

Siebel Analytics User Guide