You can limit the results displayed in a visualization to a subset of the rows returned from an ordered set of data. "Top N" is the set of results at the top of an ordered set. "N" is the number of results to show.
Top N Results is useful when you have a lot of results and you don't need to show all of them 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 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 a Dashboards 2.0 filter.
- Tips for using Top N
- To specify the exact number of Top N results
- To use a parameter filter as the Top N value - GETPROMPTVALUE
Tips:
- 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 Dashboards 2.0 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. Once the report is added to a dashboard and the filter 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 version 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
- For example, starting with a report of Retail Stores with the highest Revenue with Revenue
sorted descending.
- Click the Top button to open the Select Top Records menu.
- 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 the Top N value
To get the number of rows from a filter that is on the report, use GETPROMPTVALUE.
- For example, to have a business user set the Top N from choices you provide, create a parameter filter in Dashboards 2.0. Use the Make Mandatory option to require a value.
- 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.
- When a business user changes the filter, the report updates the Top N value.
