Ranking doubt

I need to display top 50 sales records. I’m using Rank function in Slice and Dice panel. Is it the only way to get topmost records or do i need to write SQL query for that? Is it possible without SQL query Thanks in advance?


BOisBest :india: (BOB member since 2004-04-05)

It’s possible to do it at the report level, but you’d have to return ALL the data to the report in order to rank it.

You really should do it at the database level.


Steve Krandel :us: (BOB member since 2002-06-25)

Thanks Steve. Actually, i have 6 crosstabs in my report and i’m showing data for each measure for 12 months. So i have 3 dimensions and 1 measure in each of those crosstabs. Dimensions are Code_Number, Issue_Name and Month/yr. Measures are sales, profit… I have to rank top 50 Issue_Names for each month(i.e sorted in desc order on month)
Now when i’m applying Rank function through slice and dice panel, i dont get sorted results.
My rank function is Rank(, ). What could be wrong?


BOisBest :india: (BOB member since 2004-04-05)

Have you created Sections for each month, or have you just applied a Break?

The ranking will occur contextually, so if the month is still part of the data block… You may not get the results you expect.


digpen :us: (BOB member since 2002-08-15)

The Rank function does not sort. You have to sort the data the way you want.

If you use the Ranking feature, you may get a better result.


Steve Krandel :us: (BOB member since 2002-06-25)

Actually… I thought the ranking overrode all Filters and Sorts. That means that it automatically sorts in descending order based on the Ranked Measure.

I think I’ve used a technique to use some “multipliers” to a measure to adjust my sorting the way I wanted it, but displayed the original measure in the report.

(Like using -100000 and grabbing the bottom (not top) values), or using IF THEN ELSE to add XXX amount based on the order I would prefer them in… of course the XXX amount had to be larger than the values ranked.

-RM


digpen :us: (BOB member since 2002-08-15)

The Rank function doesn’t sort or filter it just tells you where each row falls. If you want to sort, you have to do it manually,

If you use the Ranking feature (Format Ranking) it sorts and filters.


Steve Krandel :us: (BOB member since 2002-06-25)

You’re right. I keep thinking of Ranking the report and adding the RANK as the same, when you can do the latter without the filtering.

You cannot, however, do any filtering on this new rank, but you can use it to decide whether or not to display values. (e.g. If <= 50 then )


digpen :us: (BOB member since 2002-08-15)

So do i need to create section for each month or apply break on month to get top 50 sales records?


BOisBest :india: (BOB member since 2004-04-05)


 
                                           Month1   Month2
                                           Sale1       Sale2
Code        Issue_name              $             $

So should i apply break on Month to get top 50 sales records? Please let me know as this is the first time i’m working on such report. Thanks


BOisBest :india: (BOB member since 2004-04-05)