Oracle Communities

Oracle Analytics and AI

Child Item

Suppress value of a custom aggregated column

JMLMIII

Good Day, is there a way to suppress the value of my custom aggregated column?

I tried to set the property to 'Suppress' instead of 'Repeat' but it still return repeating values.

The custom aggregate column I want to suppress have this formula:

MAX(Completion_Date by Person_ID)

Thank you.

Find more posts tagged with

Analysis

OTBI

Accepted answers

All comments

Federico Venturin - Oracle

Hi @JMLMIII ,

You can't suppress values for measure columns. Only values for attribute columns can be suppressed when repeated.

You could try by transforming your column into an attribute using the ATTRIBUTE function: ATTRIBUTE(MAX(Completion_Date by Person_ID) BY attribute_list)

Please note that the BY clause and the attribute_list allows you to determine the level of granularity at which the measure will be aggregated, and then transformed into an attribute.

JMLMIII

Hello @Federico Venturin , I tried to have the code below. It loads very long and gives me an error.

ATTRIBUTE(MAX(Completion_Date by Person_ID) by Person_ID)

Error Details:

Hope you can help me on this.

Federico Venturin - Oracle

Hi @JMLMIII ,

Sorry, my bad! The ATTRIBUTE function requires to provide numerical values as input, but you have date values instead.

As a workaround, you can convert your date values to numbers first, and then apply the ATTRIBUTE function:

ATTRIBUTE(YEAR(MAX(Completion_Date by Person_ID))*10000 + MONTH(MAX(Completion_Date by Person_ID))*100 + DAYOFMONTH(MAX(Completion_Date by Person_ID)) BY Person_ID)

The above expression will work and repeated values will be suppressed... However, there could be a significative impact on performance since the calculation is really complex. Moreover, you will also need to convert the resulting numbers back to date values..

The ideal solution would be to calculate MAX(Completion_date by Person_ID) during ETL and add it as a new column directly to your physical data source. In this way, it will be treated automatically as an attribute in your analyses and you achieve the best performance since you don't need to execute any calculation at run time.

Another alternative could be to use conditional formatting:

Keep your calculation MAX(Completion_Date by Person_ID) as is
Sort rows by Person_ID, then by Completion_Date DESC
Add a new calculation like RCOUNT(1 BY Person_ID), this will generate row numbers that restart from 1 for each different Person_ID value
Add a conditional formatting rule to MAX(Completion_Date by Person_ID) based on the RCOUNT(1 by Person_ID) column. When the value is higher than 1, then you have to use the same color for the text and the background in order to "hide" repeated values

JMLMIII

Thank you and no worries @Federico Venturin . I will check and try your suggestions. :)

Regie Mercado

Hello and good day!

Dearest Federico,

I encountered this problem in suppressing the "Total" column. It is properly defined in column properties but it does not give the expected result. Hope you have possible solution.

Many thanks and best regards.

Regie

Gianni Ceresa

Hi,

You can't expect the rows to be merged on a column that has other columns on the left not merged. Your "Work Order" and "Required Quantity" force individual rows, the "Total" column can't merge rows back. The merging is a left-to-right thing: it can be merged as much as what is on it's left, or less.

Regie Mercado

Hello and good day!

Dearest Gianni,

Appreciate your response, any suggestion on how make the "Total" column not to repeat? I've tried to move the columns. Hope to hear again again sir.

Many thanks and best regards.

Regie