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 ...
Forum Discussion
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.
I've never done that before... can you give me an example of how that would look?
------------------------------
Thanks,
Chris Newsome
------------------------------
- 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.
This works kind of OK. It's still not really doing what I want it to do. I can't understand why it's not an option to just limit the # of results to X.
------------------------------
Thanks,
Chris Newsome
------------------------------Here is my formula: (64 is the FID for "Total Won Value (YTD)" and 62 is the FID for "Territory")
var text QUERY = "{64.GT.'" & [Total Won Value (YTD)] & "'}AND{62.XEX.'Knoxville'}"; Size(GetRecords($QUERY))+1Here's the results I'm getting:
------------------------------
Thanks,
Chris Newsome
------------------------------- What result are you getting? Or what's not working as you expect it to?