Limit returned rows in AdHoc Tables Reports

[neilelliott]

Hi

I'm trying to create an AdHoc report to return the, for example, top 10 debtors for a particular account. Defining the report I can see the facility to return the list of debtors in decending order of debt, but I want to limit that to only showing the top 10.

Any ideas ?

Thanks

Neil

[mdahlman]

  There are no "Top N" sort of functions built in to Ad Hoc. But depending on your exact needs you can do something like this:

1. Create a calculated field that is your value times -1. (I'm assuming the value you want to sort on is a positive value.)
2. Create a calculated field that is the rank of the field created above.
3. Create a filter on the field created above to only include rank 10 or lower.

Ranking is always from smallest to highest, that's why I needed step #1. There is of course no need to display the 2 calculated fields. You'll probably want to see them as you're testing the report. But then remove them from the final report's displayed columns.

Does that meet your needs?

Regards,
Matt