Oracle Communities

Oracle Analytics and AI

Child Item

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

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.

Find more posts tagged with

Submitted

OTBI

Pivot Table

Status: Submitted

Comments

Riyaz Ali-Oracle

Try the below:

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

Hylton

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

2. Apply Conditional Formatting to Hide Empty Columns

Riyaz Ali-Oracle

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

Hylton

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.

Hylton

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

Riyaz Ali-Oracle

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.

Riyaz Ali-Oracle

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

Victor Loh

Quite a late reply. Seem pivot table option could achieve hidden column base on pivot prompt.