limit top10 rows in a block

[system]

Hello!

I have a block where I apply a rank for top10 based on a mesaure. This measure has the max value for more than 10 rows in some cases, so webi shows me all rows that have this max value, despite the block raises 10 rows.

Any idea to limit the block size to 10, no matter the value of the max. value of measure is repeated?

Thanks !!

---

drserguey (BOB member since 2010-03-01)

[system]

These posts should give you some ideas:-

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

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

---

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

[system]

This one is amazing!

120×120 10.4 KB

I have a table and Chart both having Ranking for Top 20 Budgeted Departments. Sometimes there may be 50 departments which have same budget in that case the table expands and overlaps with other blocks below. To avoid this I wanted to show only 20 departments maximum even when there is a tie. How do we achieve this inside a Webi? Thanks in advance. Diana_baker (BOB member since 2005-03-18)

I’m not pretty sure how it works but when you have a tie is a very good thought.

Thanks!!

---

drserguey (BOB member since 2010-03-01)

[system]

Hi guys,

I do have same requirement ,but little different from the original post. MY requirement is i have 20 regions with sales value, i need to display top 0 regions with sales value and remaining 10 regions i need to tag it as "Others and it is sales value is sum of sales value for this remaining 10 regions.

Please note: i don’t have access to universe so i need to do this in WEBI level.

I know Rank function will hep me to find top 10 regions, but the issues is how to tag remaining 10 regions as Others in the table.

Any suggestions pls

Thanks,
-B-

---

lovebo (BOB member since 2010-10-06)

[system]

I’ll try creating a variable dimension like:

If ([Rank]>=10) Then “Others” Else [region]

---

drserguey (BOB member since 2010-03-01)

[system]

that is fine, but it will display 10 others in the report,
i need to display like this

Region1 2000
Region2 1999

–

Region10 1890
Others 5000

So total number of region is 20, the report needs to display top 10 region name with their sales value, remaining all 10 rows and it is value need to display in only one occurance with name "Other and the sales value is sum of all the remaining 10 regions sales value.

---

lovebo (BOB member since 2010-10-06)

[system]

@drserguey

I’ll try creating a variable dimension like:

If ([Rank]>=10) Then “Others” Else [region]

I see… you can add a row (like a footer row, like when you make a break) with “Others” and a formula that calculates the value of all regions that are not top10.

---

drserguey (BOB member since 2010-03-01)

[system]

great idea!!!

now i need to convert this table in to Pie Chart…

I will let you know if it works.

Thanks,
-B-

---

lovebo (BOB member since 2010-10-06)

[system]

Any luck converting this to a pie chart?

---

JonTarz (BOB member since 2006-01-09)

[system]

Sorry! It is not giving as expected in the graph. So i have created the derived table in the universe and used in the report.

Thanks.

---

lovebo (BOB member since 2010-10-06)