Limit the Number of Records Returned

Say you have a report that returns a large amount of data and you want to see only a sample number of rows.  You can easily do this by creating a data item with a running count of a column in the report (i.e. UST_ID).  Then add a filter in the query (e.g.  Data Item <= 100).  You can also set this up as an optional prompt allowing you to choose the number of records you’d like to view each time the report is run.

1. In the Query Explorer double click on the tabular model of your main query.
2. From the Toolbox, drag a Data Item into the Data Items pane of the query.
3. In the expression definition box type the following:
                        running-count([STUDENT_ID])
4. Name the Data Item Running-Count.
5. From the Toolbox drag a filter into the Filters pane and type in this expression:
                        [Running-Count]<= 100.

 When you run your report only the first 100 records will be returned.

 To make this a prompt:

1. From the Page Explorer select your prompt page or create one if the report does not have one.
2. From the Toolbox drag a Text Box Prompt to your prompt page.
3. Select Create a New Parameter and name the parameter record_limit.
4. Click finish.
5. In the Query Explorer double click on the tabular model of your main query.
6. Select the Running-Count filter and change the expression to:
                        [Running-Count]<=?record_limit?
7. Make this filter optional by opening Page 1 from the Page Explorer and selecting the filter icon.

 When you run your report you will be prompted for a number to enter.  If left blank the report will return all records.

The following logic will accomplish the same task without using a query item from the report.

1. Create a data item called Rows:  running-count(count(rows))
2. Then filter the query:  [Rows] <11

 This will give you 10 rows without referencing any of the query items within the report.