Case Study: Using the Rank Function to Create Row Numbers

When used as a formula for a calculated measure, the Rank() function assigns each row a unique numeric value that represents a ranking of the corresponding field value. If the corresponding field values are all unique, the row numbers will be unique. For example, the function Rank("student_id") would assign a value to each student ID in a corresponding (and separate) column, with the (numerically) lowest student ID value corresponding to 1.

For the purposes of this case study, we will use the Course Completion Table View, creating Row Numbers based on the Course Completions ID (coursecompletions.coursecompletions_id).

To create Row Numbers for the Course Completion Table View, complete the following steps:

1. In the Repository, navigate to Public > Moodle/Totara > Ad Hoc Components > Views
2. Select the Course Completion Table View
3. In the Measures panel, hover over the Detail Selectoricon. The Details menu opens.
4. Select Create Calculated Measure. The New Calculated Measure window appears:
   
   
5. From the Functions list, double-click Rank to enter it into the Formula box:
   
   
6. In the Formula box, select the NumberFieldName so that it remains highlighted, as shown in the following image:
   
   
7. From the Fields and Functions list, select the Course Completions ID. Double-click the Course Completions ID so that it replaces the selected text int he Formula box:
   
   
   Your formula (read as: Rank("Course Completion ID")) will create a calculated measure that assigns each Course Completion ID a ranking value.
8. Click the Validate button to ensure there are no errors in your formula.
9. In the Measure Name field, name your new measure "Row Number."
10. Click the Create Measure button. Your new Row Number measure will appear in the Measures Panel.

To add and assign a sorting order for your Row Numbers, complete the following steps:

1. From the Measures panel, select the Row Number calculated measure and drag it into the columns box:
   
   
   The Row Number column appears at the end of your table. Note that each row now has a unique number, corresponding to the unique Course Completion ID.
2. To sort the values, right-click the Row Number column and select Use for Sorting. The Sort window appears:
   
   
3. In the Sort On panel, click the Row Number value to alternate between the two available sorting arrangements: ascending or descending.
4. Click Ok to save your changes. Your Row Numbers will now be sorted: