Visualizing Top N Results

You can limit the results displayed in a visualization to a subset of the rows returned from an ordered set of data. Top N Results is the set of results at the top of an ordered set. "N" refers to the number of results to show.

Top N Results is useful when there are a lot of results and it is not necessary to show all results in the report. Another scenario is when you want to highlight the top or bottom rows of a set, such as the highest numbers or lowest numbers. Use Sort to change whether the Top N Results is in ascending or descending order.

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

Here are some guidelines for visualizing the top N results:

  • By default the Top N is set to 100 of the first rows returned by the query, without a sort.
  • In Visualizer you can change the value for Top N either before you add measures and attributes or after a report is already created.
  • For large result sets, using "All" can affect performance.
  • A dashboard filter can pass a value to Top N using the BQL GETPROMPTVALUE function. During development 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.
  • When Top N is applied on a table, business users can choose to see one of two versions of the Grand Summary:
    • "Local" Grand Summary associated with the visible result set after Top N has been applied.
    • "Global" Grand Summary associated with the entire result set, independent of Top N.
  • If you want the data ranked in order to show the top or bottom rows, first order the data, either 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 to make sure 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:

  1. For example, starting with a report of Retail Stores with the highest Revenue with Revenue sorted descending.
  2. Click the Top N Results button to open the Select Top Records menu.
  3. Select an option, or enter a number and click Apply. The report updates immediately. The Top N button at the top shows the current number of results.

To use a parameter filter as the Top N value:

To get the number of rows from a filter that is on the report, use GETPROMPTVALUE. See GETPROMPTVALUE. In online help, see GETPROMPTVALUE and GETPROMPTFILTER and GETPROMPTFILTERS in the Infor Birst Dashboards User Guide.

  1. For example, to have a business user set the Top N from choices you provide, create a parameter filter in the dashboard. Use the Make Mandatory option to require a value.
  2. In Visualizer, enter 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 enter 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.
Related topics