Oracle Transactional Business Intelligence Idea Lab

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Pivot Table - Dynamic column suppression based on Table Prompt or Section

32
Views
7
Comments

Pivot tables unfortunately also include columns that are not in table prompt or sectioned variables. This makes it a bit messy from an output perspective.

Would be great if these can be excluded, or if at least a conditional format option to ifnull-hide column can be added.

e.g. table prompt below does not filter the column/measure associated with that dataset, the same applies to sections.

Tagged:
1
1 votes

Submitted · Last Updated

Welcome!

It looks like you're new here. Sign in or register to get started.

Comments

  • Rank 6 - Analytics Lead
    Try the below:
    1. Use Column Selection in Pivot Table Settings

    In OTBI, manually deselect unwanted columns from the Pivot Table Layout.
    Ensure that only the required columns are included in the dataset.

    2. Apply Conditional Formatting to Hide Empty Columns

    If some columns contain only null values, apply a conditional format to hide them.

    3. Modify the Data Model to Filter Unwanted Columns

    Ensure that only the required columns are included in the SQL query.
    Use NVL(column_name, ' ') or COALESCE(column_name, ' ') to replace null values with a placeholder.

    Check the below links:

    About Pivot Tables

  • Rank 2 - Community Beginner

    I would like to do this, but it does not seem possible in Cloud OTBI

    2. Apply Conditional Formatting to Hide Empty Columns

  • Rank 6 - Analytics Lead

    Hi Hylton,

    Use Conditional Formatting in Column Properties

    Navigate to Column Properties in OTBI.
    Under the Conditional Format tab, set a rule to hide columns when they contain null values.

    Set Properties for Columns

  • Rank 2 - Community Beginner

    Thanks Riyaz

    Here in green I have added the =null condition statement, but where on the pink highlight do I now indicate to hide the column? The condition format seems to only cover font, cell alignment, order, size and data format. I don't see the hide attribute.

  • Rank 2 - Community Beginner
    edited May 19, 2025 6:51AM

    Using display:none seems to have some mixed results, but it's gotten me closer. Even though it hides the data, the column heading seems to stay behind

  • Rank 6 - Analytics Lead

    Instead of just display:none, try applying conditional formatting to the entire column, including the header.
    See if it helps:set a condition like ifnull(column_name, '') to hide both the data and the header.

  • Rank 6 - Analytics Lead

    Try formatting using <?if:condition?> ... <?end if?> statements.

    You can wrap the column in an if statement to exclude it when values are null:

    xml

    <?if: COLUMN_NAME != ''?> <Column>
    <?COLUMN_NAME?>
    </Column>
    <?end if?>

    Check this:

    Use Conditional Formatting

    Ensure Hidden Columns stay Hidden in all Report Formats — Oracle Analytics

Welcome!

It looks like you're new here. Sign in or register to get started.