Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
How to add row number (fixed order) in analysis?
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
Answers
-
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
Gabriel0 -
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
0 -
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
0 -
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
0