Alternative option to hide the dynamic row number for Grand Total in workbook

Harriet Huang-Oracle

In workbook, the row number column calling the OAC Running Aggregate Rcount(), hence it is a measure column.

When turn on the Grand Total for the workbook, row number is showing total number for Grand Total as well, which is not supposed to be.

One alternative solution to hide the row number in Grand Total is making the row number font as the same color as background color of the cell.

Below is the example to make font color as white (#FFFFFF).

More idea to hide the row number is welcome!

Harriet Huang-Oracle

Thanks @Philippe Lions-Oracle . RowID() works as expected!

However, I can't find it from OAC function, is it part of Database function?

FYI - I found this related video base on your information.

https://www.youtube.com/watch?v=FDpfThwWRFs

Federico Venturin - Oracle

Hi @Harriet Huang-Oracle ,

You could apply the ATTRIBUTE function to your calculated measure to treat it as an attribute (in this way Oracle Analytics will not calculate a grand total for it).

The syntax is as follows: ATTRIBUTE(YourMeasure BY YourAttribute1, YourAttribute2, etc.)

It's fundamental to include all the attributes in your visualization after the BY keyword to generate the values at the proper level of granularity.

Harriet Huang-Oracle

Thanks @Federico Venturin for the input.

However, converting the measure to attribute would cause the row number to be a static value and it is not correct when applying filter to the workbook. See below:

Philippe Lions-Oracle

Hi @Harriet Huang-Oracle , you could simply use the Rowid() syntax, see below. Let me know if not helping your need.

Philippe Lions-Oracle

Thanks @Harriet Huang-Oracle , we produced the video to describe how to use this syntax, and it shows that the results of rowid() function depend on the grain that is set in the query. For your example above, it will work 'as-is'.

Michal Zima

@Philippe Lions-Oracle Hi Philippe, function rowid() is very useful. Wouldn't be good to incorporate it to standard documentation (https://docs.oracle.com/en/middleware/bi/analytics-server/user-oas/expression-editor-reference.html) and also in Expression Editor (in list of available BI Server functions/expressions) ?

Thanks

Michal

Philippe Lions-Oracle

Thanks @Michal Zima we will follow up on this from the doc side. Watch out the subtleties of the function itself, in particular in the case of multi-tables datasets. You can see some example of the Rowid() function being delivered by the Auto Insights visualizations. Drag some of these in your canvas and check for the syntax of the row count calculation.

Harriet Huang-Oracle

Agree with you @Michal Zima. I am going to prepare a KM document for RowID() function, hopefully it would be helpful for customers before it is added to the document.

Thanks @Michal Zima and @Philippe Lions-Oracle !

Michal Zima

@Harriet Huang-Oracle KM document will be fine (as starting point), but inclusion into standard documentation and also Expression Editor should be the final "destination".

Harriet Huang-Oracle

@Michal Zima Yes, that's what I meant too.