IBM Support

How To: Limit the number of rows displayed in a list

Troubleshooting


Problem

This document describes how to limit the number of entries displayed in a list. [Example: N is a number (for example 10) In a "Top N" report, there is a tie for Nth place. This results in two entries having "N" as their rank, so the filter [Rank] <= N] returns eleven entries. The technique described will remove one of these entries. The entry removed depends on the order in which the data is returned from the data source.

Resolving The Problem

Use the running-count function to create a data item that increments at every row, and filter on that running-count.

Steps:
1) Add a data item to the query:
 
running-count([data item to limit])

If there are multiple sets of data being ranked, the running-count needs to reflect that instead:

running-count([data item] for [grouping data item])

2) Add a filter to the query:

[running count data item] <= 10

Alternate solution:
 

1) In Query 1, add every data item you want in the list report

2) In the same query, create the query calculation rank.

rank([dataitem] for report)

3) Go to the Query explorer and click Queries.

4) Drag in a new Query (Query 2)

5) Drag Query 1 to the right side of Query 2 (so that a subquery is created)

6) Click Query 2.

7) Drag in every data item from the Query1 to Query 2.

8) Add a new filter in Query2. (click the Query Items tab, drag in rank and [rank] <=N)

9) Go to the page, select the list, change the query from Query 1 to Query 2.

The list now shows the first N items in the list ranked based on the [data item].

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Report Studio","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.2;10.2.1;10.2","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"ARM Category":[{"code":"a8m0z000000CbsBAAS","label":"Reporting->Report Authoring"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Document Information

Modified date:
27 August 2020

UID

swg21341684

Page Feedback