8 Replies Latest reply: Oct 6, 2016 2:34 PM by Cliff Clayman

# Limiting the amount of rows returned in a straight table

I have a straight table that I would like to return only the Top 10 totals.  How do I limit the results to the top 10 in descending order?  I've attached a sample file that should help.  I need it by Division, meaning I expect to see the top ten Supplier Names by Division.

• ###### Re: Limiting the amount of rows returned in a straight table

In a table box or straight table?

• ###### Re: Limiting the amount of rows returned in a straight table

Straight table.  I just put in a table box just to display the data.  There would normally be a Sum on the Total field.

• ###### Re: Limiting the amount of rows returned in a straight table

May be using this expression:

=If(Rank(TOTAL Sum(Total), 1, 1) < 11, Sum(Total))

• ###### Re: Limiting the amount of rows returned in a straight table

I guess I should have left my expression in as I am not sure how to implement the Rank function into it:

Sum({\$<[FY]={\$(vFY)}, [Country]={'US'}, [Flag]={'x'}>} [Total])

• ###### Re: Limiting the amount of rows returned in a straight table

May be like this:

=If(Rank(TOTAL Sum({\$<[FY]={\$(vFY)}, [Country]={'US'}, [Flag]={'x'}>}Total), 1, 1) < 11, Sum({\$<[FY]={\$(vFY)}, [Country]={'US'}, [Flag]={'x'}>}Total))

• ###### Re: Limiting the amount of rows returned in a straight table

This only gave me the Top Ten overall.  I need the Top Ten for each Division.

• ###### Re: Limiting the amount of rows returned in a straight table

May be use dimension limits in that case

Expression will stay as your original expression

Sum({\$<[FY]={\$(vFY)}, [Country]={'US'}, [Flag]={'x'}>} [Total])

• ###### Re: Limiting the amount of rows returned in a straight table

That did it!  Thank you.  I thought I had to do that on the expression.  I didn't realize it would work that way for a dimension.