One of the
Top 10 lists are a common request when reviewing maintenance and operation data with mid to upper level management.
- Top 10 oldest work orders in the backlog
- Top 10 most expensive inventory items
- Top 10 assets with most repairs
- Etc, etc, etc.
A problem in BIRT is that there isn’t a clean way to limit the output to just the first 10 rows returned. The immediate thought is to use the ‘visibility’ setting on the detail row and use the row_num as a condition to limit the number of records displayed. Unfortunately, using the row ‘visibility’ setting does not work the way you’d think it should.
Here are the basic steps to limit the number of rows returned in the report:
- Create the report with all the required information. Do not worry about limiting your record set yet, make sure all the information that is needed in the report works first and that the first 10 (or 20, 50, etc) rows include the information you want to see. For example, you may need to add something like
order by workorder.reportdate descto your Open Method script to have the records have the newest WO’s listed first.
- On the Detail Row, right click the row and add a new row below. Don’t worry, this is just temporary and will be removed later on.
- In the new row, insert a new Aggregation field. Label it row_cnt or something that you’ll remember as a function to count rows.
- On the Detail Row that has report values you want to limit, click on the entire row to set the row’s visibility.
- Using the row_cnt aggregate field, add a visibility restriction to only see the first 10 rows.
- Now delete the temporary Detail Row and run your report. This will remove the row_cnt field from the report, but it doesn’t remove or affect its use in the visibility restriction.
Now when you run the report, only the first 10 records will display. Adjust the visiblity restriction as needed to show fewer or more rows in your report.