Before You Begin

This tutorial shows you how to define rules to conditionally format grand totals and subtotals in table and pivot visualizations.

Background

In Oracle Analytics, you can define rules on measures such as revenue, sales, and profit to highlight events, values, and results. Your rules define the visual formatting options such as bar, line, and cell colors, font and font styles, and background colors.

What Do You Need?

Create a Dataset

In this section, you add the sample_order_lines2023.xlsx file to Oracle Analytics to create a dataset.

  1. Sign in to Oracle Analytics.
  2. On the Home page, click Create, and then click Dataset.
  3. In Create Dataset, click Drop data file here or click to browse, select the sample_order_lines2023.xlsx file, and then click Open.
  4. In Create Dataset Table from sample_order_lines2023.xlsx, click OK. Click Save.
  5. In Save Dataset As, enter sample_order_lines2023, and then click OK.
    Description of sample_order_lines_ds.png follows
    Description of the illustration sample_order_lines_ds.png

Create a Table Visualization

In this section, you create a table visualization and set the property to display the grand total of values in the data columns. You add a data column to the table that creates subtotal values.

  1. In the sample_order_lines2023 dataset, click Create Workbook.
  2. Close the Auto Insights panel.
  3. In the Data Data pane icon pane, hold down the Ctrl key, select Product Sub Category, Sales, and Profit. Right-click, select Pick Visualization, and then click Table Table vis icon.
  4. Click Properties Properties icon.
  5. In Properties, click Totals Totals icon. In the Grand Total row, click Off to select On.


    The table show grand total values for the Sales and Profit columns.

    Description of table_grand_totals.png follows
    Description of the illustration table_grand_totals.png
  6. Click Grammar Grammar icon. In the Data Data pane icon pane, select Product Category, drag it to Rows and release above Product Sub Category.
  7. Click Properties Properties icon. In Properties, click Totals Totals icon. In the Subtotal row, click None and select Product Category.


    Description of prod_cat_subtotal.png follows
    Description of the illustration prod_cat_subtotal.png

Define a Conditional Formatting Rule

In this section, you define a conditional formatting rule for the subtotals and grand totals.

  1. In the table visualization, click Manage Conditional Formatting Rules Conditional Formatting icon.
  2. In Conditional Formatting, enter Notable Profit in Name. Click the Measure list, select Profit from Visualization Columns.
  3. In the condition, select greater than (>) and in Target and enter 200000.
  4. Click Format Format icon and click in the color scale to select a color.


    This tutorial uses #7ff80e, a light bright green.

    Description of format_color.png follows
    Description of the illustration format_color.png
  5. Click Save.
  6. Click Properties Properties icon. In Properties, click Values Values icon.
  7. Under Profit in the Condition Formatting group, click None in the Apply To row, select Product Category Total and Row Grand Total. If selected, remove the check from Data.


    Oracle Analytics updates the table by applying conditional formatting to profit data that matches the defined rule.

    Description of profit_formatted.png follows
    Description of the illustration profit_formatted.png

Modify the Conditional Formatting and Visualization

In this section, you change the visualization type and modify the conditional formatting rule.

  1. In the table visualization, click Change Visualization Type Change Visualization Type icon and click Pivot Pivot visualization icon.


    Description of pivot_vis.png follows
    Description of the illustration pivot_vis.png
  2. Click Manage Conditional Formatting Rules Conditional Formatting icon. In Conditional Formatting, click Format Format icon with green background.
  3. In Format, click a color block such as the lilac color, #9e7fcc.
  4. Click Font. In the Font Type list, click Auto and select Tahoma. In the Font Size list, click Auto and select 16.
  5. Click B for bold and click OK. In Conditional Formatting, click Save.


    Description of cf_pivot.png follows
    Description of the illustration cf_pivot.png

Learn More