Visualizing Top N / Bottom N results

You can limit and rank data displayed in the Visualizer by selecting the Top N or Bottom N records based on specific measures and attributes. N refers to the number of results displayed in the report.

Top N or Bottom N is useful when there are a lot of results, and it is not necessary to show all results in the report. and when users need to view only the highest or the lowest ranked values.

You can preset an exact number of rows to return, or you can pass a value chosen by a user from the dashboard filter.

Note: Visualizer includes a None option for the attribute and metric to preserve backward compatibility. When None is selected, Top N returns the first set of rows from the result.

Here are some guidelines for visualizing the top N or the bottom N results:

  • By default, Top N is set to 100 of the first rows returned by the query, without a sort. This preserves previous behavior of Top N (100) when no attribute or measure is selected.
  • In Visualizer you can change the value of 'N' before you add measures and attributes or after a report is already created.
  • For large result sets, using All can affect performance.
    Note:  All is available only in Top N mode. In Bottom N mode, selecting All disables the Apply button.
  • A dashboard filter can pass a value to Top N or Bottom N using the BQL GETPROMPTVALUE function. During report creation this value may not yet be available in Visualizer, in which case all rows are returned. You can add a default value to the GETPROMPTVALUE expression to see it work from within Visualizer. After the report is added to a dashboard and the filter is applied, the value updates accordingly.
  • Top N also supports the GETVARIABLE expression.
  • If you want the data ranked in order to show the top or bottom rows, first order the data, ascending or descending, using Sort in Chart Builder. See Sorting data in Visualizer.
  • During development, you can look at a table view to see the ordered set of data and use it to help decide the number to use for Top N.
  • Check if the Top N provides what you want for your business users, and does not return unnecessary rows. Large result sets can affect performance and may not be needed by your users.

To specify the number of Top N results, perform these steps:

  1. Click the Top N button to open the Select Top Records menu.
  2. Specify an option, or specify a number and click Apply. The report updates immediately. The Top N button at the top shows the current number of results.

To get the number of rows from a filter that is on the report, use GETPROMPTVALUE. See GETPROMPTVALUE. See Infor Birst User and Administration Library, Infor Birst Dashboards User Guide > Passing prompt filters and values using BQL > GETPROMPTVALUE and Infor Birst Dashboards User Guide > Passing prompt filters and values using BQL > GETPROMPTFILTER and GETPROMPTFILTERS.

For example, to have a business user set the Top N from choices you provide:

  1. Create a parameter filter in the dashboard. Use the Make Mandatory option to require a value.
  2. In Visualizer, specify the GETPROMPTVALUE expression in the Select Top Records dialog. The first parameter is the name of the dashboard filter, and the second is the default value. GETPROMPTVALUE('Select Number of Rows', '3') Visualizer validates the expression as you specify it. When you click Apply, the Top N button in the Visualizer menu bar shows that it is using a BQL expression.
  3. When a business user changes the filter, the report updates the Top N value.