Can I Show top 10(Rank) stations sort /group by Region?

Current report consist of 500 records and iam intrested in seeing only top 10 stations rank by station and Group by region.

current report after rank looks like .

[i][b]
Region Station Rev1 Rev2 Rank
R3 Albany 500 500 1
R2 Newyork 400 300 2
R3 ALaska 395 400 3
R1 Fairfax 300 600 4
R3 HOuston 290 500 5
R2 Richmond 200 300 6
R3 Tampa 195 400 7
R3 Ct 190 500 8
R2 Newjersy 175 300 9
R1 Indianapolis 125 400 10

Required Results

Region Station Rev1 Rev2 Rank
R1 Fairfax 300 600 4
Indianapolis 125 400 10
R2 Newyork 400 300 2
Richmond 200 300 6
Newjersy 175 300 9
R3 ALbany 500 500 1
ALaska 395 400 3
HOuston 290 500 5
Tampa 195 400 7
Ct 190 500 8 [/b][/i]
Can anyone tellme how can i achieve this task?:idea:

KSr


bouser_1 (BOB member since 2004-03-20)

I believe a RANK also acts as a primary sort.
You want to rank across all regions, but then group your ranked results by Region.

One way would be in modifying the universe in Designer using one of the Analytic SQL functions that many DBMS offer. Please, ask your universe designer and/or DBA for more details on this.


Andreas :de: (BOB member since 2002-06-20)

The Rank feature of BO does a sort and a filter.

What you want is to use the Rank function. Unfortunately, you end up not being able to do the filter.

I think you might be out of luck.


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

Steve,
Is there any way i can restrict the number of row to 10 (top 10) without applying the Rank function. I have access to BO,Designer,Supervisor.

KSR


bouser_1 (BOB member since 2004-03-20)

You could do this in the query itself. If you reverse sort your data you can also limit it to 10 rows. It’s on the Options button of the query panel. then you could do whatever you want at the report level.


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