OTHERS OPTION IN PIE GRAPH for Webi reports

building reports webintelligence
1

I need to create a PIE graph with OTHERS for a WEBI report.
I have 3 columns on the report
Student Roll No, Student Name, Total Marks

I need to display only the the Top 5 Student Names on the basis of Total Marks all others need to be displayed under OTHERS.

This needs to be displayed in a Pie Chart. I have a primary Key in the table “Roll No” which does not allow me to group on “Others”.

Please help!!! Can I create a Pie graph displaying only top 5 student names and the remaining students and their total marks in OTHERS section.

EX.
Roll No ----Student Name-------Total Marks
1------------Rita-------------------245
2------------Mary-----------------300
3-----------Joseph---------------200
4-----------Ralph----------------250
5----------Suzie-----------------150
6---------Ryan------------------230
7---------Tina------------------280
8----------Steve----------------295
9----------Jack----------------160
10---------Ronnie------------140

The graph should display only Mary,Steve,Tina,Ralph and Rita with their appropriate Total Marks and OTHERS should show the sum of teh remaining marks i.e 880.

firaz (BOB member since 2007-07-12)

2

Below topic talks on the same issue, hope it helps

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

Prashant Purohit :india: (BOB member since 2009-02-18)

3

Hi Firaz !

a dynamic solution below :

define a measure variable :

[Rank]=Rank([Total Marks] ; [Student Name])

and then a dimension variable :

[Student Top 5]=If([Rank] < 6 ; [Student Name] ; “Others”) In ([Student Name])

Then the objects : [Student Top 5] [Total Marks]

can be used in a chart :slight_smile:

Ive tested it on efashion, heres the scerenshot :

ese-aSH :fr: (BOB member since 2007-05-22)

4

Thanks for the reply…
I think you have not considered the Unique Key i.e Student Roll No.
This needs to be considered in the graph as we can have 2 student names with the same name but different roll no and different marks.

You have only considered Name and Marks where all names are distinct. Try having some duplicate names and different marks and a unique key like rollno…Let me knwo if you can group OTHERS then!!!

Let me know if you have any more ideas :hb:

Thanks,
Firaz

firaz (BOB member since 2007-07-12)

5

Well its the same :o

modified variables :

[Rank]=Rank([Total Marks] ; [Student Roll])

[Student Top 5]=If([Rank] < 6 ; [Student Name] ; “Others”) In ([Student Roll])

ese-aSH :fr: (BOB member since 2007-05-22)

6

I tried what you have mentioned i.e

[Rank]=Rank([Total Marks] ; [Student Roll])

[Student Top 5]=If([Rank] < 6 ; [Student Name] ; “Others”) In ([Student Roll])

I pulled only the above 2 object on the report:-
For Rank column I get #Context and Student Top 5 I get #Multivalue.
It does not work!!!

How have u done it??

firaz (BOB member since 2007-07-12)

7

Where are you trying to use the variables ?

If you are in a table or a section, this modifies the contexts.

In my example there are no sections :slight_smile:

about #CONTEXT and rank

It is normal that [Rank] gives you #Context if you use it in a table with [Student Top 5] (because the rank variable must be used with City as output context)

If you need to use those 2 objects alone in a table then put : [Rank] in([Student Roll]) instead of just [Rank]
(this will return a #MULTIVALUE for the “Others” but it is not a problem since you can sort ascending rank anyway)

about #MULTIVALUE and student top

Are you sure that you created a dimension variable ? by default webi will try to force it to a measure, which leads to a #MULTIVALUE error.
You can either force it to a dimension or let it as a measure and create an additionnal dimension variable : [Student Top 5 Dim]=[Student Top 5].

In all case, the variable you will use in your tables must be a dimension.

ese-aSH :fr: (BOB member since 2007-05-22)

8

This won’t work in XIr2.

joepeters :us: (BOB member since 2002-08-29)

9

Helped me a Lot! thanks! :smiley: :+1:

jvmauricio :philippines: (BOB member since 2009-08-03)

10

Joe - why do you say this won’t work??? :?

SarahG :australia: (BOB member since 2005-08-22)

11

I tried it in XIr2 but got a #MULTIVALUE error; same formula worked fine in XI3.

joepeters :us: (BOB member since 2002-08-29)

12

Hi Joe,
Ok that makes sense.
I found that even with a #MULTIVALUE error in the table, the graph displayed correctly - strange but true!
This is discussed more here: https://bobj-board.org/t/139201
I’m glad to know that it works in XIR3!!!
Thanks

SarahG :australia: (BOB member since 2005-08-22)

13

Geez, that’s weird! I have to admit that I did not try it in a chart, only in the table I was working with. Thanks for the tip, though!

joepeters :us: (BOB member since 2002-08-29)