Forum Discussion

ChrisNewsome's avatar
ChrisNewsome
Qrew Captain
6 months ago

Limit # of records in a table report

I'd like to be able to run a report that shows just the top 20 results. For instance, customers - sorted by volume from largest to smallest, only returning the top 20. I can't seem to figure out how to limit the results - i'm getting every customer in the system. Any help?



------------------------------
Thanks,
Chris Newsome
------------------------------

18 Replies

Sort By
  • MannyPinheiro's avatar
    MannyPinheiro
    Qrew Member
    6 months ago

    You can use a formula query with the Size function in a report formula to evaluate the field containing the volume data to check if it is greater than or equal to the volume. The size will return how many records you have by volume and assign a numeric value. You a filter the report to only show records where size is less than or equal to 20. 

    However, if you have many records that equal the same volume you will have many records with the same size value. 


    • ChrisNewsome's avatar
      ChrisNewsome
      Qrew Captain
      6 months ago

      I've never done that before... can you give me an example of how that would look?



      ------------------------------
      Thanks,
      Chris Newsome
      ------------------------------
      • MannyPinheiro's avatar
        MannyPinheiro
        Qrew Member
        6 months ago
        In this video, QBJ shows how to use Size to rank records.   It should be done as a report formula in the settings of the Table report. However, if you are dealing with a large data set you may encounter some performance issues. This is why it's suggested that you do it in a report formula vs a formula field at the table level. If there is a performance issue it will only impact that report vs the entire table. If performance is a concern you should look into alternate options.  Perhaps a pipeline that will query the top 20 and copy the records to a reporting table and each time it runs it refreshes the table.