Top Values

building semantic layer
1

Hi,

I have thousands of data in report. But user want’s to see only Top 5 Rows/Records. Below is the scenario, in which Amount has same values for multiple states. But user want’s to see only 5 Rows.If I apply rank function in Report level it’s showing all instead of 5 Rows(till St5).
How to get this info. in Report level. Do we needs to create any universe object based on row number on amount in order to get correct values. If yes, how to do this.

State Amount Rank
St1 234 1
St2 210 2
St3 198 3
St4 185 4
St5 185 4
St6 185 4
St7 185 4

Thanks in Advance.

Shravan R.

shravan_bo :india: (BOB member since 2006-04-25)

2

Firstly, to reduce the data set you could do the following, this will make the report more manageable:-

An anlytical function in the universe, if you RDBMS supports it.
DB Rank in Webi query panel, if your RDBMS supports it.

As for the report side, you could look at this for ideas:-

https://bobj-board.org/t/160695

Mak 1 :uk: (BOB member since 2005-01-06)

3

Thanks for response. But it’s not working fo my scenario i.e, ties of values. Please let me know any other way to do this.

shravan_bo :india: (BOB member since 2006-04-25)

4

There are a number of methods there, have you tried them all?
The linked post describes various methods for dealing with ties, thats why I posted it :roll_eyes: .

Mak 1 :uk: (BOB member since 2005-01-06)

5

But what result will you be giving to your end-user?

How do you deside what data to leave out of the report?

May a rownumber filter be an option (first sort so that the highest value is on top)?

create a variable as:


=If (RowIndex()<6) Then 1 Else 0

Then apply a filter on this variable

martensnl :netherlands: (BOB member since 2006-12-19)

6

Hi Marten,

This was just one of the scenarios in the linked post, maybe, he didn’t bother reading it!
I agree, which record to show, how do you decide, still, not really my problem 8) ?

Cheers,

Mark.

Mak 1 :uk: (BOB member since 2005-01-06)

7

Hi

The methods in post is not working for me, I don’t know what went wrong.

It’s based on paid amount(I mentioned in the example above).

BTW, I tried with Row_number function,with universe object

ROW_NUMBER() OVER( ORDER BY (SUM(table.paid_amount)) DESC NULLS LAST)

And applied this objects as a conditnal filter in a report.

Thanks of r ur reply and suggesations.

Shravan

shravan_bo :india: (BOB member since 2006-04-25)