How to add row number (fixed order) in analysis?

Jonathan Chan gmg

Summary

How to add row number (fixed order) in analysis?

Content

Hello Oracle Team,

Is there a way to add row numbers in analysis?

When I used RCOUNT(1), the row number will change when other columns are being sorted.  Is there a way to lock row number so it does not change when other columns are sorted?

Thank you.

Jon

Version

Oracle Business Intelligence 11.1.1.9.0

Gabriel Grigorie-Oracle

Dear Jon,

Please find an example to add row numbers to custom OTBI reports.

Go to OBTI reports
Analytics --> New --> Analysis --> select the Subject Area 'Workforce Goals - Goal Status Overview Real Time' (this is an example)
Add 'Business Unit Name' column name by double-clicking it.
Go to 'Results' and make sure you see the output.

Now the next step is to add a row number to this report.

Go to Criteria --> Add the same column name again to the analysis.
Edit the first column name --> Edit Formula and replace column formula to RCOUNT(1) and click 'ok'
Results will now show row numbers in the first column.

Hope this helps

Best Regards
Gabriel

Jonathan Chan gmg

Hello Gabriel,

Thank you for your response.

Your recommended method was my original approach.  Is there a way to lock the ranking order so it doesn't change despite re-sorting are performed on other columns.

The goal is to show an index column of row numbers for read friendly purpose, so this column should be static despite sorting that goes on for other columns.

Best.

Jon

 

Robert Fighiroae-Oracle

Hi Jon,

You can use the RANK function on a custom column/formula.

The RANK function calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

e.g.: RANK(Country_column by Manager_column)

The new rank column will have its values locked, even if you sort any other columns.

Maybe you can take a look around this function and try to adapt it to your business reporting needs.

Kind regards,

Robert

Jonathan Chan gmg

Hello Robert,

Thank you.

You advised method works produce a column with consecutive non-repeating row numbers for analysis if its columns do not share identical values.

For analysis having identify entries within a column, I hope Oracle will come up with a "Row Number" check box option for analysis in its future update.

Best.

Jon

Zee

This case is an issue in our reporting.

We solved this case not by RCOUNT(1), but by RSUM(1)

If it helps….