Formatting a pivot table and adding calculations

This tutorial shows you how to format a pivot table and add some calculations.

What you'll learn

In this tutorial, we'll show you how to:

  • Format numbers in the pivot table
  • Move some data in the pivot table to a row
  • Add a calculation to the pivot table
  • Change the display width of the pivot table

What you'll need

Step 1: Format numbers in the pivot table

First, we'll update a column to change the column name and the format of the column numbers.

To format numbers:

  1. Open the analysis you were working with in Creating analysis with a pivot table.
  2. Click An image of the Options icon Optionsfor Attributed Revenue and select Column Properties.

    An image of the Column Properties menu item

    The Column Properties dialog box appears.

  3. Click the Column Format tab. Select the Custom Headings check box, and enter Revenue in the Column Heading field.

    An image highlight the column colum heading settings

  4. Click the Data Format tab. Select the Override Default Data Format check box and select the values as indicated in the image below.

    An image showing the custom data format

  5. Click OK and then click the Results tab.

    An image showing the pivot table with the customized column settings

  6. Save your analysis.

Step 2: Changing the pivot table layout and adding calculations

In this step, you'll update the pivot table to move some of the data to columns instead of rows. You will also add a new calculation to the pivot table.

To change the pivot table layout and add a calculation:

  1. On the Results tab, click the An image of the Edit View icon Edit View to format the pivot table.
  2. Use the Layout pane to format the pivot table. Drag Campaign Product below Measure Labels.

    The Layout pane should look like this:

    An image highlighting the change to the layout columns

    The pivot table now looks like this:

    An image showing the updated pivot table with products moved to columns

    Next, add a calculation to the pivot table by duplicating the Revenue column.

  3. In the Layout pane, click An image of the Options icon More Options for the Revenue column and select Duplicate Layer.

    The duplicated Revenue column appears.

    An image showing the new duplicate layer

  4. For the duplicate Revenue column, click An image of the Options icon More Options > Format Heading.

    The Edit Format dialog appears.

  5. Enter % Revenue in the Caption field, and click OK.

    An image showing the updated column heading

    Next, we'll add a calculation to reflect a percentage of the parent.

  6. For the % Revenue column, click An image of the Options icon More Options > Show Data As > Percent of > Row Parent.

    An image highlighting the Show Data As menu selection

    The calculation is added to the column. The pivot table should look like this:

    An image showing the % Revenue columns

    Because we added these additional columns, the pivot table now has a horizontal scroll bar. Let's update the pivot table to increase the width.

  7. Click An image of the Edit Properties icon View Properties above the pivot table.

    An image showing the View Properties icon to select

    The Pivot Table Properties dialog appears.

  8. Enter 1200 in the Maximum Width field, and click OK.

    An image showing the updated Maximum Width

    The pivot table now looks like this:

    An image showing the updated pivot table with an increased width

  9. Click Done and save the analysis.

Now that you're done

Oracle University offers the following instructor-led courses to help you achieve success:

Also be sure to checkout the Oracle Business Intelligence Enterprise Edition Help Center where you can find more resources on using Oracle BI Enterprise Edition. Remember though, not all of the features included in a stand-alone version of Oracle BI EE are available in Insight.