Database Ranking

Definition

Database Ranking is a method of filtering at the query level that allows a smaller selection of records based on ranking on a particular field. Database ranking uses functions built in at the database level to limit selections to only to top or bottom number of records or the top or bottom percentage of records.

Database Ranking is similar to the Ranking Filter that can be placed on a table after a query has been run.

Ranking at the query level, like the Ranking FIlter, is helpful because it can pick the top or bottom set of records based on your criteria, making your report more concise. Database Ranking can be more helpful than the Ranking Filter because it can retrieve information to judge the top or bottom set from outside the scope of your actual report. In other words, it can itself be filtered by different criteria than your query filters.

Video


Database Ranking

1. Create or open a report. Navigate to the query panel.

2. Select the field that you want to limit.

3. Click the Database Ranking button in the upper right-hand corner of the Query Filters area.

Screenshot of InfoView Query Panel showing the location of the database ranking button

Example: Course Title

Your filter will look something like this:

Screenshot of InfoView showing Course Title filter using database ranking

4. Select the measure that Course Title will be ranked on.

Note: This can only be a measure.

Example: Course Title by Enrollment

This ranking will pick the rows with the course titles with the top 10 individual section enrollment counts of all time.

Screenshot of InfoView database ranking function example course title by enrollment

5. The database ranking itself can also be limited independently from the other limits in your query. This can be accomplished by selecting the limiting field and drag-and-dropping it into the space below the database ranking. A new limit-within-your-limit will appear and can be filled out in exactly the same way as a normal query filter.

Example: Course Title by Enrollment

Instead of picking the top 10 enrollment counts of all time, this filter pick the top 10 enrollment counts from the 2005-06 academic year.

Screenshot of InfoView database ranking with further filters

6. It is also possible to pick multiple top-10's in a single ranking query, such as picking the top 10 of each term in a year or in each division.

Ti accomplish this, click the last button on the filter, the black triangle

Screenshot of InfoView database ranking filter extension button location

The extra field that appears, "Ranked by", allows you to add another dimension to your ranking. A "top 10" set will be retrieved for each value of your dimension.

Screenshot of InfoView database ranking with extension

Example: Course Title by Enrollment

This filter will pick top 10 enrollment by individual sections for Fall, Winter, Spring and Summer in the 2005-06 academic year.

Screenshot of InfoView showing DB ranking

7. The first drop-down menu in the filter gives the option of picking the top number of records, bottom number of records or the top or bottom percent of records based on the logic already implemented.

Example: Course title based on Enrollment counts

Choices in this menu will return:

  • Top: The names of the courses with the top 10 enrollment counts
  • Bottom: The names of the courses with the bottom 10 enrollment counts
  • % Top: The names of the courses with the top 10% of enrollment counts
  • % Bottom: The names of the courses with the bottom 10% of enrollment count

Screenshot of InfoView database ranking with first drop down menu showing

8. The default number 10 can also be changed by clicking the field and typing a new number. Clicking on the black triangle allows the number to be changed to a prompt.

Screenshot of InfoView database ranking second field and menu expanded

9. When you are satisfied with your selections, click ok. Once you run your report, it will be limited to only those records that fulfill the database ranking.